SQL PRIMARY KEY Vs FOREIGN KEY Constraints

The essential factors for building a relationship between two tables in MySQL are called keys.

They come in handy when it comes to maintaining a relational database structure. The PRIMARY KEY is used to identify each entry in the table.

The FOREIGN KEY is used to connect two tables.

SQL PRIMARY KEY Constraint

A PRIMARY KEY is a unique key that identifies each record in a table or relation.

Every row of a table in a database requires a unique identifier, and the primary key plays a critical role in uniquely identifying rows in the table.

Duplicate values cannot be stored in the primary key column. Because we can't provide more than one primary key in any relationship, it's also known as a minimal super key.

Example: For instance, we have a Employeedetail database with attributes like EmpID, Name , and EmpAge .

Because each client has a unique identification number, the EmpID field can never include NULL values.

This feature aids in the unique identification of each database record. As a result, the EmpID attribute can be used as a primary key.

let's work with query

Mysql

CREATE TABLE Employeedetail
(
EmpID int NOT NULL PRIMARY KEY,
Name VARCHAR(255) ,
EmpAge int ,
);

Sql-server,Ms access

CREATE TABLE Books
(
EmpID int NOT NULL,
Name VARCHAR(255) ,
EmpAge int ,
PRIMARY KEY(EmpID)
);

INSERT INTO Employedetail(EmpID,Name,EmpAge)
Values(1,'Narmatha',23);
INSERT INTO Employedetail(EmpID,Name,EmpAge)
Values(2,'Hari',25);
INSERT INTO Employedetail(EmpID,Name,EmpAge)
Values(3,'Kavya',27);

In this example primary key used to add distinct value to query. so if we execute following query let's see how primary works,

INSERT INTO Employedetail(EmpID,Name,EmpAge)
Values(2,'Hari',25);

The above statement get an error.because we decalred EmpID is not null not only that EmpId 2 is already exist in table.

SQL FOREIGN KEY Constraint

To maintain referential integrity, a FOREIGN KEY is a set of one or more columns in a database that uniquely identifies another database record in another table.

It's also known as the reference key, and it's responsible for establishing a link between two different tables in a database.

A foreign key column in one table must always match the main key column in another. It means that one table's foreign key column relates to another table's main key column. In relational database normalisation, a foreign key is useful, especially when we need to access records from other tables.

A foreign key establishes a parent-child relationship between tables, with the parent table storing the original column values and the child table referencing them. Only when the foreign key restriction is found on the child table can we achieve this relationship.

Example: We have a contact database, for example, with attributes like ID, Customer Id, Customer Info , and Type. The Customer Id column can be made a FOREIGN KEY here.

We may define the foreign key in the contact table as follows to erase the referential data that eliminates records from both tables:

FOREIGN KEY (ProductID) REFERENCES Product(ID)     
ON DELETE CASCADE     
ON UPDATE CASCADE

When we delete any record from the customer table, the related rows will also delete in the contact table, and both tables update automatically.

SQL DIFFERENCE BETWEEN PRIMARY KEY And FOREIGN KEY

Here we discussed 8 main distinguish in primary vs foreign key in below table:-