Re: SQL Question: CHECK constraint or trigger?

John Wiberg <Nobody_at_NoWhere.com> wrote in article <34FD6D69.3BD0_at_NoWhere.com>...
> Try changing the check constraint to (CURRENT='Y'). That will allow
> values of 'Y' or NULL, NULL replacing 'N' in your application logic.
> Then add a unique key constraint on (NAMEID, CURRENT). This combination
> of constraints will allow one instance of NAMEID + 'Y' and multiple
> instances of NAMEID + NULL.

as far as I can see, and have quickly tested, that will let you have 1 instance of (nameid, 'Y') and only 1 of (nameid, null) for a given nameid, not multiple.

SQL> create table test (nameid integer, curr varchar2(1));

Table created.

SQL> alter table test add constraint test_uk unique (nameid, curr);

Table altered.

SQL> insert into test values (1, 'Y');

1 row created.

SQL> insert into test values (1, null);

1 row created.

SQL> insert into test values (1, null);
insert into test values (1, null)
*
ERROR at line 1:
ORA-00001: unique constraint (SMS.TEST_UK) violated  

Andrew - Wizzard

barnetaj_at_bp.com Received on Wed Mar 04 1998 - 00:00:00 CET