SQL: - Primary Key and Foreign key

“A very little key will open a very heavy door.”

- Charles Dickens

Keys are important in the database model. A structured query language will be very much unstructured if there are no keys. The keys in Structured query language play a pivotal role in maintaining data integrity, consistency and improving database performance. This blog helps you to understand about the basics of Primary key and foreign key.

Primary Key

What are Primary Keys?

* A primary key is a column or set of columns that uniquely identifies each row in that table.

* A primary key must contain unique values that means there should not be any null values or duplicate values.

* A table can have only one primary key; and in this table; this primary key can contain single or multiply columns or fields.

Example: -

Student Table

Student ID	First Name	Last Name	Student City
101	Chase	Cooper	Austin
102	Charles	Miller	Dallas
103	Myles	Cooper	Dallas
104	Luke	Wellington	Houston

In the above example, Primary Key is Student ID as it is the only column which did not have any duplicate values. As you can see, other columns like Last name and the city have duplicate values. But the Student ID will always remain unique.

Syntax of Primary Key on CREATE TABLE in PostgreSQL: -

For single column:

CREATE TABLE table_name (

Column1 datatype PRIMARY KEY,

Column2 datatype,

Column3 datatype,

……other columns

);

The PRIMARY KEY constraint is placed after the column definition, marking that column as the primary key for the table. We can also have a composite primary key by listing multiple columns under the PRIMARY KEY constraint. For example:

For multiple columns:

CREATE TABLE table_name (

Column1 datatype,

Column2 datatype,

Column3 datatype,

PRIMARY KEY (column1, column2)

);

This creates a composite primary key using both column1 and column2.

If you already have a table and want to set a column as Primary key, we can use Alter command: -

Alter Command: -

ALTER TABLE table_name

ADD CONSTRAINT pk_constraint_name

PRIMARY KEY (column_name);

Drop the primary key: -

ALTER TABLE table_name

DROP CONSTRAINT pk_constraint_name;

Foreign Key

What are foreign keys?

* The foreign key is a column or set of columns in a table, which refers to the PRIMARY KEY in another table and is to link two tables together.

* The table with the primary key is the parent table and the table with the foreign key is the child table.

* The foreign key constraint is to prevent actions that would destroy links between tables. Which means foreign key does not allow values other than parent table.

Hence foreign keys are not only linking tables, but they also ensure accuracy by rejecting insertions when they do not match what is in the referenced table. It is good practice to note that all our foreign keys are referencing to the primary keys of other tables.

Primary and foreign keys help to prevent data inconsistencies and improve the overall quality of the data in a database.

Example: -

Student Table

Student ID	First Name	Last Name	Student City
101	Chase	Cooper	Austin
102	Charles	Miller	Dallas
103	Myles	Cooper	Dallas
104	Luke	Wellington	Houston

City Table

City ID	City
1	Dallas
2	Houston
3	San Antonio
4	Austin

· The City Table consists of the Name of the City and the respective id.

· Here the foreign key is City ID in City table and primary key is Student ID in the Student Table.

· City ID will be the primary key in the city table and Student Id column will be the primary key in the student table

· Student City column in the student table is the foreign key whereas to link these two tables, we use City Id as foreign key. Having City id in the student table can make the student table easier and can lower the

executing time of the table.

Syntax of Foreign key

CREATE TABLE Orders (

OrderID int NOT NULL PRIMARY KEY,

OrderNumber int NOT NULL,

PersonID int FOREIGN KEY REFERENCES Persons (PersonID)

);

Foreign key has a command called References. The purpose of reference is it gives reference to the primary key of another table.

Alter Command: -

ALTER TABLE table_name

ADD CONSTRAINT fk_constraint_name

FOREIGN KEY (column_name)

REFERENCES other_table (other_column);

Drop the foreign key: -

ALTER TABLE table_name

DROP CONSTRAINT fk_constraint_name;

What happens if we do not use Primary and foreign keys?

Duplicate data: - We may end up storing the same data in multiple places, leading to confusion and inconsistencies.

Inconsistent data: Without foreign keys, it may be hard to keep data consistent between tables.

Slow Performance: Querying a table without primary and foreign keys can be slow, especially when working with enormous amounts of data.

Conclusion: -

Apart from Primary key and Foreign Key, there are other keys like Unique key, Composite key which serves various purposes like enforcing uniqueness, defining conditions, or creating compound identifiers within a table. In conclusion, Primary keys, and foreign keys play a significant role in improving data accuracy and consistency in SQL database and thereby helps in query optimization.

Thank you for reading.

Happy reading.

Aiswarya R