To understand the difference between PRIMARY KEY and FOREIGN KEY in SQL, you first need to understand the definition of a PRIMARY KEY as well as a FOREIGN KEY. SO,
- Read: More Interview Questions
PRIMARY KEY: It can be defined as a unique column or a group of few columns which are chosen to identify a particular row uniquely. There cannot be more than one PRIMARY KEY linked to one table and also, each table should have for sure one PRIMARY KEY. The characteristics of PRIMARY KEY can be included during the column definition as one of its part. For e.g. in a table named EMPLOYEE which contains the data of all the employees in a firm, the PRIMARY KEY can constitute the columns “Employee_Id”, “Employee_grade” and “Employee_section”.
FOREIGN KEY: It may be defined as a single column or group of few columns in a particular table when the values of chosen column or columns must have identical values in the PRIMARY KEY of a different (or same) Table. Defining a FOREIGN KEY is the best way to maintain the data integrity. For example If we take a table named GRADE, which consists the grade of all the employees working in a firm then any grade listed in the “Employee _grade” column of EMPLOYEE table should also exist in the Grade table. In this case you can declare the “Employee_grade” column as a FOREIGN KEY that references the “Grade” Column of the Grade table and ensures the existence of matching values in both the tables.
- Learn: Aptitude Learning
Now, let’s discuss the Difference between the PRIMARY KEY and the FOREIGN KEY in a Table:
- It is not necessary that both PRIMARY and FOREIGN KEYS have same names. You can define both the KEYS with different names as well.2. It is a fact that the value of a PRIMARY KEY cannot be NULL, but in case of FOREIGN KEY it can be NULL, For example we can have a record of EMPLOYEE with Employee_grade is equal to NULL. This implicates that there is no matching record is found in the GRADE table.
3. We all know that all the values of PRIMARY KEY are unique in nature but when it comes to FOREIGN KEY you may found some duplicate values or records.
4. PRIMARY KEY defines some unique values whereas FOREIGN KEY is a link between two distinguished tables when we make use of different JOINS (INNER JOIN and OUTER JOIN) over these tables. It helps in saving the data from any loss and managing it sequentially.
5. We can implement referential integrity in table relationships of a database and this leads to the implementation of data integrity.
6. The Table with a PRIMARY KEY is known as Parent Table while the table with FOREIGN KEY is known as a child table.
7. A PRIMARY KEY uniquely identifies a row in a table but a FOREIGN KEY may or may not uniquely identify a row in a table.
8. There can be only one PRIMARY KEY can exist or is allowed but there may be more than one FOREIGN KEY exit in a Table. Read Health website here.
9. The Index for PRIMARY KEY is created automatically or in other words it is indexed automatically but when it comes to FOREIGN KEY no index is created automatically.
- Recommended: Final year Academic projects download