Difference between Primary and Foreign key in table - SQL
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 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 enforces some constraint e.g. UNIQUE and NOT NULL, which means if you try to enter another record with the primary key already existing in the database than 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 e.g. Employee and Department, those cases, the primary key of one table becomes the foreign key of another table. In this article, you will learn some key difference between the Primary and foreign key in a table. This is also one of the most common SQL Interview questions for 1 to 2 years experience programmers.
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 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 foreign key. You can have more than one rows in a table with 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 difference 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 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 the secondary table if a row with the primary key doesn't exist in 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 key in a table is that 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 key in the table:
That's all about the difference between Primary and Foreign key 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 11g or Microsoft SQL SERVER 2014. It's important for a beginner to understand these key SQL concepts, if you are just starting SQL, then you should check out the Head First SQL book, one of the best books to learn SQL.
Some other SQL Interview Questions from this blog:
Difference between Primary and Foreign key in table
As I said, if two table is 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 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 foreign key. You can have more than one rows in a table with 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 difference 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 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 the secondary table if a row with the primary key doesn't exist in 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 key in a table is that 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 key in the table:
That's all about the difference between Primary and Foreign key 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 11g or Microsoft SQL SERVER 2014. It's important for a beginner to understand these key SQL concepts, if you are just starting SQL, then you should check out the Head First SQL book, one of the best books to learn SQL.
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 Oracle database? (answer)
- How to find 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