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
