Primary Key Vs Foreign Key In SQL

Hello Everyone. Hope you are doing well. In this article, you can learn the difference between Primary Key Vs Foreign key in SQL.

Primary Key:
A primary key is used to ensure data in the specific column is unique. It is a column that cannot have NULL values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

Example: Refer to the figure –
STUD_NO, as well as STUD_PHONE both, are candidate keys for relation STUDENT but STUD_NO can be chosen as the primary key (only one out of many candidate keys).

Foreign Key:
A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It is a column (or columns) that references a column (most often the primary key) of another table.

Example: Refer to the figure –
STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in STUDENT relation.

Let check out the difference between Primary Key Vs Foreign key in SQL

S.NO.	PRIMARY KEY	FOREIGN KEY
1	A primary key is used to ensure data in the specific column is unique.	A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables.
2	It uniquely identifies a record in the relational database table.	It refers to the field in a table which is the primary key of another table.
3	Only one primary key is allowed in a table.	Whereas more than one foreign key is allowed in a table.
4	It is a combination of UNIQUE and Not Null constraints.	It can contain duplicate values and a table in a relational database.
5	It does not allow NULL values.	It can also contain NULL values.
6	Its value cannot be deleted from the parent table.	Its value can be deleted from the child table.
7	It constraint can be implicitly defined on the temporary tables.	Its constraint cannot be defined on the local or global temporary tables.

Difference between Primary and Foreign key in the table: As I said, if two tables are in relation with each other then the primary key of one table becomes the foreign key of another table. Let’s see some more differences between Primary and Foreign keys in SQL:

1) One of the major differences between these two keys is that the primary key enforces clustered index, which is the actual physical order of how data is stored in the disc. On the other hand, the Foreign key doesn’t create a clustered index in the table.

2) The primary key is unique in the table. So a table cannot contain more than one row with the same primary key, but that’s not required for foreign key. You can have more than one row in a table with the same foreign key e.g. all employees will have a unique id but many of them working for the same department will have the same dept_id. This is one of the main differences between a unique key and a primary key.

3) Foreign key helps to maintain the integrity of related tables in the database. For example, it’s not possible to delete a department, unless a single employee is referring that. So, an accidental delete of such department will be prevented by database management systems e.g. Oracle, MySQL, or SQL SERVER. You can further set up cascade actions with foreign key e.g. when a department is deleted, update the dept_id of all employees in the Employee table referring to this department. You can set up such an arrangement using the ON CASCADE clause. Similarly, you cannot insert the data in the secondary table if a row with the primary key doesn’t exist in the Primary key e.g. adding a non-existent department to an employee’s profile. This is also known as referential integrity in the database.

4) Another key difference between the primary and foreign keys in a table is that the primary key cannot accept null, but the foreign key can accept multiple values.

5) The Primary key uniquely identifies a record in the table, but the foreign key can not be used for that purpose, it’s just the primary key of the related table.