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.
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
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
CREATE TABLE Employeedetail ( EmpID int NOT NULL PRIMARY KEY, Name VARCHAR(255) , EmpAge int , );
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:-
|PRIMARY KEY||FOREIGN KEY|
|In a relational database, a primary key constraint serves as a unique identifier for each table row.||A foreign key constraint creates a link between two tables in order to uniquely identify a row in the same or another table.|
|NULL values are not stored in the main key column.||The foreign key can have multiple NULL values.|
|In a relational database, each table can only have one primary key.||In a table, we can declare numerous foreign keys.|
|The table's primary key value cannot be deleted. If you want to get rid of it, make sure the referring foreign key doesn't have its value in it.||Even though the child table's foreign key value refers to the parent table's main key, we can delete it.|
|Because a primary key is a non-null constraint, no two rows can have the same value for that attribute.||Duplicate values can be stored in foreign key fields.|
|We have no restrictions on inserting data into the primary key column.||When entering data into the foreign key table, we must guarantee that the value is present in a primary key column.|
|On temporary tables, we can define the primary key constraint implicitly.||Foreign key constraints cannot be enforced on temporary tables.|
|The main key is a clustered index, and the data in the DBMS table is physically ordered in the clustered index's sequence.||A foreign key cannot generate an index, aclustered or non-clustered, on its own.|