How do you create a table with a FOREIGN KEY constraint?
EveClark
Posted by EveClark
Last Updated: July 29, 2024
table constraint
To create a table with a FOREIGN KEY constraint in SQL, you need to define the foreign key relationship between two tables. The FOREIGN KEY constraint ensures that the values in a column (or a set of columns) match values in another table's primary key or unique key. Here’s how you can create a table with a FOREIGN KEY constraint in SQL: 1. Create the parent table: This table will contain the primary key. 2. Create the child table: This table will contain the foreign key that references the primary key from the parent table.
SQL Example
Let's say we have a Customers table (the parent) and an Orders table (the child).
Step 1: Create the Parent Table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL
);
Step 2: Create the Child Table with FOREIGN KEY
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Explanation:
- In the Customers table, CustomerID is defined as the primary key. - In the Orders table: - OrderID is defined as the primary key for orders. - CustomerID is defined as an integer column that will store references to CustomerID from the Customers table. - The FOREIGN KEY (CustomerID) line establishes a foreign key constraint that enforces referential integrity, ensuring that any value in CustomerID of Orders must match a value in CustomerID of Customers.
Important Notes:
- When adding a FOREIGN KEY constraint, the data types of the foreign key and the primary key must match. - The parent table must be created before the child table to avoid reference errors. - Foreign key constraints can also include options such as ON DELETE CASCADE or ON UPDATE CASCADE to specify actions when the referenced data is updated or deleted. Here’s an example: 
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);
This will automatically delete any orders associated with a customer if that customer record is deleted from the Customers table.
Default Image