SQL – Foreign Key

A foreign key in SQL is a column or set of columns in a table that references the primary key of another table. It is used to establish and enforce a link between data in two tables, and is used to prevent actions that would create orphan records.

Foreign keys are used to create a relationship between two tables and maintain data integrity in a relational database.

Here is an example how to use foreign key.

-- Create table1
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    order_date DATE
);

-- Create table2
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(255),
    price DECIMAL(10,2),
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES

In this example, we have two tables: “orders” and “order_items.” The “orders” table has a primary key column “order_id,” and the “order_items” table has a column “order_id” that is used as a foreign key to link the data in the two tables. The syntax of foreign key is FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column) It creates a constraint that the value of column_name in current table must match with referenced_column in referenced_table for a row to be inserted or updated in the current table.

Here is an another example of how to create two tables, one with a primary key and another with a foreign key constraint in SQL:

CREATE TABLE parent_table (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE child_table (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);

In this example, we are creating two tables called “parent_table” and “child_table”. The “parent_table” has two columns: “id” and “name”. “id” column is the primary key and “name” column is defined as NOT NULL.

The “child_table” has three columns: “id”, “parent_id” and a foreign key column that references to the primary key column of parent_table.

To insert data in the table

INSERT INTO parent_table (id, name)
VALUES (1, 'John Doe');

INSERT INTO child_table (id, parent_id)
VALUES (1, 1);

If we try to insert a value in the parent_id column of child_table which does not exist in the parent table

INSERT INTO child_table (id, parent_id)
VALUES (2, 2);

This query will return an error because the foreign key constraint is defined to only allow values that match the primary key of the parent_table.

It is important to note that the syntax for creating tables and inserting data may vary depending on the specific database management system you are using. It’s also worth to mention that some databases implement cascading actions on foreign key, so when you delete a row in the parent_table, the child_table will be affected as well.

Primary vs Foreign

Here is a table that shows the main differences between primary keys and foreign keys in SQL:

Feature	Primary Key	Foreign Key
Definition	A column or set of columns in a table that uniquely identifies each row in the table.	A column or set of columns in a table that is used to establish a link between the data in two tables.
Constraint	Enforces the uniqueness of the values in the primary key column(s).	Enforces referential integrity by ensuring that the value of the foreign key matches a value of a primary key in another table.
Can be Null?	No (null values are not allowed in primary key columns)	Yes (null values are allowed in foreign key columns, but it’s not recommended)
Can be repeated in the same table	No (primary key values must be unique within the table)	No (foreign key values must match the primary key values of the referenced table)
Can be referenced by	None	A foreign key

It is worth noting that A table can have multiple foreign keys but only one primary key. Also, a primary key can be a foreign key to another table.