Day 79 – SQL FOREIGN KEY Constraint
In SQL, a FOREIGN KEY constraint is a way to enforce referential integrity between two tables. It ensures that the data in the child table (the table containing the foreign key) always refers to valid data in the parent table (the table being referred to by the foreign key).
To create a FOREIGN KEY constraint, you need to specify the column(s) in the child table that will contain the foreign key, as well as the parent table and column(s) that the foreign key will refer to. Here’s an example:
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, we’re creating a table called “orders” with an order_id, customer_id, and order_date column. The customer_id column is a foreign key that references the customers table. The REFERENCES keyword specifies the parent table and column that the foreign key refers to.
With this FOREIGN KEY constraint in place, we can ensure that no orders are placed for non-existent customers. If a customer is deleted from the customers table, any orders that reference that customer will also be deleted (or updated, depending on the ON DELETE behavior specified in the constraint).
In summary, the FOREIGN KEY constraint is an important tool for maintaining data integrity in a database. It allows you to create relationships between tables and ensure that those relationships are always valid.