SQL Foreign Key – Understanding SQL Foreign Key, Syntax and Examples

Use of foreign keys in SQL enhances the relationship between tables and ensures data integrity.

1. Introduction to SQL Foreign Key

In SQL, a Foreign Key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In simpler terms, it is a column or a set of columns used to establish a link between the data in two tables.

The table that contains the foreign key is called the child table, and the table that is uniquely identified by it is known as the parent table. It’s essential to understand that this “parent-child” relationship is a cornerstone of maintaining data integrity in a relational database.

Foreign keys allow us to perform operations like ‘JOIN’ more effectively and enable us to enforce certain types of data integrity constraints.

2. SQL Foreign Key Syntax

Let’s take a look at the general syntax to create a foreign key on a table:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
    FOREIGN KEY (column1, column2, ...)
    REFERENCES parent_table (column1, column2, ...)
);

In this syntax:

• table_name: is the name of the table.

• column1, column2, column3,…: are the names of the columns in the new table.

• datatype: defines the type of data the column can hold (e.g., varchar, integer, date, etc.)

• FOREIGN KEY: is used to indicate that you are defining a foreign key.

• REFERENCES: is a keyword used to specify the table and column(s) from which the foreign key gets its values.

3. Example of SQL Foreign Key

Consider two related tables, ‘Orders’ and ‘Customers’, in a database of an e-commerce website.

The ‘Customers’ table is:

CREATE TABLE Customers (
    CustomerID int NOT NULL,
    CustomerName varchar(255) NOT NULL,
    ContactName varchar(255),
    Country varchar(255),
    PRIMARY KEY (CustomerID)
);

The ‘Orders’ table is:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustomerID int,
    OrderDate date,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

In this case, ‘CustomerID’ in the ‘Orders’ table is a foreign key that points to ‘CustomerID’ in the ‘Customers’ table. The ‘Orders’ table is the child table and the ‘Customers’ table is the parent table.

4. Data Integrity with SQL Foreign Key

The SQL foreign key provides two types of data integrity:

1. Referential integrity: This means that the foreign key value for a record in the child table must match the primary key value for a record in the parent table.

2. Restrictive action: SQL allows you to enforce what actions will be taken when an attempt is made to delete or update a row to which existing foreign keys point. The keywords for this are ON DELETE and ON UPDATE, which can have the following options:

   • CASCADE: Automatically update or delete the records in the child table when the corresponding record in the parent table is updated or deleted.

   • SET NULL: Set the foreign key value to NULL in the child table if the corresponding record in the parent table is deleted or updated.

   • NO ACTION: No action is taken when the parent record is updated or deleted. However, you cannot delete data in the parent table that is being used by the child table.

   • SET DEFAULT: Set the foreign key to its default value if the corresponding record in the parent table is deleted or updated.

5. Conclusion

SQL foreign keys are a powerful tool in maintaining the relationships between tables in a database. They not only help in linking the data between different tables but also play a critical role in maintaining the data integrity within your database.

Understanding and implementing foreign keys are a must for any SQL developer aiming to design efficient, robust databases.