Re: Unique constraint over 2 columns with allowable NULLs

mikew01 schrieb:
Thanks for the replies, but in both cases you have put different data
into the non null column so the uniqueness constraint has not been
violated.

Following on from the previous example...

SQL> CREATE TABLE t (
2 col1 NUMBER,
3 col2 NUMBER);

Table created.

SQL> ALTER TABLE t
2 ADD CONSTRAINT uc_t
3 UNIQUE (col1, col2)
4 USING INDEX;

Table altered.

SQL> INSERT INTO t VALUES (1, NULL);

1 row created.

What might happen is this again

SQL> INSERT INTO t VALUES (1, NULL);

Which will fail with Oracle

Ive tried using a trigger to check the values when they are being
updated but I get a mutating table error.



create unique index t_uidx on t(nvl2(col1+col2,col1,null),nvl2(col1+col2,col2,null));


Best regards

Maxim
.

Relevant Pages

  • Re: Unique constraint over 2 columns with allowable NULLs
    ... into the non null column so the uniqueness constraint has not been ... SQL> CREATE TABLE t ( ... UNIQUE (col1, col2) ...
    (comp.databases.oracle.server)
  • Re: NULLs
    ... Can you tell us what part of the SQL '92 standard it says this? ... SQL> create unique index unq_allnulltest on allnulltest ...
    (comp.databases.oracle.server)
  • Re: Compound index and unique
    ... Forget about using a UNIQUE index, you're just asking for trouble and/or ... If you need a list for display purposes, then do a SQL SELECT using the ... Microsoft Visual FoxPro MVP ... "Charles" wrote in message ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: Creating a sqlce index in code?
    ... You can add UNIQUE (create unique index) and/or sort order for columns ... (ASC/DESC) ... See SQL CE Books Online for more information (available if you install SQL ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: bookmark continuous form slow slow slow
    ... In access when I did "New Table - LINK" I did an ODBC link to ... the field in my SQL Table and I checked the ones that made up the ... Unique Record Identifier prompt. ... If you're using a view, then, yes, you'll have to set the unique index on ...
    (comp.databases.ms-access)