Open In App

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

Improve
Improve
Like Article
Like
Save
Share
Report

Pre-requisite: Structured Query Language 

SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, PostgreSQL, etc. In order to create a table with Foreign Key, we must have to know few Key Constraints:

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.

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

Super Key

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

Example: 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.

Example: 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.

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.

Method 1: SQL FOREIGN KEY ON CREATE TABLE 

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: SQL FOREIGN KEY ON CREATE TABLE 

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.

Method 3: SQL FOREIGN KEY ON CREATE TABLE 

Foreign key can be consist of more than two attributes. Below is the implementation of foreign key consisting of multiple attributes referring to multiple columns of another table.

Customer_id Customer_name Customer_Address Payment_id
101 Geek 1 Chennai P100
102 Geek 2 Delhi P200
103 Geek 3 Bombay P300
Customer_id Item_Id Payment_Mode Payment_id
101 1334151 COD P100
101 16652 Debit Card P200
CREATE TABLE SALES (
  Customer_id INT PRIMARY KEY,
  Item_id INT,
  Payment_Mode varchar(20),
  Payment_id INT,
  CONSTRAINT customer_payment_id
  FOREIGN KEY (customerId, paymentId) 
  REFERENCES Customer(customerId, paymentId)
);

The above examples show reference of foreign keys consisting of more than one attribute. The constraint customer_payment_id is a foreign key containing two columns Customer_id and Payment_id. These foreign key refers to the two columns of parent table Customer – Customer_id, Payment_id

Method 4: SQL FOREIGN KEY ON ALTER TABLE 

Foreign key can also be added in the existing table. The following method will illustrate the same.

Retailer_id Order_Purchased
101 Item1
102 Item2
103 Item3
ALTER TABLE Retailer
ADD FOREIGN KEY (Retailer_id) 
REFERENCES Customer(Customer_id);
  • Here the table Retailer is an existing table containing Retailer_id as an attribute. 
  • Now we are altering the same table using the ALTER TABLE clause in dbms.
  • The column name that serves as the foreign key will come after the ADD FOREIGN KEY clause.
  • After the REFERENCE clause we need to specify the Primary key and the table table that is being referred.
     

Dropping a Foreign Key Constraint

To remove a foreign key from a table requires execution of  the correct SQL statement, which will be different for each database management system (DBMS). The following are some instances of well-known DBMS:

MYSQL

ALTER TABLE table_name
DROP FOREIGN KEY foreign_key_name;

The above query drops a foreign key from a desired table. The table_name need to be replaced with the table name which contains foreign key and the foreign_key_name is the foreign key constraint name that we want to delete.

PostgreSQL/ SQL Server 

ALTER TABLE table_name
DROP CONSTRAINT foreign_key_name;

Conclusion

In this we have discussed about the different types of keys in database and the different ways of implementing foreign key in newly created table as well existing table. We have also mentioned the different syntax of dropping foreign key in different database that we use.



Last Updated : 29 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads