The SQL Foreign Key

In SQL, a Foreign Key is a column in one table that matches a Primary Key in another table.
It connects the two tables, enabling a relationship between them and ensuring referential integrity.

Syntax

Below is the basic syntax for creating a Foreign Key in a table:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT fk_name 
    FOREIGN KEY (column_name) 
    REFERENCES referenced_table(referenced_column)
);
    

Example: Creating a Foreign Key

Let us create two tables: CUSTOMERS and ORDERS. In the example, the ID column
in the CUSTOMERS table is a Primary Key, and the CUSTOMER_ID column in the ORDERS
table is the Foreign Key referencing the ID column.

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
        

CREATE TABLE ORDERS (
   ID INT NOT NULL,
   DATE DATETIME, 
   CUSTOMER_ID INT,
   CONSTRAINT FK_CUSTOMER 
   FOREIGN KEY(CUSTOMER_ID) 
   REFERENCES CUSTOMERS(ID),
   AMOUNT DECIMAL,
   PRIMARY KEY (ID)
);
        

Adding a Foreign Key to an Existing Table

If you forgot to add a Foreign Key while creating a table, you can use the ALTER TABLE statement to add it later:

ALTER TABLE ORDERS 
ADD CONSTRAINT FK_ORDERS 
FOREIGN KEY(CUSTOMER_ID) 
REFERENCES CUSTOMERS(ID);
    

Dropping a Foreign Key

To drop a Foreign Key from a table, use the ALTER TABLE statement:

ALTER TABLE ORDERS DROP FOREIGN KEY FK_ORDERS;
    

Important Notes