Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Gorav.

Asked: October 11, 2012 - 11:57 am UTC

Last updated: September 15, 2016 - 1:18 pm UTC

Version: 11.0.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I am totally confused about unique constraint.
I was reading Oracle Database Concepts 11g Release 2 (11.2), where its written about unique constraint that:


A unique key constraint requires that every value in a column or set of columns be unique. No rows of a table may have duplicate values in a column (the unique key) or set of columns (the composite unique key) with a unique key constraint.


Kindly explain the below doubts:

1) We are able to insert null values into this column then how does every value is unique.Even all the rows of unique constraint column can have null values.

2) What is the usage of unique constraint, why was it created/introduced when we have primary key.

3) When to use unique constraint instead of primary keys.


Thanks.


and Tom said...

The concepts guide goes on to say:

Unless a NOT NULL constraint is also defined, a null always satisfies a unique key constraint. Thus, columns with both unique key constraints and NOT NULL constraints are typical. This combination forces the user to enter values in the unique key and eliminates the possibility that new row data conflicts with existing row data.

Note:
Because of the search mechanism for unique key constraints on multiple columns, you cannot have identical values in the non-null columns of a partially null composite unique key constraint.



A null is never equal (nor not equal) to any other null, hence, a unique constraint on a single attribute will allow as many null rows as you can stuff in there.

If you have a composite key (more than one attribute) then the non-null attributes must be unique. for example:

ops$tkyte%ORA11GR2> create table t ( x int, y int, constraint xy_unique unique(x,y) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( null, null );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( null, null );

1 row created.

<b>that works because we don't know if the rows are unique or not.</b>

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 1, null );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 2, null );

1 row created.

<b>those are unique because we know X is unique and we don't know about Y</b>

ops$tkyte%ORA11GR2> insert into t values ( 2, null );
insert into t values ( 2, null )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.XY_UNIQUE) violated

<b>that we know is not unique</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 1, 1 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 1, 2 );

1 row created.

<b>just like that</b>



a constraint may evaluate to TRUE, FALSE or *unknown* and they only fail when they evaluate to FALSE.



a primary key has semantic meaning, it is to be immutable (never changing in value), unique and non-null.

a unique constraint simply says "at any point in time, these values are unique - they may change and they may be null"



You use a unique constraint when

a) you don't already have a primary key since a table may only have one
b) you allow nulls in the attributes
c) you allow updates to the values in the attributes

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SQL Server is different

djb, October 12, 2012 - 10:57 am UTC

It's been a number of years since I've worked with SQL Server so there may be a switch to change this. But they way they handle unique constraints and NULLs is different. They treat NULL as a value in the constraint, so you can have one NULL value, and if you attempt insert another NULL value you'll get a unique constraint violation.

I *much* prefer the way Oracle does it.

meaningless primary key

Gijs Bürmann, February 25, 2014 - 10:55 am UTC

When using a meaningless primary key value instead of a primary key with a semantic meaning, there is no business need for changing it. (fixing a typo for instance)
I recommend that every table should have:
1 primary key with meaningless value for system uniqueness purpose (referential integrity)
- one compound only
- never changing value
- unique at all times
- NOT NULL
1 or more unique constraints for user uniqueness purpose
- Their compounds may change over time
- unique at any point in time
- Their compounds may be NULL

Foreign keys may only reference primary keys. (Since primary keys are meaningless and never changing, referential integrity is easily enforced)

Exception: When user-uniqueness can't be defined in 'real life' (the business domain we create the information system for) we can’t and shouldn’t add a unique constraint. For example a bulk tables like a logging-table.

Unique key useful in dimension tables

Rosemary kariuki, September 15, 2016 - 12:35 pm UTC

For data warehouse dimension tables, star schema, it is necessary to define a natural key when the primary key is a surrogate key. The unique key is helpful in comparing changes in the row being inserted (eg. Type 2 dimensions") This response is similar to the person who mentioned "meaningless key" aka surrogate key.
Chris Saxon
September 15, 2016 - 1:18 pm UTC

True. This advice applies to all databases though! Anywhere you have a surrogate key, you should also create a unique constraint on the natural key.

Chris