1. Introduction

Goal of the Tutorial

In this tutorial, we will explore how to use primary and foreign keys in SQL. These keys are fundamental for establishing relationships between tables in a database, ensuring data integrity and enabling efficient data lookup.

Learning Outcomes

By the end of this tutorial, you will be able to:
- Understand what primary keys and foreign keys are in SQL
- Create tables with primary keys and foreign keys
- Use these keys to associate records across tables

Prerequisites

You should have a basic understanding of SQL and how to create tables and insert data into them.

2. Step-by-Step Guide

Primary Keys

A primary key is a column or a set of columns that uniquely identifies each row in a table. It must contain unique values and cannot contain NULL values.

Here's how you create a table with a primary key:

CREATE TABLE Customers (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (ID)
);

In this example, 'ID' is the primary key for the 'Customers' table.

Foreign Keys

A foreign key is a column or a set of columns used to establish a link between the data in two tables. The foreign key in one table points to the primary key in another table.

Here's how you create a table with a foreign key:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
);

In this example, 'CustomerID' is the foreign key that links the 'Orders' table to the 'Customers' table.

3. Code Examples

Example 1: Creating Tables with Primary and Foreign Keys

-- Create the 'Customers' table with 'ID' as the primary key
CREATE TABLE Customers (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (ID)
);

-- Create the 'Orders' table with 'OrderID' as the primary key and 'CustomerID' as the foreign key
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
);

Example 2: Inserting Data Into Tables

-- Insert data into the 'Customers' table
INSERT INTO Customers (ID, Name) VALUES (1, 'John Doe');

-- Insert data into the 'Orders' table
INSERT INTO Orders (OrderID, OrderNumber, CustomerID) VALUES (1, 12345, 1);

4. Summary

In this tutorial, you've learned about primary and foreign keys in SQL. You've seen how to create tables with these keys and how to use them to associate data across tables. Continue practicing to get more comfortable with these concepts.

5. Practice Exercises

Exercise 1

Create a 'Products' table with 'ProductID' as the primary key and a 'Sales' table with 'SaleID' as the primary key and 'ProductID' as the foreign key.

Exercise 2

Insert data into the 'Products' and 'Sales' tables.

Solutions

-- Solution for Exercise 1
CREATE TABLE Products (
    ProductID int NOT NULL,
    ProductName varchar(255) NOT NULL,
    PRIMARY KEY (ProductID)
);

CREATE TABLE Sales (
    SaleID int NOT NULL,
    SaleAmount decimal NOT NULL,
    ProductID int,
    PRIMARY KEY (SaleID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Solution for Exercise 2
INSERT INTO Products (ProductID, ProductName) VALUES (1, 'Product 1');
INSERT INTO Sales (SaleID, SaleAmount, ProductID) VALUES (1, 100.00, 1);