Oracle / plsql: foreign keys with set null on delete

Drop MySQL foreign key constraints

To drop a foreign key constraint, you use the statement:

In this syntax:

• First, specify the name of the table from which you want to drop the foreign key after the keywords.
• Second, specify  the constraint name after the keywords.

Notice that is the name of the foreign key constraint specified when you created or added the foreign key constraint to the table.

To obtain the generated constraint name of a table, you use the statement:

For example, to see the foreign keys of the table, you use the following statement:

The following is the output of the statement:

As you can see clearly from the output, the table table has one foreign key constraint:

And this statement drops the foreign key constraint of the table:

To ensure that the foreign key constraint has been dropped, you can view the structure of the products table:

Create a foreign key — Using ALTER TABLE statement

Syntax

The syntax for creating a foreign key using an ALTER TABLE statement in SQL Server (Transact-SQL) is:

ALTER TABLE child_table
ADD CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n);

child_table

The name of the child table that you wish to modify.

fk_name

The name of the foreign key constraint that you wish to create.

child_col1, child_col2, … child_col_n

The columns in child_table that will reference a primary key in the parent_table.

parent_table

The name of the parent table whose primary key will be used in the child_table.

parent_col1, parent_col2, … parent_col3

The columns that make up the primary key in the parent_table. The foreign key will enforce a link between this data and the child_col1, child_col2, … child_col_n columns in the child_table.

Example

Let’s look at an example of how to create a foreign key in SQL Server (Transact-SQL) using the ALTER TABLE statement.

For example:

ALTER TABLE inventory
ADD CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id);

In this foreign key example, we’ve created a foreign key on the inventory table called fk_inv_product_id that references the products table based on the product_id field.

We could also create a foreign key with more than one field as in the example below:

ALTER TABLE inventory
ADD CONSTRAINT fk_inv_product
    FOREIGN KEY (product_name, location)
    REFERENCES products (product_name, location);

In this SQL Server example, we have created a foreign key on the inventory table called fk_inv_product that references the products table based on the product_name and location columns.

Oracle foreign key constraint syntax

Oracle allows you to create, add, drop, disable and enable a foreign key constraint.

Create a foreign key constraint

The following statement illustrates the syntax of creating a foreign key constraint when you create a table:

Let’s examine the statement in detail.

First, to explicitly assign the foreign key constraint a name, you use the clause followed by the name. The clause is optional. If you omit it, Oracle will assign a system-generated name to the foreign key constraint.

Second, specify the clause to defines one or more column as a foreign key and parent table with columns to which the foreign key columns reference.

Third, use the clause to specify consequence when the rows in the parent table are deleted.

• : if a row in the parent is deleted, then all the rows in the child table that reference the removed row will be deleted.
• : if a row in the parent is deleted, then all the rows in the child table reference the removed row will be set to NULL for the foreign key columns.

Unlike the primary key constraint, a table may have more than one foreign key constraint.

Enable a foreign constraint

Similarly, you use also use the statement to enable a disabled foreign key constraint:

In this tutorial, you have learned how to use the Oracle foreign key constraint to enforce the relationship between tables.

• Was this tutorial helpful?

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the «PersonID» column when the «Orders» table is created:

MySQL:

CREATE TABLE Orders
(
   
OrderID int NOT NULL,
   
OrderNumber int NOT NULL,
   
PersonID int,
   
PRIMARY KEY (OrderID),
   
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
   
OrderID int NOT NULL PRIMARY KEY,
   
OrderNumber int NOT NULL,
   
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

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

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
   
OrderID int NOT NULL,
   
OrderNumber int NOT NULL,
   
PersonID int,
   
PRIMARY KEY (OrderID),
   
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
   
REFERENCES Persons(PersonID)
);

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the «PersonID» column when the «Orders» table is created:

MySQL:

CREATE TABLE Orders
(
   
OrderID int NOT NULL,
   
OrderNumber int NOT NULL,
   
PersonID int,
   
PRIMARY KEY (OrderID),
   
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
   
OrderID int NOT NULL PRIMARY KEY,
   
OrderNumber int NOT NULL,
   
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

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

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
   
OrderID int NOT NULL,
   
OrderNumber int NOT NULL,
   
PersonID int,
   
PRIMARY KEY (OrderID),
   
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
   
REFERENCES Persons(PersonID)
);

Disabling foreign key checks

Sometimes, it is very useful to disable foreign key checks e.g., when you import data from a CSV file into a table. If you don’t disable foreign key checks, you have to load data into a proper order i.e., you have to load data into parent tables first and then child tables, which can be tedious. However, if you disable the foreign key checks, you can load data into tables in any order.

To disable foreign key checks, you use the following statement:

And you can enable it by using the following statement:

In this tutorial, you have learned about the MySQL foreign key and how to create a foreign key constraint with various reference options.

• Was this tutorial helpful?