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 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).
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 the figure –
STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in STUDENT relation.
|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 are 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.||It constraint cannot be defined on the local or global temporary tables.|
Difference between Primary and Foreign key in table : As I said, if two table is in relation with each other than primary key of one table becomes the foreign key of another table. Let’s see some more differences between Primary and Foreign key in SQL:
1) One of the major difference between these two keys is that primary key enforces clustered index, which is the actual physical order of how data is stored in the disc. On the other hand, 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 rows in a table with 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 difference 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 setup cascade actions with foreign key e.g. when a department is deleted, update the dept_id of all employees in Employee table referring to this department. You can setup such arrangement using ON CASCADE clause. Similarly, you cannot insert the data in the secondary table if a row with the primary key doesn’t exist in 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 key in a table is that 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.