Oracle Database - FOREIGN KEY Constraints

1 - About

A foreign key is a referential constraint between two tables.

The foreign key identifies:

  • a column
  • or a set of columns

in one (Child/dependent) table that refers to set of columns in another (parent/referenced) table.

It ensures that the other table already has a primary or unique key constraint on the corresponding columns. A foreign key constraint enforces then referential integrity. (Ie the tables have data that relate to each other).

Locking behaviour depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.

3 - Star Schema

In a star schema data warehouse, FOREIGN KEY constraints validate the relationship between the fact table and the dimension tables. A sample constraint might be:

ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id)


However, in some situations, you may choose to use a different state for the FOREIGN KEY constraints, in particular, the ENABLE NOVALIDATE state. A data warehouse administrator might use an ENABLE NOVALIDATE constraint when either:

  • The tables contain data that currently disobeys the constraint, but the data warehouse administrator wishes to create a constraint for future enforcement.
  • An enforced constraint is required immediately.

Suppose that the data warehouse loaded new data into the fact tables every day, but refreshed the dimension tables only on the weekend. During the week, the dimension tables and fact tables may in fact disobey the FOREIGN KEY constraints. Nevertheless, the data warehouse administrator might wish to maintain the enforcement of this constraint to prevent any changes that might affect the FOREIGN KEY constraint outside of the ETL process. Thus, you can create the FOREIGN KEY constraints every night, after performing the ETL process, as shown in the following:

ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id)

ENABLE NOVALIDATE can quickly create an enforced constraint, even when the constraint is believed to be true. Suppose that the ETL process verifies that a FOREIGN KEY constraint is true. Rather than have the database re-verify this FOREIGN KEY constraint, which would require time and database resources, the data warehouse administrator could instead create a FOREIGN KEY constraint using ENABLE NOVALIDATE.

3.2 - RELY

The ETL process commonly verifies that certain constraints are true. For example, it can validate all of the foreign keys in the data coming into the fact table. This means that you can trust it to provide clean data, instead of implementing constraints in the data warehouse. You create a RELY constraint as follows:

  ALTER TABLE sales ADD CONSTRAINT sales_time_fk
  FOREIGN KEY (time_id) REFERENCES times (time_id) 

This statement assumes that the primary key is in the RELY state. RELY constraints, even though they are not used for data validation, can:

  • Enable more sophisticated query rewrites for materialized views.
  • Enable other data warehousing tools to retrieve information regarding constraints directly from the Oracle data dictionary.

Creating a RELY constraint is inexpensive and does not impose any overhead during DML or load. Because the constraint is not being validated, no data processing is necessary to create it

4 - Metadata Disable or enable all the foreign key of a primary key

A select statement with:

  • an alter statement to disable or enable all the foreign key of a primary key
  • to see all the referenced table of a primary key

Just give a table name value for the bin :TableWithPrimaryKey

        Referentielconstraint.Table_Name TABLE_NAME
      , Userconstraint.R_Constraint_Name Primary_Key
      , Userconstraint.Constraint_Name Foreign_Constraint_Name
      , UserConstraint.Table_Name Foreign_Key_Table
      , UserConstraint.status
      , 'alter table '
        || UserConstraint.Table_Name
            WHEN UserConstraint.status = 'DISABLED'
            THEN ' enable '
            ELSE ' disable '
        || ' constraint '
        || Userconstraint.Constraint_Name
        || ' ;' AS alter_statement
                  , R_CONSTRAINT_NAME
                  , TABLE_NAME
                  , STATUS
                    Constraint_Type = 'R'
      , USER_CONSTRAINTS ReferentielConstraint
        Userconstraint.R_Constraint_Name     = Referentielconstraint.Constraint_Name
        AND Referentielconstraint.Table_Name = UPPER( :TableWithPrimaryKey ) ;
db/oracle/foreign_key.txt ยท Last modified: 2017/09/13 21:21 by gerardnico