oracle - Unique constraint on multiple columns

Question

I am using an oracle table and have created a unique constraint over four columns. Can these columns within the constraint have NULL in them?

question from:https://stackoverflow.com/questions/1841388/unique-constraint-on-multiple-columns

Answer 1

you can have NULLs in your columns unless the columns are specified NOT NULL. You will be able to store only one instance of NULLs however (no two sets of same columns will be allowed unless all columns are NULL) :

SQL> CREATE TABLE t (id1 NUMBER, id2 NUMBER);

Table created
SQL> ALTER TABLE t ADD CONSTRAINT u_t UNIQUE (id1, id2);

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

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

INSERT INTO t VALUES (1, NULL)

ORA-00001: unique constraint (VNZ.U_T) violated

SQL> /* you can insert two sets of NULL, NULL however */
SQL> INSERT INTO t VALUES (NULL, NULL);

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

1 row inserted