Lets discuss Foreign key in SQL with examples. A Foreign Key provides a way of establishing a link between two tables. In simple words, the Foreign key confirms values in one table must be present in another table. The foreign key rule generally controls action that destroys links between tables. It also prevents invalid data to enter into foreign key columns.
Rules for FOREIGN KEY
- SQL Foreign key authorized NULL.
- The referenced table called the Parent Table
- The table with the Foreign Key in SQL is called Child Table.
- This parent-child relationship implements the rule known as “Referential Integrity.”
Lets discuss Foreign key in SQL with examples
Look at the following two tables for a better understanding of FOREIGN KEY:
Catch that the “UserID” column in the “Orders” table points to the “UserID” column in the “Persons” table.
The “UserID” column in the “Persons” table is the PRIMARY KEY in the “Persons” table.
The “UserID” column in the “Orders” table is a FOREIGN KEY in the “Orders” table.
SQL FOREIGN KEY on CREATE TABLE
The following SQL query creates a FOREIGN KEY on the “UserID” column when the “Orders” table is created:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
UserID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (UserID) REFERENCES Persons(UserID)
);
SQL FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the “UserID” column when the “Orders” table is already created, use the following SQL Query:
ALTER TABLE Orders
ADD FOREIGN KEY (UserID) REFERENCES Persons(UserID);
DROP a FOREIGN KEY
Use the following SQL Query to drop a FOREIGN KEY.
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;