Primary Key and Foreign Key in SQL: All You Need To Know

According to the Stack Overflow Developer Survey 2023, Structured Query Language (SQL) is the fourth most popular programming language used for data analysis. Business owners or managers set the rules to be applied to the data before it is stored in the database. For example, a marketing department employee may have a salary of less than 15,000 rupees. Such rules must be applied to the stored data. This special business rule that applies to the data stored in a table is called a constraint.

From this simple example, it becomes clear that constraints manage the data that enters the database for storage. Constraints apply to a column within a table or to the table itself. In this blog, you will learn more about the primary key and foreign key in SQL, their uses, and how to create, modify, and remove a constraint.

How do we Apply Data Constraints?

SQL constraints define rules for the data in a table. In a table, constraints limit the types of data that have to be entered, so that the data in the table is accurate and reliable. Constraints are found either at the column level or at the table level. Column-level constraints apply to a single column, while table-level constraints apply to the entire table. Some constraints used in SQL are: NOT NULL, CHECK, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CREATE INDEX.

Data constraints are applied to table columns using SQL syntax. Once the data constraint is a part of the table column, the database engine checks the data being entered into the table column against the data constraints. If the data passes this check, it is stored in the table column. Otherwise, it is rejected. Both the ‘Create Table’ and ‘Alter Table’ statements are used to attach constraints. Now let us discuss the primary key and foreign key in SQL with examples and their syntax.

What are a Primary Key and a Foreign Key in SQL?

A primary key is one or more columns in a table used to uniquely identify each row in the table. There can be a primary key that consists of two or more columns, called a composite primary key. A composite primary key is used when a single column is insufficient to identify the records.  

A foreign key represents relationships between tables. It is a column or a group of columns whose values are related to the primary key of some other table. A ‘child table’ defines a foreign key, and the table that a foreign key refers to as its parent table is called the ‘parent table’ (referenced table).

Use the REFERENCES table name (column_name) clause to define the foreign key reference.

Features of Primary Key and Foreign Key

Some of the key features of primary and foreign keys in SQL  are –

Features of Primary Key

• Uniqueness- The main purpose of a primary key is to maintain the uniqueness of the record. The data held in this column must not have duplicate values.
• Non-NULLIBILTY – A primary key column cannot be left blank.  
• No. of Primary Keys in a Table – Only one primary key is allowed per table. It can be a single-column or composite-column primary key.
• Relationship Establishment – It helps in relating tables with one another.
• Index – A unique index is created automatically if there is a primary key in a table, such that it helps in faster sorting and identifying of data.

Features of Foreign Key

• Specification of Foreign Key – Foreign key constraint is specified on the child table but not on the parent table.
• Specify the NULL and Duplicate Values – The child table may have  NULL and duplicate values. You have to specify them.
• Many-to-one Relationship – The multiple rows on the child table can be a reference to the same row in the parent table.
• No. of Foreign Keys in a Table – Any number of foreign keys can be referenced in a child table.

To understand how these keys in SQL are used for data analytics, you can take an online SQL course. 

Syntax of Primary Key and Foreign Key

To attach a constraint to a column or a table, we use syntax. The syntax to apply the primary key and foreign key is discussed below –

Primary Key Syntax

In this section, we will learn to define a primary key constraint at a column level, create a table with a primary key, add a primary key to an existing table, define a composite primary key, and delete a primary key from the table.

• Syntax to Define a Primary Key At a Column Level

             See the syntax and example below –

Column_name data type (size) PRIMARY KEY,

Example, 
Customer_no varchar(10) PRIMARY KEY,

• Syntax to Create a Table with Primary Key

Learn the syntax to create a table with primary key –

CREATE TABLE table_name
(
column1 data type(size) PRIMARY KEY,
column2 data type(size),
column3 data type(size)
…
column_n data type(size)
);

Example, 

CREATE TABLE Customer_list
(
Customer_ID varchar(10) PRIMARY KEY,
First_name varchar(25),
Last_name varchar(25),
Product_ID int
Customer_address varchar(30) ,
);

Output: Table created

In this syntax, table_name is the name of the existing table where you want to add a primary key, and column1 is the column’s name where the primary key is attached.

• Syntax to Add a Primary Key in an Existing Table: Use the ALTER TABLE and ADD CONSTRAINT statements to add a primary key to an existing table.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY(column1);

