Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Gorav.

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

Answered by: Tom Kyte - Last updated: September 15, 2016 - 1:18 pm UTC

Category: Database - Version: 11.0.2

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Attribute clustering (part 3)

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 we 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

and you rated our response

  (3 ratings)

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

Reviews

SQL Server is different

October 12, 2012 - 10:57 am UTC

Reviewer: djb from Redmond, WA

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

February 25, 2014 - 10:55 am UTC

Reviewer: Gijs Bürmann from The Netherlands

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

September 15, 2016 - 12:35 pm UTC

Reviewer: Rosemary kariuki from Pittsburgh, PA, USA

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

Followup  

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