CODEWITHSUNDEEP
oracle-databaseuniquecheck-constraints
Marcelo Martinez
Marcelo Martinez

Reputation: 61

Oracle check constraint column either zero or unique

I have to add a constraint to an Oracle table. A numeric column should be zero or unique(and >0). I tried inserting queries inside the constraint definition or functions but they are no allowed. Any idea? Greetings, Marcelo.

Upvotes: 1

Views: 1498

Answers (1)

Aleksej
Aleksej

Reputation: 22949

If I understand well your need, you could use a function-based index to check uniqueness on only positive values.

For example:

SQL> create table checkUniqueTab(n number);

Table created.

SQL> create unique index i1 on checkUniqueTab( case when n > 0 then n end);

Index created.

Inserting different values work:

SQL> insert into checkUniqueTab values (1);

1 row created.

SQL> insert into checkUniqueTab values (2);

1 row created.

Here I try to insert a duplicate positive value:

SQL> insert into checkUniqueTab values (1);
insert into checkUniqueTab values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (ALEK.I1) violated

I can insert many rows with 0 value:

SQL> insert into checkUniqueTab values (0);

1 row created.

SQL> insert into checkUniqueTab values (0);

1 row created.

The result:

SQL> select * from checkUniqueTab;

         N
----------
         1
         2
         0
         0

This assumes that you already have a constraint on your table to prevent inserting negative values.

Upvotes: 2

Related Questions