Verification of the Foreign Key Property (FOREIGN KEY)

Purpose

Use this to select rows that violate the specified foreign key property. This is the case if the row values do not exist in the specified columns of the referenced table.

Syntax

select_invalid_foreign_key::=

Selec Invalid Foreign Key

Selec Invalid Foreign Key

Selec Invalid Foreign Key

Usage Notes

  • Preferably, the referenced columns of the reference table should possess the primary key property. However, this is not verified by this statement.
  • In the formulation without select_list, the columns to be checked for the foreign key property are selected.
  • ROWNUM cannot be used in combination with this statement.
  • Verification of the foreign key property occurs directly in the table specified in the FROM clause. It is not until WHERE, GROUP BY, and similar are used on the table with the columns that violate the property.

Examples

The following examples relate to this table:

SELECT * FROM t1;
NR NAME FIRST_NAME
1 meiser inge
2 mueller hans
3 meyer karl
3 meyer karl
5 schmidt ulla
6   benno
2 fleischer jan 
SELECT * FROM t2;
ID NAME FIRST_NAME
1 meiser otto
2 mueller hans
3 meyer karl
5 schmidt ulla
6   benno
7 fleischer benno 
SELECT first_name,name WITH INVALID FOREIGN KEY (nr) from T1
    REFERENCING T2 (id);

-- Empty result because all of the values of nr exist in the column id
SELECT * WITH INVALID FOREIGN KEY (first_name,name) from T1
REFERENCING T2;
NR FIRST_NAME NAME
1 meiser inge
6   benno 
SELECT INVALID FOREIGN KEY (nr,first_name,name) from T1
REFERENCING T2 (id, first_name,name);
NR FIRST_NAME NAME
1 meiser inge
6   benno
2 jan fleischer