The SQL Foreign Key

In SQL, a Foreign Key is a column in one table that matches a Primary Key in another table, allowing the two tables to be connected together.

A foreign key also maintains referential integrity between two tables, making it impossible to drop the table containing the primary key (preserving the connection between the tables).

The foreign key can reference the unique fields of any table in the database. The table that has the primary key is known as the parent table and the key with the foreign key is known as the child table.

Let”s consider an example scenario, assume we have two tables namely CUSTOMERS (ID, NAME, AGE, ADDRES, SALARY) and ORDERS (ID, DATE, CUSTOMER_ID, AMOUNT). Here the id of the customer is primary key (ID) in the CUSTOMERS table and foreign key in the ORDERS (CUSTOMER_ID) table observe the following diagram −

Features of Foreign Key

Following is the of features of Foreign Key −

• A Foreign Key is used to reduce the redundancy (or duplicates) in the table.

• It helps to normalize (or organize the data in a database) the data in multiple tables.

Syntax

Following is the basic syntax to add Foreign Key constraints on a column of a table in MySQL database −

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

Example

Let us create two tables with the names CUSTOMERS and ORDERS. The following query creates a table with the name CUSTOMERS −

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)
);

Output

Following is the output of the above SQL statement −

Query OK, 0 rows affected (0.02 sec)

Now, let us create the ORDERS table. While doing so, we add the foreign key constraint on column CUSTOMER_ID reference on column ID of the CUSTOMERS table as shown in the statement below −

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)
);

Output

The above statement produces the following output −

Query OK, 0 rows affected (0.04 sec)

Verification

We have created a Foreign Key Constraint on a column named CUSTOMER_ID in the ORDERS table that references the column named ID of the CUSTOMERS table; so you can”t drop table1 (CUSTOMERS) before dropping the table2 (ORDERS).

First of all, let”s drop the CUSTOMERS table without dropping the ORDERS table using the DROP TABLE statement −

DROP TABLE CUSTOMERS; 

If you verify the error message below, you will observe that it says that the table can not be dropped because it is referenced by a FOREIGN KEY constraint −

ERROR 3730 (HY000): Cannot drop table ''customers'' referenced by a foreign key constraint ''FK_CUSTOMER'' on table ''orders''.

Foreign Key Constraint on an Existing Column

We can also create a Foreign key constraint on a column of an existing table. This is useful when you forget to add a Foreign Key constraint on a column while creating a table, or when you want to add this constraint on another column even if one Foreign Key column exists in a table.

Syntax

Using the ALTER TABLE statement we can add a Foreign Key constraint on an existing column in a table in MySQL database as shown below −

ALTER TABLE TABLE2 
ADD CONSTRAINT[symbol] 
FOREIGN KEY(column_name) 
REFERENCES TABLE1(column_name);

Here, FK_ORDERS is the name of the foreign key constraint. It is optional to specify the name of a constraint but it comes in handy while dropping the constraint.

Example

Assume the CUSTOMERS and ORDERS tables have already been created in the SQL database. Now, we will add a Foreign Key Constraint on the ID column of the ORDERS table.

Following is the SQL query to add the foreign key constraint on an the column of an existing table −

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

Output

Following is the output of the above program −

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

We have created a Foreign Key Constraint on a column named CUSTOMER_ID in the ORDERS table that references the column name ID of the CUSTOMERS table. So, you can”t drop table1 (CUSTOMERS) before dropping the table2 (ORDERS).

First of all, let us drop the CUSTOMERS table without dropping the ORDERS table by executing the following statement −

DROP TABLE CUSTOMERS;

This generates an error message saying that the table can not be dropped because it is referenced by a FOREIGN KEY constraint −

ERROR 3730 (HY000): Cannot drop table ''customers'' referenced by a foreign key constraint ''FK_CUSTOMER'' on table ''orders''.

Dropping a FOREIGN KEY

You can drop the foreign key from a table, without dropping that entire table, using the ALTER TABLE statement.

Syntax

Following is the syntax to drop the FOREIGN key constraint from the column of the table using the ALTER TABLE statement−

ALTER TABLE table_name DROP FOREIGN KEY (constraint symbol);

Where, FK_NAME is the name of the foreign key constraint you need to drop.

Example

The SQL query to drop the foreign key constraint from the column of a table is as follows −

ALTER TABLE ORDERS DROP FOREIGN KEY FK_ORDERS;

Output

Following is the output of the above SQL query −

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Since we have dropped the Foreign key constraint from the ORDERS table, you can now directly drop the CUSTOMERS table without dropping the ORDERS table, as shown below −

DROP TABLE CUSTOMERS;

If you verify the below status code thrown by the above SQL command, you observe that the CUSTOMERS table has dropped.

Query OK, 0 rows affected (0.02 sec)

Primary Key vs Foreign Key

Even though both the primary key and foreign key refer to the same column, there are many differences to be observed in the way they work. They are listed below −