FOREIGN KEY constraints

When two tables contain one or more common columns, the Oracle mode of OceanBase Database can enforce the relationship between the two tables by using a FOREIGN KEY constraint, which is also called a referential integrity constraint.

Features

Each value in the column on which a FOREIGN KEY constraint is defined must match a value in the corresponding column in the other specified table. The syntax is as follows:

REFERENCES [ schema. ] object [ (column_name [, column_name...]) ] [ON DELETE { CASCADE | SET NULL } ]

The following table describes the terms related to referential integrity constraints.

Example: Create a table on which a FOREIGN KEY constraint is defined.

obclient> CREATE TABLE supplier_groups(
     group_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
     group_name VARCHAR2(127) NOT NULL,
     PRIMARY KEY (group_id)  
      );
Query OK, 0 rows affected

obclient> CREATE TABLE suppliers (
     supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
     supplier_name VARCHAR2(127) NOT NULL,
     group_id NUMBER NOT NULL,
     PRIMARY KEY(supplier_id),
     CONSTRAINT fk_name FOREIGN KEY(group_id) REFERENCES supplier_groups(group_id)
     );
Query OK, 0 rows affected

Features of FOREIGN KEY constraints are described as follows:

• Self-referential integrity constraints

  A self-referential integrity constraint involves a foreign key that references a parent key in the same table. The sample statement for creating a table with a self-referential integrity constraint is as follows:

  obclient> CREATE TABLE employee (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(30),
  salary VARCHAR(30),
  manager_id INT,
  CONSTRAINT sr_fk_emp_man FOREIGN KEY (manager_id) REFERENCES employee(employee_id)
  );
  

• Null values and foreign keys

  The relational model allows the value of a foreign key to match the value of the referenced primary key or unique key, or be null. If a column of a composite foreign key is null, the non-null part of the key does not need to match the corresponding part of the parent key.

• Parent key modifications and foreign keys

  The relationship between a foreign key and a parent key affects the deletion of the parent key. When the parent key is modified, referential integrity constraints can specify to perform the DELETE NO ACTION or DELETE CASCADE operation on the affected rows in a child table.

  The following table describes the DML statements allowed by different referential operations on the key values in the parent table and the foreign key values in the child table.

  DML statement	Allowed on the parent table	Allowed on the child table
  INSERT	Allowed if the parent key value is unique.	Allowed if the foreign key value exists in the parent key, or is partially or all null.
  DELETE NO ACTION	Allowed if no rows in the child table reference the parent key value.	Yes
  DELETE CASCADE	Yes	Yes

• Indexes and foreign keys

  Generally, foreign key columns are indexed columns. The only exception occurs when the matching unique key or primary key is never updated or deleted. Indexing the foreign keys in child tables has the following benefits:

  • Prevents full table locks on the child tables. The database needs to acquire only a row lock on the index.

  • Requires no full table scan of the child tables. ​

Common operations

• Create a FOREIGN KEY constraint on a table.

  ALTER TABLE child ADD CONSTRAINT fk_name_1
  FOREIGN KEY (col1) REFERENCES parent (col1);
  

• Drop a FOREIGN KEY constraint.

  ALTER TABLE child DROP CONSTRAINT fk_name_1;
  

• Disable a FOREIGN KEY constraint.

  ALTER TABLE child DISABLE CONSTRAINT fk_name_1;
  

• Enable a FOREIGN KEY constraint.

  ALTER TABLE child ENABLE CONSTRAINT fk_name_1;