SQL Foreign Key

h‮/:sptt‬/www.theitroad.com

In SQL, a foreign key is a column or set of columns in one table that refers to the primary key of another table. The foreign key constraint ensures that the values in the foreign key column(s) match the values in the primary key column(s) of the referenced table, or are null if the referenced row is deleted.

Here is an example of how to create a foreign key constraint on a table:

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

In this example, the orders table has a foreign key constraint on the customer_id column. The customer_id column refers to the customer_id primary key column of the customers table. This means that each value in the customer_id column of the orders table must exist in the customer_id column of the customers table.

You can add a foreign key constraint to an existing table using the ALTER TABLE command:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_id FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

In this example, we are adding a foreign key constraint to the customer_id column of the orders table using the ALTER TABLE command.

Foreign keys are important in database design because they help enforce data integrity and ensure that data is consistent across multiple tables. They can also help with data retrieval and querying, as they allow tables to be joined based on common columns.