SQL FOREIGN KEY: Understanding and Implementing in Relational Databases

SQL FOREIGN KEY

A foreign key is a crucial element in relational databases, allowing the establishment of a link between two tables. In simple terms, a foreign key in one table points to the primary key of another table, ensuring referential integrity.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

Let’s take an example to understand it better:

We have two tables: Employees and Salaries.

Employees Table:

Salaries Table:

The Emp_Id column in the “Employees” table is cited in this instance by the Emp_Id column in the “Salaries” table.

The primary key in the “Employees” table is the Emp_Id column.

The “Salaries” table’s Emp_Id column is a Foreign Key.

Why Use Foreign Keys?

A foreign key constraint prevents actions that would destroy links between tables. It also ensures that invalid data cannot be inserted into the foreign key column.

SQL FOREIGN KEY Constraint on CREATE TABLE

To define a foreign key while creating the “Salaries” table, use the following SQL syntax:

MySQL:

CREATE TABLE Salaries
(  
Sal_Id INT NOT NULL,  
Amount INT NOT NULL,  
Emp_Id INT,  
PRIMARY KEY (Sal_Id),  
FOREIGN KEY (Emp_Id) REFERENCES Employees(Emp_Id)  
);

SQL Server / Oracle / MS Access:

CREATE TABLE Salaries
(  
Sal_Id INT NOT NULL PRIMARY KEY,  
Amount INT NOT NULL,  
Emp_Id INT FOREIGN KEY REFERENCES Employees(Emp_Id)  
);

SQL FOREIGN KEY Constraint Using ALTER TABLE

If the “Salaries” table is already created and you need to add a foreign key constraint later, use this syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Salaries  
ADD CONSTRAINT fk_EmpSalaries  
FOREIGN KEY (Emp_Id)  
REFERENCES Employees (Emp_Id);

Dropping a FOREIGN KEY Constraint

To remove a foreign key constraint, use the following syntax:

MySQL:

ALTER TABLE Salaries  
DROP FOREIGN KEY fk_EmpSalaries;

SQL Server / Oracle / MS Access:

ALTER TABLE Salaries  
DROP CONSTRAINT fk_EmpSalaries;

Differences Between Primary Key and Foreign Key in SQL

Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE

Foreign keys are essential for ensuring data consistency and establishing strong relationships between tables in a relational database. By implementing FOREIGN KEY constraints properly, you can maintain referential integrity and avoid orphaned records in your database.

For more SQL tutorials, stay updated with UpdateGadh!

sql foreign key example
sql foreign key w3schools
primary key in sql
primary key and foreign key in sql with examples
foreign key query in sql
foreign key syntax
foreign key in mysql
sql foreign key references two tables