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

A foreign key is a column or a set of columns in one table that references the primary key of another table. Foreign keys are used to establish and enforce a link between the data in two tables, ensuring referential integrity in the relational database system.

In this article, we will explain how to create tables with foreign keys in SQL, with multiple examples and outputs, to help us understand the process.

Why Use Foreign Keys in SQL?

Foreign keys are essential for maintaining the relationship between tables and ensuring data consistency. They help enforce the referential integrity of the database, meaning that a record in one table must have a corresponding valid record in another table. This prevents the creation of "orphaned" records and ensures data accuracy across the database.

Syntax

CREATE TABLE TABLE_NAME(
    Column 1 datatype,
    Column 2 datatype,
    Column 3 datatype FOREIGN KEY REFERENCES Table_name(Column name),
    ..
    Column n )

Key Terms

• Column1, Column2, Column3 represent the columns of the table.
  
• FOREIGN KEY (ColumnName) specifies the column that will act as a foreign key.
  
• REFERENCES ReferencedTable(ReferencedColumn) links the foreign key column to a primary key column in another table.

Example: Creating a Table with a Foreign Key

Let's assume we are managing a customer relationship database where each sale is linked to a specific customer. Here's how we can create a Customer table and a Sales table, with a foreign key in Sales referring to the Customer table.

Step 1: Create the Customer Table

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

Step 2: Insert the values into the Customer Table

After creating the above table, we can use the following SQL INSERT INTO statement:

INSERT INTO Customer (Customer_id, Customer_name, Customer_address)
VALUES
(101, 'Geek 1', 'Chennai'),
(102, 'Geek 2', 'Delhi'),
(103, 'Geek 3', 'Bombay'),
(104, 'Geek 4', 'Pune'),
(105, 'Geek 5', 'Nashik');

Output

Step 3: Create the Sales Table with a Foreign Key

Now, create the Sales table, where Customer_id will be a foreign key referring to the Customer_id in the Customer table. We can create the table using the following command.

CREATE TABLE Sales (
    Sale_id INT PRIMARY KEY,
    Customer_id INT,
    Item_id INT,
    Payment_mode VARCHAR(20),
    FOREIGN KEY (Customer_id) REFERENCES Customer(Customer_id)
);

Step 4: Insert the values into the Sales Table

After creating the above table, we can use the following SQL INSERT INTO statement:

INSERT INTO Sales (Customer_id, Item_id, Payment_Mode)
VALUES
(101, 1334151, 'COD'),
(101, 16652, 'Debit Card'),
(104, 1009, 'Paypal'),
(102, 14543, 'COD');

Step 3: Verify the Data and Foreign Key Relationship

To verify that the foreign key relationship works, use the DESCRIBE command:

DESCRIBE Sales;

Output

Explanation:

• In the Sales table, the Customer_id column is a foreign key.
  
• This foreign key references the Customer_id column in the Customer table, ensuring that each sale record corresponds to a valid customer in the Customer table.

Other Methods to Create a Table with Foreign Key in SQL

There can be different ways of using the FOREIGN KEY constraint depending on our preference or specific requirements to create a table with foreign key in SQL.

Method 1: FOREIGN KEY Constraint After Column Declaration

Here, we will first declare all the columns, and then use foreign key constraint on the desired column. Now in order to create the same sales table using customer table we can run the following command.

Query:

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

Output

Method 2: Create Table with Foreign Key consisting of Multiple Attributes 

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

Query:

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

Output

Explanation:

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.

SQL FOREIGN KEY ON ALTER TABLE 

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

Query:

ALTER TABLE Retailer
ADD FOREIGN KEY (Retailer_id) 
REFERENCES Customer(Customer_id);

Output

Explanation:

• 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 that is being referred. 

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

PostgreSQL/ SQL Server 

ALTER TABLE table_name
DROP CONSTRAINT foreign_key_name;

Conclusion

Creating and managing foreign keys in SQL is important for maintaining referential integrity between related tables in a database. Whether we are creating a new table or altering an existing one, foreign keys ensure that the data remains consistent and that relationships between tables are well-defined.

By implementing foreign keys properly, we can ensure the integrity and consistency of our relational database while efficiently managing the relationships between our data. Whether we are using simple or composite foreign keys, it’s important to understand the core concepts and apply them to optimize our database design.