Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Unique constraint over 2 columns with allowable NULLs
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.
Cheers
On 29 Nov, 16:50, DA Morgan <damor..._at_psoug.org> wrote:
> mikew01 wrote:
> > Hello, I need to add a unique constraint over 2 columns where one of
> > the columns could be NULL.
> > A standard UNIQUE constraint applied over these 2 columns will break
> > when someone tries to put a second NULL into the allowable NULL column
> > so Im wondering how to go about enforcing this constraint?
>
> > TIA
>
> 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.
>
> SQL> INSERT INTO t VALUES (2, NULL);
>
> 1 row created.
>
> SQL> INSERT INTO t VALUES (3, NULL);
>
> 1 row created.
>
> SQL> COMMIT;
>
> Commit complete.
>
> SQL>
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
Received on Fri Nov 30 2007 - 05:26:06 CST