FOREIGN KEY - One table points to another PRIMARY KEY.

Look at the following two tables:

There is a table "Persons":

The column "P_Id" in table "Orders" is a FOREIGN KEY.

SQL FOREIGN KEY constraints in CREATE TABLE

MySQL:

CREATE TABLE Orders (
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

MySQL / SQL Server / Oracle / MS Access:

1
2
3
4
5
6
7
8

CREATE TABLE Orders(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

SQL FOREIGN KEY constraints in ALTER TABLE

MySQL / SQL Server / Oracle / MS Access:

1
2
3

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

MySQL / SQL Server / Oracle / MS Access:

1
2
3
4

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

Removal of FOREIGN KEY

MySQL:

1
2

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access:

1
2

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders