How to Create a Table With a Foreign Key in SQL?

Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, PostGre, etc.

In order to create a table with Foreign Key, we must have to know few Key Constraints:

Super Key :

An attribute, or set of attributes, that uniquely identifies a tuple within a relation.

Ex: Consider a customer database then,

The customer_id of the relation customer is sufficient to distinguish one tuple from another. Thus, customer_id is a super key. Similarly, the combination of customer_id and customer_name is a super key for the relation customer. Here the customer_name is not a super key, because several people may have the same name.

Candidate Key :

A super key such that no proper subset is a super key within the relation.

Ex: The combination of customer_name and customer_street is sufficient to distinguish the members of the customer relation. Then both, {customer_id} and {customer_name,customer_street} are candidate keys. Although customer_id and customer_name together can distinguish customer tuples, their combination does not form a candidate key , since the customer_id alone is a candidate key.

Primary Key :

The candidate key is selected to identify tuples uniquely within the relation. Out of all the available candidate keys, a database designer can identify a primary key. The candidate keys that are not selected as the primary key are called alternate keys.

Ex: For the customer relation, we can choose customer_id as the primary key.

Foreign Key :

Foreign keys represent the relationships between tables. A foreign key is a column (or a group of columns) whose values are derived from the primary key of some other table. The table in which a foreign key is defined is called a Foreign table or Details table. The table that defines the primary key and is referenced by the foreign key is called the Primary table or Master table.

We can add a foreign key to a relation in the following ways

Method-1 :

Syntax :

CREATE TABLE TABLE_NAME(
    Column 1 datatype,
    Column 2 datatype,
    Column 3 datatype FOREIGN KEY REFERENCES Table_name(Column name), //Column which has to be a forigen key
    ..
    ..
    Column n
)

Consider The following customer relationship

Customer_id	Customer_name	Customer_Address
101	Geek 1	Chennai
102	Geek 2	Delhi
103	Geek 3	Bombay
104	Geek 4	Pune
105	Geek 5	Nashik

In order to create the following table, we use the following command

CREATE TABLE Customer(
    Customer_id int primary key,
    Customer_name varchar(20),
    Customer_Address varchar(20),
)

Now Consider the  Sales relation,

Customer_id	Item_Id	Payment_Mode
101	1334151	COD
101	16652	Debit Card
104	1009	Paypal
102	14543	COD

So in this relation, Customer_id is a foreign key that is obtained from the above customer relation. We can create the table using the following command.

CREATE TABLE SALES(
    Customer_id int FOREIGN KEY REFERENCES Customer(Customer_id)
    Item_id int,
    Payment_Mode varchar(20), 
)

Method -2 :

Syntax :

CREATE TABLE TABLE_NAME(
    Column 1 datatype,
    Column 2 datatype,
    Column 3 datatype  //Column which has to be a forigen key
    ..
    ..
    Column n
    CONSTRAINT Constraint_name FOREIGN KEY (Column name) REFERENCES Table_Name(Column name),
)

Now in order to create the same sales table using customer table we can run the following command

CREATE TABLE SALES(
    Customer_id int,
    Item_id int,
    Payment_Mode varchar(20),
    CONSTRAINT FK_Sales FOREIGN KEY (Customer_id)REFERENCES Customer(Customer_id)
)

Customer_id	Item_Id	Payment_Mode
101	1334151	COD
101	16652	Debit Card
104	1009	Paypal
102	14543	COD

Then we get the following table with customer_id as the foreign key.

My Personal Notes arrow_drop_up

Save