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:

Emp_Id	LastName	FirstName	City
101	Sharma	Rohan	Mumbai
102	Verma	Anjali	Delhi
103	Mehta	Kunal	Bangalore

Salaries Table:

Sal_Id	Amount	Emp_Id
201	50000	102
202	60000	103
203	55000	101

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

Feature	Primary Key	Foreign Key
Can be NULL	No	Yes
Uniqueness	Always unique	Can be duplicated
Identifies records	Uniquely identifies a record	References primary key in another table
Quantity per table	Only one primary key	Multiple foreign keys allowed
Indexing	Creates a clustered index	Does not automatically create an index

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