SQL FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

Look at the following two tables:

Products table:

product table inner join

Category table:

Notice that the "Category_id" column in the "category" table points to the "Category_id" column in the "product" table.

The "Category_id" column in the "category" table is the PRIMARY KEY in the "category" table.

The "Category_id" column in the "products" table is a FOREIGN KEY in the "products" table.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "Caterory_id" column when the "products" table is created:

Example

MySQL

 CREATE TABLE  products(
 Product_id int  NOT NULL  ,
Product_name varchar( 50 ) NOT NULL ,
Category_id int ,
 PRIMARY KEY  (Product_id),
 FOREIGN KEY  (Category_id) REFERENCES category(Category_id)
 );

You can click on above box to edit the code and run again.

Example

Example

SQL Server / Oracle / MS Access:
 CREATE TABLE  products(
Product_id int  NOT NULL PRIMARY KEY ,
Product_name varchar( 50 ) NOT NULL ,
Category_id int 
                 
 FOREIGN KEY  REFERENCES category(Category_id)
);

You can click on above box to edit the code and run again.

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

Example

MySQL / SQL Server / Oracle / MS Access:

 CREATE TABLE  products(
Product_id int  NOT NULL  ,
 Product_name varchar( 50 ) NOT NULL ,
Category_id int ,
 PRIMARY KEY  (Product_id),
 CONSTRAINT FK_categoryproducts  FOREIGN KEY  (Category_id)
REFERENCES category(Category_id)

);

You can click on above box to edit the code and run again.

SQL FOREIGN KEY on ALTER TABLE

The following SQL creates a FOREIGN KEY constraint on the "Category_id" column when the products table is already created, use the following SQL:

Example

MySQL / SQL Server / Oracle / MS Access:
 ALTER TABLE  products
 ADD FOREIGN KEY (Category_id)
 REFERENCES  category(Category_id) ;

You can click on above box to edit the code and run again.

To name a FOREIGN KEY constraint, and to define a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

Example

MySQL / SQL Server / Oracle / MS Access:
 ALTER TABLE  products
ADD CONSTRAINT  FK_categoryproducts
 FOREIGN KEY (Category_id) REFERENCES  category(Category_id) ;

You can click on above box to edit the code and run again.

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY FOREIGN KEY constraint, use the following SQL:

Example

MySQL:
ALTER TABLE products DROP FOREIGN KEY FK_categoryproducts;

You can click on above box to edit the code and run again.

Example

SQL Server / Oracle / MS Access:
 ALTER TABLE  products
DROP CONSTRAINT  FK_categoryproducts;

You can click on above box to edit the code and run again.

Output