How to Create a Foreign Key?

A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.

The structure of the tables would be:



In the above example, the Customer_ID column in the ORDERS table is a foreign key pointing to the ID_Number column in the CUSTOMER table.

We are going to create the relationship and referential integrity of the two tables using the given syntax below.


CREATE TABLE table_name(field_name datatype(index),…fieldnameN datatype(index),PRIMARY KEY(field_name_primary_key),FOREIGN KEY(foreignkey_fieldName) REFERENCES table_name(foreign_key_field));

Now, you are ready to do the coding part. Here is the code:

CREATE TABLE EMPLOYEE(emp_num int(10),emp_name varchar(50),position varchar(25),Cust_num int(6),PRIMARY KEY(emp_num),FOREIGN KEY(Cust_num) REFERENCES customer(ID_Number));

And the output is: