Foreign Key Constraint

A foreign key constraint in SQL is a type of constraint that is used to establish a link between two tables in a relational database. This constraint ensures that the data being stored in the child table (the table that contains the foreign key) is consistent with the data in the parent table (the table that contains the primary key).

When a foreign key constraint is applied to a table, it specifies that the values in one or more columns of the table must match the values in the primary key of another table. This ensures that only valid data is stored in the child table, and that referential integrity is maintained between the two tables.

To create a foreign key constraint in SQL, you must specify the name of the foreign key, the name of the column or columns that contain the foreign key data, the name of the parent table, and the name of the primary key column or columns that the foreign key references.

Example

For example, consider the following two tables:

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50)
);

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

In this example, the customers table has a primary key column id, while the orders table has a foreign key column customer_id that references the id column in the customers table. This establishes a relationship between the two tables, where each order is associated with a specific customer.

If a user tries to insert a row into the orders table with a customer_id value that does not exist in the customers table, the foreign key constraint will prevent the insertion and raise an error. This ensures that only valid customer IDs can be associated with orders, and that referential integrity is maintained between the two tables.

In summary, foreign key constraints are an essential aspect of relational database design and are used to ensure that the data in a database remains consistent and accurate. By establishing relationships between tables, foreign key constraints help maintain referential integrity and prevent the insertion of invalid data.