sql
  1. sql-foreign-key
PreviousNext

SQL Keys – Foreign Key

A foreign key is a field in a relational database table that is used to establish a connection between two tables. It refers to a primary key of another table to establish the relationship between them. In this way, foreign keys ensure referential integrity within a database.

Syntax

The syntax for creating a foreign key in SQL is as follows:

ALTER TABLE table_name
ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column_name);

Example

Let's take an example of two tables, Customer and Orders. We want to establish a relationship between the two tables where the customer ID in the Orders table corresponds to the ID in the Customer table.

CREATE TABLE Customer (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_price DECIMAL(10,2),
    CONSTRAINT fk_customer_id
        FOREIGN KEY (customer_id) REFERENCES Customer(id)
);

Output

If the above SQL code is executed successfully, we will have two tables – Customer and Orders, with a foreign key constraint established between them.

Explanation

The ADD CONSTRAINT keyword is used to add a constraint to an existing table. In this case, we're adding a foreign key constraint. fk_name is the name of the foreign key constraint. column_name is the name of the column in the child table that will reference the parent table's primary key. parent_table is the name of the table where the primary key column resides, and parent_column_name is the name of the primary key column in the parent table.

A foreign key constraint ensures that there is referential integrity between the child and parent tables. It prevents you from inserting a row into the child table that doesn't have a corresponding row in the parent table.

Use

Foreign keys are used to establish a relationship between two tables in a relational database. They allow you to create meaningful connections between tables, which can be used to retrieve data in more sophisticated ways.

For example, in the above example, if we want to retrieve all orders of a particular customer, we can join the Orders and Customer tables on the customer_id.

Important Points

  • A foreign key references another table's primary key.
  • It ensures referential integrity between two tables.
  • It prevents you from inserting a row into the child table that doesn't have a corresponding row in the parent table.
  • It allows you to retrieve data in more sophisticated ways.

Summary

A foreign key is a key used to establish a connection between two tables in a relational database. It refers to a primary key of another table to establish the relationship between them. The syntax for creating a foreign key uses ADD CONSTRAINT keyword, and it ensures referential integrity between two tables. Foreign keys are used to retrieve data in more sophisticated ways.

Primary KeyComposite Key
Published on: