limited time offer

What are the differences between a primary key and a foreign key?

The database table uses a primary key to identify each row uniquely. It is necessary to declare the primary key on those tables that require to create a relationship among them. One or more fields of a table can be declared as the primary key.

When the primary key of any table is used in another table as the primary key or another field for making a database relation, then it is called a foreign key.

The differences between these two keys are mentioned below.

  • The primary key uniquely identifies a record, whereas foreign key refers to the primary key of another table.
  • The primary key can never accept a NULL value but foreign key accepts a NULL value.
  • When a record is inserted in a table that contains the primary key then it is not necessary to insert the value on the table that contains this primary key field as the foreign key.
  • When a record is deleted from the table that contains the primary key then the corresponding record must be deleted from the table containing the foreign key for data consistency. But any record can be deleted from the table that contains a foreign key without deleting a related record of another table.

Example

CREATE TABLE manufacturers(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));

CREATE TABLE items(id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(50), 
type VARCHAR(50), 
brand VARCHAR(50), 
manufacturer_id INT, 
FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(id));