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?
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.
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.
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.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With