FOREIGN KEY in SQL Server Database Table
A FOREIGN KEY is a key used to link two tables together.Sometimes this is also known as reference key
First table:
| PersonID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | MATHUR | SUHAN | 25 |
| 2 | DAKSH | MAYUR | 26 |
| 3 | ARJUN | SINHA | 22 |
Second table:
| OrderID | OrderNo | PersonID |
|---|---|---|
| 1 | 789564231 | 2 |
| 2 | 658974231 | 2 |
| 3 | 954687216 | 3 |
| 4 | 845632174 | 1 |
Notice that the "PersonID" column in the "Second" table points to the "PersonID" column in the "First" table
The "PersonID" column in the "First" table is the PRIMARY KEY in the "First" table.
The "PersonID" column in the "Second" table is a FOREIGN KEY in the "Second" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
To create a foreign key on the "PersonID" column when the "Second" table is created:
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
To create a FOREIGN KEY constraint on the "PersonID" column when the "Second" table is already created, use the following SQL
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
SQL Server Connection String Formats
posted on 2019-08-27 05:53:53 - SQL Server Tutorials