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 privilegeUSAGE
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 theSELECT
object privilege on tables or views which are referenced in theSELECT
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 |