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::=
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:
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 |
FIRST_NAME | NAME |
---|---|
karl | meyer |
karl | meyer |
NR | NAME | FIRST_NAME |
---|---|---|
3 | meyer | karl |
3 | meyer | karl |
FIRST_NAME |
---|
karl |
karl |