In SQL, keys are used to identify records present in a database. The keys used in SQL may look identical but their behaviour is entirely different. The difference between primary, unique and foreign keys is one of the most important concepts in relational database management. The difference between keys is also a common SQL interview question.
What is primary key?
The primary key is a candidate key which is used to search the records present in a database. Any candidate key used to search the records is spontaneously declared as a primary key.
What is unique key?
The unique is a type of primary key which is used to uniquely identify all the rows present in a table. The unique key can be either normally attributed in a table or created by DBMS. The unique key created by DBMS have unique identifiers throughout the globe.
What is foreign key?
Foreign keys are the keys which are represented as column in one table and as primary key in another table. Usually foreign keys are used to define the relationship between the tables in a database.
- Read: More Interview Questions
Difference between Primary and Foreign Key in SQL?
- Primary key is used to find records present in a table while foreign key is a field in one table and act as primary key in another table. Foreign key is used to relate between two tables present in a database.
- Multiple null values are accepted in foreign keys. No null values are allowed in primary keys.
- Primary keys are always unique while it is possible to duplicate foreign keys.
- Referential integrity can be established between tables using constraints of foreign keys.
- Foreign keys are used to relate tables during inner and outer joins. It is impervious to use primary key between the tables.
- Only one primary key is allowed in a table while number of foreign keys used in a table is not restricted.
- The column in which a primary key is declared is known as “parent table”. The column in which foreign key is declared is known as “child table”.
- Primary key is a clustered index which is created by default. Index in foreign key can be either created by automatic and manual. Foreign can be either clustered or non-clustered.
Difference between Primary and Unique Key in SQL?
- Unique key can be declared as NULL but declaring primary key as NULL is not allowed.
- A primary key can be a combination of several unique keys.
- Primary keys are organized as sequential clustered index while unique key are declared as non-clustered index.
- Only one primary key is allowed in a table but more than one unique key is permitted.
- Primary key is created by primary key constraint while unique key is created by unique constraint.
- Unlike foreign keys only one null value is allowed in unique keys.