The database is a collection of tables and a table is the collection of rows or records. A primary key is the data to uniquely identify a row in the table. For example, if an Employee table has millions of rows and emp_id is a primary key then it can uniquely identify that row. You can use this primary key to retrieve (SELECT) the row, update the row and delete the row. In SQL, a table can have only one primary key but it can contain more than one columns. There are two types of primary key, a simple primary key, and a composite primary key. A simple primary key is made of just one column while a composite primary key is made of more than one columns. Primary key also enforces some constraint e.g. UNIQUE and NOT NULL, which means if you try to enter another record with the primary key already existing in the database than DBMS will reject it. Similarly, a primary key cannot be NULL, it must have some value. On the other hand, the foreign key is just the primary key of another table. In a relational database, two tables can be related to each other e.g. Employee and Department, those cases, the primary key of one table becomes the foreign key of another table. In this article, you will learn some key difference between the Primary and foreign key in a table.
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.