Verification of the Uniqueness (UNIQUE)

Purpose

Use this to verify if the rows of a number of columns cols are unique. This is the case if the specified columns cols do not contain data records in duplicate. Rows in the specified columns cols which only contain NULL values are classified as being unique (even if there is more than one). Non-unique rows are selected.

Prerequisite

  • You need either the system privilege USE ANY SCHEMA or the object privilege USAGE on the target schema(s), or the schema(s) must be owned by you or one of your assigned roles.
  • You need either the system privilege SELECT ANY TABLE or the SELECT object privilege on tables or views which are referenced in the SELECT list, or the tables or views must be owned by you or one of your assigned roles.

Syntax

select_invalid_unique::=

select invalid unique

select invalid unique

Usage Notes

  • In the formulation without select_list, the columns to be checked for uniqueness are selected.
  • ROWNUM cannot be used in combination with this statement.
  • Verification of uniqueness 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 uniqueness.

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
3    
SELECT INVALID UNIQUE (first_name, name) from T1;
FIRST_NAME NAME
karl meyer
karl meyer
SELECT * WITH INVALID UNIQUE (nr,name) from T1;
NR NAME FIRST_NAME
3 meyer karl
3 meyer karl
SELECT first_name WITH INVALID UNIQUE (nr, name, first_name) from T1;
FIRST_NAME
karl
karl