Main difference between Primary key and Foreign key in a table is that, it’s
the same column which behaves as primary key in parent table and as foreign key
in child table. For example in Customer and Order
relationship, customer_id is primary key in Customer table but
foreign key in Order table. By the way what is foreign key in a
table and difference between Primary and Foreign key are some of the popular
SQL interview questions, much like truncate
vs delete in SQL or difference
between correlated and noncorrelated subquery. We have been learning key SQL
concepts along with these frequently asked SQL questions and in this SQL
tutorial we will discuss about what is foreign key in SQL and purpose of foreign
key in any table. By the way this is the third article related to primary key
in SQL, other being difference
between primary and unique key and How
to find second highest salary in SQL. If you are preparing for any technical
job interview where you expect some SQL questions, check out these questions,
they are worth preparing.
What is Foreign key in a table
Foreign key is a column in one table which is primary key on another
table. Foreign key and Primary key is used to define relationship between two
tables in relational database. For example in Employee and Department relationship,
we have two tables Department(dept_id, dept_name) and Employee (emp_id,
emp_name, dept_id). dept_id is primary
key in Department table and foreign key in Employee table.
Though it’s not require that name of foreign key must be same with primary key,
we have kept it same as per standard SQL best practices. Foreign key in a table
enforce Referential
Integrity constraint, which can be used to implement business rules e.g. referential integrity can stop you from creating an Employee with a non
existent department. This kind of check maintains integrity of data in a
relationship. As discussed in our post What
is referential integrity in MySQL database, we have seen that it's
implemented as foreign key constraint and can allow CASCADE
UPDATE and DELETE. These referential action delete
or update matching column in child table ( foreign key table) when
corresponding row from parent table (primary key table ) is deleted or updated
to maintain integrity of data.Difference between Primary key and Foreign key in SQL
Here are some important difference between primary and foreign keys in a
table which is worth remembering both on SQL interview point of view and
knowledge point of view.
1) Name of foreign key can be
different than the name of primary key
it represent in other table. For example in our Employee and Department
relationship, Primary key in Department table is dept_id and we have used same name in Employee table to
create foreign key. It could have been different e.g. departmentId
or departmentID t etc.
2) Another difference between primary and foreign key is that unlike
primary key, foreign key can be null e.g. in our example you can have an
Employee record for which dept_id can be null, this shows that no
corresponding record in Department table.
3) One more difference between
primary key and foreign key is that foreign key can be duplicate
opposite to primary key which is always unique.
4) By using foreign key constraints, we can introduce referential
integrity in multiple table relationship in SQL. Referential integrity guarantees data integrity, see benefits
of Referential Integrity in SQL to know more.
5) Foreign key mostly work as link between two table when we join tables using
INNER JOIN and OUTER JOIN. For
example when we INNER JOIN both Employee with Department
table, we can use dept_id as joining column. See How
to join three tables in SQL for more details.
6) Table on which a column is declared as primary key is known
as parent table in relationship and foreign key table is
known as child table in relationship. For example in Employee and Department
relationship, Department is parent table because dept_id is primary
key there and Employee is child table because dept_id is foreign
key in this table.
Primary key and Foreign key Example in SQL
One of the best example to understand Primary key and Foreign key in a
table is Employee and Department
relationship or Customer and Order
relationship. You can create Order and Customer table in
MySQL as following to create primary and foreign keys :
CREATE TABLE
Customer (cust_id INT NOT NULL,
cust_name VARCHAR(256),
PRIMARY KEY (cust_id)) ENGINE=INNODB;
CREATE TABLE ORDER (order_id INT NOT NULL,
amount INT NOT NULL,
cust_id INT,
FOREIGN KEY (cust_id) REFERENCES Customer(cust_id)
ON DELETE CASCADE) ENGINE=INNODB;
cust_name VARCHAR(256),
PRIMARY KEY (cust_id)) ENGINE=INNODB;
CREATE TABLE ORDER (order_id INT NOT NULL,
amount INT NOT NULL,
cust_id INT,
FOREIGN KEY (cust_id) REFERENCES Customer(cust_id)
ON DELETE CASCADE) ENGINE=INNODB;
Now cust_id is primary key in Customer table and
foreign key in Order table. If we try to insert an Order for which cust_id is
something which is invalid in Customer table, MySQL database will
reject such INSERT or UPDATE. This is one of the benefit of using Referential
Integrity. It also allow to CASCADE UPDATE and DELETE operation
which first delete or update a row in parent table e.g. Customer and then
delete or update all matching rows in child table e.g. Order table.
That's all on what is foreign key in a table and difference between
primary and foreign key in SQL. I suggest to create some table by yourself and
try to test foreign key constraint by violating it and see how database e.g.
Oracle, MySQL or SQL Server behaves. To
understand more try ON DELETE CASCADE and ON
DELETE UPDATE to see how database maintains foreign key constraint. You can also see
my post on Referential
Integrity example on MySQL database
Other SQL Interview Questions for practice
No comments:
Post a Comment