Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL FOREIGN KEY Constraint
The FOREIGN KEY constraint is used to ensure referential integrity between two tables. It establishes a relationship between columns in two tables, where one table’s column (or columns) refers to the primary key in another table. This helps maintain consistent and valid data across related tables.
Syntax
CREATE TABLE table_name (
column_name datatype,
FOREIGN KEY (column_name) REFERENCES other_table (primary_key_column),
…
);
— For composite foreign key
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
FOREIGN KEY (column1, column2) REFERENCES other_table (primary_key_column1, primary_key_column2),
…
);
- column_name: The column in the current table that references the primary key in the other table.
- other_table: The table containing the primary key that is referenced.
- primary_key_column: The column in the other table that is the primary key.
Example:
1 Creating Tables with Foreign Key Relationship
Suppose you have two tables, Departments and Employees. Each Employee belongs to a Department, so DepartmentID in Employees references DepartmentID in Departments:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FullName VARCHAR(100),
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments (DepartmentID)
);
In this example, DepartmentID in Employees must match an existing DepartmentID in the Departments table.
2 Inserting Data with Foreign Key Constraint
When inserting data into the Employees table, ensure that the DepartmentID exists in the Departments table:
— Insert into Departments
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, ‘HR’);
— Insert into Employees
INSERT INTO Employees (EmployeeID, FullName, DepartmentID)
VALUES (101, ‘Rajesh Sharma’, 1);
In this case, DepartmentID 1 in Employees must already exist in Departments.
3 Updating Data with Foreign Key Constraint
If you need to update a department’s ID, you must ensure that all references in Employees are updated accordingly:
— Update DepartmentID in Departments
UPDATE Departments
SET DepartmentID = 2
WHERE DepartmentID = 1;
— Update corresponding DepartmentID in Employees
UPDATE Employees
SET DepartmentID = 2
WHERE DepartmentID = 1;
4 Deleting Data with Foreign Key Constraint
If you try to delete a department that has associated employees, SQL will prevent the deletion if ON DELETE RESTRICT is set. You might need to remove dependent records first:
— Attempt to delete a department
DELETE FROM Departments
WHERE DepartmentID = 1;
— If Employees table has employees with DepartmentID 1, this will fail unless dependent records are removed.
To handle such cases, you can use ON DELETE CASCADE to automatically delete dependent records:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FullName VARCHAR(100),
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments (DepartmentID) ON DELETE CASCADE
);
Tips to Remember
- Ensure Data Integrity: Foreign keys help maintain valid relationships between tables and enforce consistency.
- Handle Deletes Carefully: Use ON DELETE CASCADE or ON DELETE SET NULL to manage related records appropriately.
- Consider Indexing: Foreign key columns should be indexed to improve performance on joins.
Frequently Asked Questions
Q1: Can a foreign key column have NULL values?
A1: Yes, foreign key columns can have NULL values, which means that the row does not need to reference a row in the related table.
Q2: Can a foreign key reference a column that is not a primary key?
A2: No, a foreign key must reference a primary key or a unique key column in the related table.
Q3: What happens if I try to insert a value into a foreign key column that doesn’t exist in the referenced table?
A3: SQL will return an error, preventing the insertion of a row with an invalid foreign key value.
Q4: How do I drop a foreign key constraint?
A4: Use the ALTER TABLE command to drop a foreign key constraint:
ALTER TABLE Employees
DROP FOREIGN KEY constraint_name;
Q5: Can I have multiple foreign keys in a single table?
A5: Yes, a table can have multiple foreign keys, each referencing different columns in the same or different tables.
