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...@xxxxxxxxx> 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...@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org


.

Relevant Pages

  • Re: SQL
    ... Even SQL cannot avoid them. ... If you add X with one constraint to Y with other constraint and what would ... RDBMS exist only because in 70s there were no languages capable to provide ... There is no numeric type for all purposes. ...
    (comp.object)
  • Re: SQL
    ... SQL is more or less based on "COBOL thinking". ... possible nor most type-free that a relational query language can be. ... If you add X with one constraint to Y with other constraint and what would ... A relational interface fits to a kd-tree as a beach umbrella does ...
    (comp.object)
  • Re: Parameter??
    ... in common with most available SQL ... given month' Validation Rule or constraint? ... date_col DATETIME NOT NULL, ... The above relies on the Jet implementation to coerce a DATETIME to ...
    (microsoft.public.access.queries)
  • 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) ... Best regards ...
    (comp.databases.oracle.server)
  • Re: How do you mulitply in a field?
    ... asked how one places a unique constraint on a combination of fields ... This will work for Jet in ANSI-92 Query Mode Jet SQL. ... I apply security so that the database can't be hacked. ... If I am selecting only one name, ...
    (microsoft.public.access.tablesdbdesign)