Contents

Create tables with a foreign key using SQL

When two tables in your database are related to each other, this relation is expressed by a foreign key field. The foreign key field is a field that references the primary key field of another table.

foreign key reference

The customer field in the orders table is a foreign key field. It references the customer_id primary key field of the customers table, linking each order to a customer.

When a relationship exists between two tables, you are not obliged to express this explicitly by defining a foreign key constraint, but it is a good practice to do so, because the foreign key constraint will guard the integrity of your data. With a foreign key constraint in place it would not be possible to add an order with a non-existent customer in the example tables above for example. Without the foreign key constraint, this would be possible.

Creating the foreign key constraint with SQL

The syntax for defining foreign key constraints differs between databases. The standard syntax below should work on most databases. It creates a foreign key constraint between the customer field in the orders table and the customer_id primary key field of the customers table.

CREATE TABLE orders (
order_id int NOT NULL,
order_date datetime NOT NULL,
customer INT NOT NULL,
FOREIGN KEY(customer) REFERENCES customers(customer_id),
PRIMARY KEY (order_id)
)

Naming a foreign key constraint

If you read the last page you know that constraints have names and if you don't supply one yourself, like in the last example, your database will make one up for you. When I just tried the example above on Microsoft SQL Server it generated this name for my new constraint:  FK__orders__customer__1367E606.

constraint names

If you want to create tables with your own foreign key constraint names, you can. Just provide your name using the CONSTRAINT keyword.

CREATE TABLE orders1 (
order_id int NOT NULL,
order_date datetime NOT NULL,
customer INT NOT NULL,
CONSTRAINT fk_orders_to_customers FOREIGN KEY(customer) REFERENCES customers(customer_id),
PRIMARY KEY (order_id)
)

Now, the constraint is called fk_orders_to_customers. Just make sure you don't use the same constraint name twice, because constraint names must be unique.