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 Basis | Primary Key | Foreign Key |
|---|---|---|
| Basic | Primary 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. |
| Count | Each table has only one primary key. | There can be more than one foreign keys in a table. |
| NULL | The value of the primary key can never be NULL. | A foreign key can accept a NULL value. |
| Duplication | Primary key cannot contain duplicate values in a relation. It has unique attributes. | Foreign key can contain duplicate values. |
| Insertion | A 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. |
| Deletion | If 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 Index | A primary key is clustered indexed by default. | A foreign key is made clustered indexed manually. |
| Temporary Table | We can define primary key constraint on the temporary tables. | We cannot define foreign key constraint on the temporary table. |
| Number allowed per table | One | One 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”.
1 2 3 4 5 | 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:
1 2 3 4 5 6 | 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:
1 2 | ALTER TABLE Products ADD PRIMARY KEY (product_quantity); |
If we want to remove the primary key constraint, the query is:
1 2 | 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.
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 6 7 | 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:
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!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started