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::=
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:
NR | NAME | FIRST_NAME |
---|---|---|
1 | meiser | inge |
2 | mueller | hans |
3 | meyer | karl |
3 | meyer | karl |
5 | schmidt | ulla |
6 | benno | |
2 | fleischer | jan |
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
NR | FIRST_NAME | NAME |
---|---|---|
1 | meiser | inge |
6 | benno |
NR | FIRST_NAME | NAME |
---|---|---|
1 | meiser | inge |
6 | benno | |
2 | jan | fleischer |