SQL FOREIGN KEY Constraint
This tutorial will explain about the FOREIGN KEY in SQL and how to use them with the examples.
The FOREIGN KEY field (constraint) references a PRIMARY KEY field in another table.
Child table contains the foreign key and the parent table contains the primary key.
Here is the parent table creation statement
CREATE TABLE Users (
UserID int NOT NULL,
Name nvarchar(50),
PRIMARY KEY (UserID)
);
Lets now create table with foreign key to use UserID column as reference value
CREATE TABLE Tasks (
TaskID int NOT NULL PRIMARY KEY,
Title nvarchar(50),
UserID int,
CONSTRAINT FK_Tasks_Users FOREIGN KEY(UserID) REFERENCES Users(UserID)
);
Lets see these tables with data
Users Table
UserID Name
1 User1
2 User2
3 User3
Tasks Table
TaskID Title UserID
1 Task1 1
2 Task2 1
3 Task3 2
4 Task4 1
UserID column from Tasks table points to row from Users table . As an example Task2 UserID value is 1 which is User1 in Users table.
* The following statement execution will fail as the FOREIGN KEY will prevent inserting UserID which does not exist in Users table (There is UserID as 4).
INSERT INTO Tasks(TaskID, Title, UserID) VALUES (5, "Task5", 4);
DROP FOREIGN KEY
The following statement will remove the foreign from the table
ALTER TABLE Tasks DROP CONSTRAINT FK_Tasks_Users;
ADD FOREIGN KEY
The following statement will remove the foreign from the table
ALTER TABLE Tasks
ADD CONSTRAINT FK_Tasks_Users FOREIGN KEY(UserID) REFERENCES Users(UserID)