SQL Constraints: Foreign Key Constraint

A foreign key constraint is a type of constraint in SQL that ensures referential integrity between two tables. It defines a relationship between a column in one table and a column in another table.

Syntax

The syntax for creating a foreign key constraint is:

CREATE TABLE table_name (
  column_name1 data_type PRIMARY KEY,
  column_name2 data_type,
  column_name3 data_type,
  ...
  FOREIGN KEY (column_name2) REFERENCES parent_table(parent_column_name)
);

Example

Let's say we have two tables: customers and orders.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(255) NOT NULL
);

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

Output

In the example above, the orders table has a foreign key constraint on the customer_id column, which references the customer_id column in the customers table.

This ensures that any data entered into the customer_id column in the orders table is valid and exists in the customers table. If a user attempts to insert data into the orders table with a customer_id value that does not exist in the customers table, an error will be thrown.

Explanation

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

In the example above, the foreign key constraint ensures that the customer_id column in the orders table is always valid and exists in the customers table. This prevents any data inconsistencies and ensures data integrity.

Use

Foreign key constraints are useful when dealing with relational data that is spread across multiple tables. They help to maintain referential integrity, which prevents data inconsistencies.

Important Points

• The column in the referencing table (the one with the FOREIGN KEY constraint) must match the data type of the column in the referenced table.
• The column in the referencing table must also have an index on it.
• A FOREIGN KEY constraint can be defined as NULL, which allows data in the referencing table to be empty.
• A FOREIGN KEY constraint can be defined with the ON DELETE CASCADE property, which deletes the rows in the referencing table when a row in the referenced table is deleted.

Summary

A FOREIGN KEY constraint is a type of SQL constraint used to ensure referential integrity between two tables. It defines a relationship between a column in one table and a column in another table. Foreign key constraints help to maintain data consistency and prevent data inconsistencies.