• Syntax to Create a Table with Composite Primary Key in Multiple Columns

 See the syntax and example below to create a composite primary key.

CREATE TABLE table_name
(
column1 data type(size),
column2 data type(size),
column3 data type(size),
PRIMARY KEY (colomn1,column2)
);

 In this syntax, “(column1, column2)” forms a composite primary key. The combination of values in column 1 and column 2 must be unique.

Example,

CREATE TABLE customer_list
(
Customer_ID int,
First_name varchar(25),
Last_name varchar(25),
Product_ID int
Customer_address varchar(30),
PRIMARY KEY(Customer_ID,Product_ID) ,
);

In this table, the combination of customer_ID and product_ID values must be unique in each table row. It means that no two customers can have the same customer_id and product_id.

• Syntax to Drop a Primary Key

Use the ALTER TABLE and DROP PRIMARY KEY statements in syntax to delete a primary key.

ALTER TABLE table_name
DROP PRIMARY KEY;

Example,

ALTER TABLE Customer_list

DROP PRIMARY KEY;

The primary key “Customer_ID” will be removed from the table Customer_list.

Foreign Key Constraint

In this section, we will learn the syntax to create a table with a foreign key, add a foreign key to an existing table, and delete a foreign key in a table.

• Syntax to Create Table With a Foreign Key

See below the syntax and its example to attach a foreign key to a table-

CREATE TABLE table_name
(
column1 data type PRIMARY KEY,
column2 data type,
column3 data type,
…
column_n data type,
ADD FOREIGN KEY (column_name) REFERENCES referenced_table (referenced_column);
);

When using the syntax for defining a foreign key, the “referenced table” is the table with the primary key that the foreign key refers to. The “referenced column” is the specific column in the referenced table that connects to the foreign key.

Example,

First, create a parent table –

CREATE TABLE Customers
(
customer_ID int PRIMARY KEY,
customer_name varchar(30),
);

Second, create a child table –

CREATE TABLE Orders
(
       	order_ID int PRIMARY KEY,
customer_ID int,
order_date date,
ADD FOREIGN KEY (customer_ID) REFERENCES Customers(customer_ID);
);

In this example, the Orders table’s foreign key constraint column references the customer_ID column of the Customers table. As a result, there is a link between the two tables, in which each order connects to a particular customer.

• Syntax to Add Foreign Key in an Existing Table

Use the ALTER and ADD FOREIGN KEY statements to add a foreign key to an existing table.

ALTER TABLE table_name

ADD FOREIGN KEY (column_name) REFERENCES referenced_table (referenced_column);

Example

ALTER TABLE Order
ADD FOREIGN KEY (customer_ID) REFERENCES Customers (customer_ID);

• Syntax to Drop Foreign Key

Use the ALTER TABLE and DROP FOREIGN KEY statements to delete a foreign key from the table.

ALTER TABLE table_name
DROP FOREIGN KEY foreign_key_name;

Example,

ALTER TABLE Orders
DROP FOREIGN KEY customer_ID;

Difference Between a Primary Key and a Foreign Key in SQL

Here is the difference between primary keys and foreign keys in SQL- 

PRIMARY KEY	FOREIGN KEY
It uniquely identifies the data in each row.	It establishes a relationship between two tables by referencing a primary key in another table.
It ensures data integrity by not allowing any null or duplicate values in a column or table.	It ensures referential integrity by verifying that the value in the foreign key column exists in the primary key column of the referenced table.
It is specified within the same table where the column is identified.	The foreign key in the child table is used to link to another table where the primary key is referenced.
Each value is unique and not null in a primary key column.	Values in the foreign key column might be duplicated or null unless the foreign key with constraints – (NOT NULL and CASCADE) is mentioned.
The primary key constraint defines itself automatically.	A parent table with a primary key is needed to define a foreign key.
A primary key can be defined for temporary tables.	A foreign key cannot be defined for the temporary tables.

Conclusion

The primary key and foreign key in SQL are the two main constraints used in tables and relational databases. It makes the navigation of matching the data between tables using a foreign key and maintaining clean and non-duplicate data within a table easy with a primary key. You can practice the queries to define both keys and enhance your understanding of how to use them in the table(s).