» SQL,Oracle,MySQL: Difference between Primary key and Foreign Key in table

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.
Example:
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;

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.