The FOREIGN KEY is used to define a relationship between two tables and a FOREIGN KEY in one table points to the PRIMARY KEY in another table.
Syntax of FOREIGN KEY Constraint on one column with CREATE TABLE statement:
MySQL:
CREATE TABLE Orders
(
Order_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (Order_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
) |
CREATE TABLE Orders
(
Order_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (Order_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
Order_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
) |
CREATE TABLE Orders
(
Order_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
Syntax of FOREIGN KEY Constraint on one column with ALTER TABLE statement:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders DROP FOREIGN KEY fk_POrders |
ALTER TABLE Orders DROP FOREIGN KEY fk_POrders
Syntax of FOREIGN KEY Constraint on one column with CREATE TABLE statement:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
Order_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (Order_Id),
CONSTRAINT fk_POrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
) |
CREATE TABLE Orders
(
Order_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (Order_Id),
CONSTRAINT fk_POrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
Syntax of FOREIGN KEY Constraint on one column with ALTER TABLE statement:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD CONSTRAINT fk_POrders
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) |
ALTER TABLE Orders ADD CONSTRAINT fk_POrders
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
DropFOREIGNKEY:
Use following syntax to delete the foreign key.
MySQL:
ALTER TABLE Orders DROP FOREIGN KEY fk_POrders |
ALTER TABLE Orders DROP FOREIGN KEY fk_POrders
SQL Server / Oracle / MS Access:
ALTER TABLE Orders DROP CONSTRAINT fk_POrders |
ALTER TABLE Orders DROP CONSTRAINT fk_POrders
Difference between primary key and foreign key:
| Primary key |
Foreign key |
- Primary key can’t contain null values.
- Primary key can’t be duplicate.
- A table can have only one primary key.
- Primary key automatically adds a clustered index.
|
- Foreign key can contain null values.
- Foreign key can be duplicate.
- A table can have more than one foreign key.
- Foreign key not add any index automatically.
|
Next Topic: SQL NOT NULL Constraint with example. Previous Topic: SQL PRIMARY KEY Constraint with example.
Please follow and like us:
