WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL FOREIGN KEY Constraint


SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table focuses to a PRIMARY KEY in another table.

Let's show the outside key with a precedent. Take a gander at the accompanying two tables:

The "Persons" table:

User_Id UserName UserName Password Email
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

The "Orders" table:

O_Id OrderNo User_Id
1 501 3
2 502 3
3 503 2
4 504 1

The "User_Id" section in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "User_Id" section in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY imperative is utilized to anticipate activities that would decimate interfaces between tables.

The FOREIGN KEY requirement additionally keeps invalid information from being embedded into the remote key section, since it must be one of the qualities contained in the table it focuses to.


SQL FOREIGN KEY Constraint on CREATE TABLE

The following SQL makes a FOREIGN KEY on the "User_Id" segment when the "Orders" table is created:

MySQL:

Make TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
User_Id int,
Essential KEY (O_Id),
Outside KEY (User_Id) REFERENCES Persons(User_Id)
)

SQL Server/Oracle/MS Access:

Make TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
User_Id int FOREIGN KEY REFERENCES Persons(User_Id)
)

To permit naming of a FOREIGN KEY limitation, and for characterizing a FOREIGN KEY requirement on various sections, utilize the accompanying SQL syntax:

MySQL/SQL Server/Oracle/MS Access:

Make TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
User_Id int,
Essential KEY (O_Id),
Requirement fk_PerOrders FOREIGN KEY (User_Id)
REFERENCES Persons(User_Id)
)

SQL FOREIGN KEY Constraint on ALTER TABLE

To make a FOREIGN KEY requirement on the "User_Id" section when the "Orders" table is as of now made, utilize the accompanying SQL:

MySQL/SQL Server/Oracle/MS Access:

Change TABLE Orders
Include FOREIGN KEY (User_Id)
REFERENCES Persons(User_Id)

To permit naming of a FOREIGN KEY requirement, and for characterizing a FOREIGN KEY limitation on various segments, utilize the accompanying SQL syntax:

MySQL/SQL Server/Oracle/MS Access:

Modify TABLE Orders
Include CONSTRAINT fk_PerOrders
Remote KEY (User_Id)
REFERENCES Persons(User_Id)

To DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY limitation, utilize the accompanying SQL:

MySQL:

Change TABLE Orders
DROP FOREIGN KEY fk_PerOrders

SQL Server/Oracle/MS Access:

Change TABLE Orders
DROP CONSTRAINT fk_PerOrders