Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Unique constraint on multiple columns

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?

like image 412
Nicks Avatar asked Dec 03 '09 17:12

Nicks


People also ask

Can we apply unique constraint on multiple columns?

You can define a UNIQUE constraint at the column or the table level. Only at the table level, you can define a UNIQUE constraint across multiple columns. Once a UNIQUE constraint is defined, if you attempt to insert or update a value that already exists in the column, SQLite will issue an error and abort the operation.

How do I create a unique constraint on multiple columns in SQL Server?

SQL UNIQUE constraint for 2 columns example Notice that we named the UNIQUE constraints using CONSTRAINT keyword. We can use this name to remove the UNIQUE constraint later if we want. To define a UNIQUE on multiple columns, we put a comma-separated columns list inside parenthesis that follows the UNIQUE keyword.

Can unique constraints be multiple?

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

How many columns can hold unique constraints?

Defining Composite Unique Keys Oracle creates an index on the columns of a unique key, so a composite unique key can contain a maximum of 16 columns.


2 Answers

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 
like image 107
Vincent Malgrat Avatar answered Oct 26 '22 23:10

Vincent Malgrat


Yes, Oracle allows UNIQUE constraints to contain columns with NULL contents, but PRIMARY KEY constraints cannot contain columns containing NULL values. (Edited: was "... nullable columns...", but my example below shows that not to be true. Columns in a PK can be defined as nullable, but cannot contain NULL.)

You cannot have a UNIQUE constraint and a PRIMARY KEY constraint with the same columns.

SQL> create table stest (col1 integer not null, col2 integer null);  Table created.  SQL> alter table stest add constraint stest_uq unique (col1, col2);  Table altered.  SQL> insert into stest values (1, 3);  1 row created.  SQL> insert into stest values (1, null);  1 row created.  SQL> insert into stest values (1, null); insert into stest values (1, null) * ERROR at line 1: ORA-00001: unique constraint (SUSAN_INT.STEST_UQ) violated  SQL> insert into stest values (2, null);  1 row created.  SQL> commit;  Commit complete.  SQL> select * from stest;        COL1       COL2 ---------- ----------          1          3          1          2  SQL> alter table stest add constraint stest_pk PRIMARY KEY (col1, col2); alter table stest add constraint stest_pk PRIMARY KEY (col1, col2)                                                              * ERROR at line 1: ORA-01449: column contains NULL values; cannot alter to NOT NULL  SQL> truncate table stest;  Table truncated.  SQL> alter table stest add constraint stest_pk PRIMARY KEY (col1, col2); alter table stest add constraint stest_pk PRIMARY KEY (col1, col2)                                           * ERROR at line 1: ORA-02261: such unique or primary key already exists in the table  SQL> alter table stest drop constraint stest_uq;  Table altered.  SQL> alter table stest add constraint stest_pk PRIMARY KEY (col1, col2);  Table altered. 
like image 39
shoover Avatar answered Oct 26 '22 23:10

shoover