Primary Key vs Foreign Key

Introduction

Primary keys and foreign keys are the most common types of keys used in databases. A primary key uniquely identifies a tuple in a table whereas a foreign establishes a relationship between two tables. In this article, we will do the comparison between primary key and foreign in relational databases. We will also see how primary key and foreign key are implemented in PostgreSQL.

Primary Key vs Foreign Key Summarized

Comparison BasisPrimary KeyForeign Key
BasicPrimary key is a chosen candidate key that uniquely identifies a record/row in a table.Foreign key links two tables together. A foreign key in one table refers to the primary key of another table.
CountEach table has only one primary key.There can be more than one foreign keys in a table.
NULLThe value of the primary key can never be NULL.A foreign key can accept a NULL value.
DuplicationPrimary key cannot contain duplicate values in a relation. It has unique attributes.Foreign key can contain duplicate values.
InsertionA value can be inserted to a primary key even if the foreign key does not contain that value.A value that is not present in a primary key, cannot be added in the referencing foreign key.
DeletionIf a primary key value has to be deleted, make sure that value is not contained by the referencing foreign key.A value from foreign key can be deleted without bothering that the referenced primary key has that value or not.
Clustered IndexA primary key is clustered indexed by default.A foreign key is made clustered indexed manually.
Temporary TableWe can define primary key constraint on the temporary tables.We cannot define foreign key constraint on the temporary table.
Number allowed per tableOneOne or more

Implementations

Implementations of the primary key and foreign key are shown below using PostgreSQL.

Primary Key in PostgreSQL

The primary key is added when we are creating the table. In the table “Products”, the primary key is “product_no”.

CREATE TABLE Products (
   product_no INTEGER PRIMARY KEY,
   product_name CHARACTER(35),
   product_quantity NUMERIC
);

If we want two attributes to be create a composite primary key, the query is written as:

CREATE TABLE Products (
   product_no INTEGER,
   product_name CHARACTER(35),
   product_quantity NUMERIC
   PRIMARY KEY (product_no, product_name)
);

If you already have a table and want to add a primary key constraint, the query is:

ALTER TABLE Products
ADD PRIMARY KEY (product_quantity);

If we want to remove the primary key constraint, the query is:

ALTER TABLE Products
DROP CONSTRAINT Products_pkey;

Foreign Key in PostgreSQL

For demonstration purposes We will create a table “Customers”. This table will contain the customer_id as the primary key.

CREATE TABLE Customers (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    Address VARCHAR (355)
);

We will create another table that will contain the information about the order the customer has placed.

CREATE TABLE Orders (
    product_id INTEGER NOT NULL,
    order_id INTEGER,
    product_quantity INTEGER,
    net_price INT,
    PRIMARY KEY (product_id, order_id)
);

In the above query, we have defined a primary key of two columns; product_id and order_id.

We will create a foreign key “order_id” in the table “Orders” by referencing order_id to id in the Customers table.

CREATE TABLE Orders (
    product_id INTEGER NOT NULL,
    order_id INTEGER REFERENCES Customers(id),
    product_quantity INTEGER,
    net_price INT,
    PRIMARY KEY (product_id, order_id)
);

The relation between the two tables is shown below:

Image from Gyazo

Image from Gyazo

The order_id 1 in Orders is referring to the id 1 in the Customers table. In the customers table, the id customer 1 has placed two orders which are shown in red boxes in the above figures. Similarly, the id 2 customer has placed one order which is shown in purple box, same is the case with the id 3 customer, whose order is shown in yellow box.

Conclusion

Primary key and foreign key play a crucial role in DBMS as they establish relation between tables in a database schema. Primary key is unique for each table whereas foreign keys can be multiple. The structure of both keys is same but there function differs as primary is used to identify a tuple in a relation uniquely and foreign key is used to link two tables together.

Pilot the ObjectRocket platform free for 30 Days

It's easy to get started. Imagine the time you'll save by not worrying about database management. Let's do this!

PILOT FREE FOR 30 DAYS

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.