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 column. 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 column. The primary key also enforces some constraints like UNIQUE and NOT NULL, which means if you try to enter another record with the primary key already existing in the database then 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 like Employee and Department, in those cases, the primary key of one table becomes the foreign key of another table.
In this article, you will learn some key differences between the Primary and foreign keys in a table. This is also one of the most common SQL Interview questions for 1 to 2 years of experienced programmers.
1. One of the major differences between these two keys is that the primary key enforces clustered index, which is the actual physical order of how data is stored in the disc. On the other hand, the 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 a foreign key. You can have more than one row in a table with the 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 differences 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 a department will be prevented by database management systems like Oracle, MySQL, or SQL Server.
A simple primary key is made of just one column while a composite primary key is made of more than one column. The primary key also enforces some constraints like UNIQUE and NOT NULL, which means if you try to enter another record with the primary key already existing in the database then 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 like Employee and Department, in those cases, the primary key of one table becomes the foreign key of another table.
In this article, you will learn some key differences between the Primary and foreign keys in a table. This is also one of the most common SQL Interview questions for 1 to 2 years of experienced programmers.
Difference between Primary and Foreign key in a table
As I said, if two tables are in relation to each other than the primary key of one table becomes the foreign key of another table. Let's see some more differences between Primary and Foreign keys in SQL:1. One of the major differences between these two keys is that the primary key enforces clustered index, which is the actual physical order of how data is stored in the disc. On the other hand, the 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 a foreign key. You can have more than one row in a table with the 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 differences 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 a department will be prevented by database management systems like Oracle, MySQL, or SQL Server.
You can further set up cascade actions with foreign keys like when a department is deleted, update the dept_id of all employees in the Employee table referring to this department. You can set up such an arrangement using the ON CASCADE clause.
Similarly, you cannot insert the data in the secondary table if a row with the primary key doesn't exist in the 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 keys in a table is that the 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.
Here is a nice table of some key differences between the Primary and Foreign keys in the table:
Similarly, you cannot insert the data in the secondary table if a row with the primary key doesn't exist in the 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 keys in a table is that the 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.
Here is a nice table of some key differences between the Primary and Foreign keys in the table:
That's all about the difference between Primary and Foreign keys in SQL. It's one of the most important concepts in a relational database and a good understanding of this is required to manage or retrieve data from a commercial database like Oracle or Microsoft SQL Server.
Some other SQL Interview Questions from this blog:
- How to find the second highest salary in MySQL? (solution)
- What is the difference between UNION and UNION ALL in SQL? (answer)
- The difference between TRUNCATE and DELETE in SQL? (answer)
- The difference between self and equijoin in SQL? (answer)
- What is the difference between View and Materialized View in the Oracle database? (answer)
- How to find the second highest salary in Oracle using ROW_NUMBER? (answer)
- The difference between WHERE and HAVING clause in SQL? (answer)
- The difference between LEFT and RIGHT OUTER JOIN in SQL? (answer)
- How to find duplicate records in a table? (query)
No comments:
Post a Comment
Feel free to comment, ask questions if you have any doubt.