SQL FOREIGN KEY Constraint

The FOREIGN key in one table points to the PRIMARY key in the other table.

Let's use an example to explain the foreign key. Take a look at the following two tables:

"Persons" table:

"Orders" table:

Notice that theid_pcolumn in Orders points to theid_pcolumn in the Persons table.

The "id_p" column in the "Persons" table is the PRIMARY KEY in the Persons table.

The "id_p" column in the Orders table is the FOREIGN KEY in the Orders table.

The primary key "id_p" column of the Persions table is the foreign key in the Orders table.

The FOREIGN KEY constraint is used to prevent actions that disrupt the connection between tables.

The FOREIGN key constraint can also prevent illegal data from being inserted into the foreign key column, because it must be one of the values in the table it points to. SQL FOREIGN KEY Constraint on CREATE TABLE

The following SQL creates FOREIGN Key:mysql for the "id_p" column when the Orders table is created:

CREATE TABLE Orders (id_o int not null,orderno int not null,id_p int,primary KEY (id_o), FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)--Persions表的主键“Id_P”列是“Orders” 表中的FOREIGN KEY。 )

SQL Server/oracle/ms Access:

CREATE TABLE Orders (id_o int NOT null PRIMARY Key,orderno int. NOT NULL Id_P int FOREIGN KEY REFERENCES Persons(Id_P) )

If you need to name the FOREIGN key constraint and define the FOREIGN key constraint for more than one column, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

CREATE TABLE Orders (id_o int not null,orderno int not null,id_p int,primary KEY (id_o), CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) )

SQL FOREIGN key Constraint on ALTER table if you create a FOREIGN KEY constraint for the id_p column if the Orders table already exists, use the following sql:mysql/sql Server/ Oracle/ms Access:

ALTER TABLE OrdersADD FOREIGN KEY (Id_P)REFERENCES Persons(Id_P)

If you need to name the FOREIGN key constraint and define the FOREIGN key constraint for more than one column, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (Id_P)REFERENCES Persons(Id_P)

To revoke the FOREIGN key constraint if you want to revoke the FOREIGN key constraint, use the following sql:mysql:

ALTER TABLE OrdersDROP FOREIGN KEY fk_PerOrders

SQL Server/oracle/ms Access:

ALTER TABLE OrdersDROP CONSTRAINT fk_PerOrders

SQL FOREIGN KEY Constraint