Hello guys, if you are preparing for database or developer interview then questions form primary key is very common on interviews. One of such question is primary key vs foreign key which is asked ot me multiple times during early stage of my career. Now my experience has grown so I don't see this question quite often but the concept is still very important for any database admin or programmer to understand.
The key difference between primary key and foreign key is that primary key on one table can be foreign key on other related table.
For example, you have two tables Employee and Department, each have primary key emp_id and dept_id, now if you want to link or join these two table then you created a column called dept_id on employee table to indicate which department a particular employee work and now dept_id is a foreign key in Employee table.
This serves multiple purposes when it comes to query and maintaining database integrity. For example, it allows you to pull additional data like department_name form Department table using dept_id using SQL joins.
It also can maintain integrity by avoiding you to add a deprt_id which doesn't exists in department table or delete all employee or migrate them to another department when a particular department is closed. So solid understanding of primary key and foreign key concept is very important not just for interviews but also for working with database in general.
'
What is Database and Primary Key?
Database is collection of tables and a table is 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 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 enforce some constraint e.g. UNIQUE and NOT NULL, which means if you try to enter another record with primary key already existing in database than DBMS will reject it.
Similarly, primary key cannot be NULL, it must have some value. On the other hand, foreign key is just the primary key of another table.
In relational database, two tables can be related to each other e.g. Employee and Department, those cases, primary key of one table becomes foreign key of another table. In this article, you will learn some key difference between the Primary and foreign key in table.
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 the clustered index in table.
2) Primary key is unique in the table. So a table cannot contains more than one row with 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 unique id but many of them working for same department will have same dept_id.
3) Foreign key helps to maintain the integrity of related tables in 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.
Difference between Primary and Foreign key in table
As I said, if two table are 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 the clustered index in table.
2) Primary key is unique in the table. So a table cannot contains more than one row with 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 unique id but many of them working for same department will have same dept_id.
3) Foreign key helps to maintain the integrity of related tables in 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 secondary table if a row with the primary key doesn't exists in Primary key e.g. adding a non existent department to an employee's profile.
Similarly, you cannot insert the data in secondary table if a row with the primary key doesn't exists in Primary key e.g. adding a non existent department to an employee's profile.
Example of primary key and foreign key in database
While Employee and Department is the classic and most popular example to understand primary key and foreign key concept, Here is another example to illustrate the concept of primary key and foreign key in a database. This will make your understanding even better.
Consider a database for a library system. The library has a table for books (Book) and a table for authors (Author).
Primary Key
In the Book table, the ISBN number can be used as the primary key. A primary key is a unique identifier for each record in a table, and is used to enforce data integrity. For example, if two books have the same ISBN, they would be considered duplicates and would violate the primary key constraint.
Foreign Key
In the Author table, the author ID can be used as the primary key. In the Book table, the author ID can be used as a foreign key, which is a reference to the primary key of another table. The foreign key enforces referential integrity, ensuring that a book cannot reference an author that does not exist in the Author table.
Here is an example of what the database tables may look like:
Book:
ISBN Title Author ID
123 Book1 1
456 Book2 2
789 Book3 1
Author:
Author ID Name
1 John
2 Jane
In this example, the ISBN is the primary key in the Book table and the author ID is the primary key in the Author table. The author ID in the Book table is also a foreign key, referencing the author ID in the Author table.
Summary
Primary Key and Foreign Key are two important concepts in relational database management systems like SQL. A primary key is a unique identifier for each record in a database table. It is used to enforce data integrity and ensure that each record in a table is unique.
A primary key can be composed of one or more columns, but each record must have a unique combination of values for these columns.
On the other hand, A foreign key is a reference to the primary key of another table. It is used to enforce referential integrity, ensuring that data in one table is consistent with data in another table. For example, if a book record in a books table references an author who doesn't exist in the authors table, a foreign key constraint would prevent this inconsistent data from being stored.
In summary, a primary key is a unique identifier for each record in a table, while a foreign key is a reference to the primary key of another table.
Also, here is a nice table to review and remember difference between primary key and foreign key in a table:
Other SQL and Database Articles you may like:
- Difference between UNION and UNION ALL in SQL
- 10 Examples of ALTER command in SQL
- MySQL vs NoSQL comparison
- Difference between Self and Equi join in SQL
- What are T-SQL Window Functions?
- How to use Stored Procedure in SQL?
- 4 Examples of CASE expression in SQL Server
- How to create and rollback transaction in database?
- How to convert String to Date in SQL?
- How to use WHERE and HAVING clause in SQL
- How to find top 10 Records in SQL
- Difference between Truncate, Drop, and Delete in SQL
Thanks for reading this article so far. If you have any questions or doubt feel free to ask.
No comments:
Post a Comment