Verification of the Primary Key Property (PRIMARY KEY)

Purpose

Use this to check whether a number of columns have the primary key property. This is the case if the corresponding row value combinations appear more than once, or if single row values are NULL. Rows that do not conform to the primary key property are selected.

Syntax

select_invalid_primary_key::=

Select invalid primary key

Select invalid primary key

Usage Notes

  • In the formulation without select_list, the columns to be checked for the primary key property are selected.
  • ROWNUM cannot be used in combination with this statement.
  • Verification of the primary key property occurs in the table stated 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 first_name,name WITH INVALID PRIMARY KEY (nr) from T1;
FIRST_NAME NAME
hans mueller
jan fleischer
karl meyer
karl meyer
SELECT * WITH INVALID PRIMARY KEY (nr,name) from T1;
NR NAME FIRST_NAME
3 meyer karl
3 meyer karl
6   benno
SELECT INVALID PRIMARY KEY (first_name) from T1;
FIRST_NAME
karl
karl