Keys are one of the most important elements in a relational database to maintain the relationship between the tables and it also helps in uniquely identifying the data from a table. The primary key is a key that helps uniquely identify the tuple of the database. In contrast, the Foreign Key is a key used to determine the relationship between the tables through the primary key of one table that is the primary key of one table acts as a foreign key to another table. Now, let's discuss both of them in some detail.
What is Primary Key?
A primary key is used to ensure that data in the specific column is unique. A column 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: STUD_NO, as well as STUD_PHONE, are candidate keys for relation STUDENT but STUD_NO can be chosen as the primary key (only one out of many candidate keys).
Table STUDENT:
| STUD_NO | STUD_NAME | STUD_PHONE | STUD_STATE | STUD_COUNT | STUD_AGE |
|---|---|---|---|---|---|
| 1 | RAM | 9865278251 | Haryana | India | 20 |
| 2 | RAM | 9655470231 | Punjab | India | 19 |
| 3 | SUJIT | 7514290359 | Rajasthan | India | 18 |
| 4 | SURESH | 8564103258 | Punjab | India | 21 |
Table STUDENT_COURSE:
| STUD_NO | COURSE_NO | COURSE_NAME |
|---|---|---|
| 1 | C1 | DBMS |
| 2 | C2 | Computer Networks |
| 1 | C2 | Computer Networks |
What is 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: STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in STUDENT relation.
Difference Between Primary Key and Foreign Key
| PRIMARY KEY | FOREIGN KEY |
|---|---|
| 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. |
| 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. |
| Only one primary key is allowed in a table. | Whereas more than one foreign key is allowed in a table. |
| It is a combination of UNIQUE and Not Null constraints. | It can contain duplicate values and a table in a relational database. |
| It does not allow NULL values . | It can also contain NULL values. |
| Its value cannot be deleted from the parent table. | Its value can be deleted from the child table. |
| It constraint can be implicitly defined on the temporary tables. | It constraint cannot be defined on the local or global temporary tables. |