How to Create a Table with a Foreign Key in SQL

If you're looking to create a table with a foreign key in SQL, you're in the right place! A foreign key is a column or group of columns in a table that references the primary key of another table. This is a great way to ensure data integrity and maintain relationships between different tables in your database.

The Solution

The solution is to use the CREATE TABLE statement with the FOREIGN KEY clause. This clause allows you to specify the column or columns that will be used as the foreign key. For example, if you wanted to create a table called orders with a foreign key referencing the customers table, you would use the following query:

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Examples

Let's look at a few examples of how this statement can be used. Suppose you have two tables, customers and orders, with the following data:

If you wanted to create a table with a foreign key referencing the customers table, you would use the following query:

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

This query would create a table with a foreign key referencing the customers table. This foreign key would ensure that any data inserted into the orders table must have a valid customer_id that exists in the customers table.

Additional Info

The syntax for creating a table with a foreign key may vary slightly depending on the database you are using. For more information, check out the documentation for your particular database. 🤓