Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, binoj.

Asked: January 22, 2018 - 1:04 am UTC

Last updated: January 23, 2018 - 2:00 am UTC

Version: Oracle 11 g

Viewed 1000+ times

You Asked

Dear Sir,

When asked what is the difference between Primary Key and Unique Key , People say
Primary Key cannot be NULL, and Unique Key can be NULL.

My Question is If we add a NOT NULL constraint on Unique Key , we can use Unique key constraint instead of Primary Key?
On what scenario we use Primary Key ?

Thanks
Binoj Daniel

and Connor said...

You are referring mainly to the *implementation* here of the primary key and unique constraints, but from a data definition perspective they are different. A primary key is the means via which we uniquely identify a row, and this is intended to be immutable. A unique constraint is a means of ensuring certain attributes in a table are unique.

For example: In a table of employees, the primary key might be EMPLOYEE_ID (which could be a meaningless number, or something assigned within my organization), and each employee is given a company mobile phone, so I might have a unique constraint on the mobile phone number.

"So why not make mobile phone the primary key?" is the obvious question. Well, phones get lost, get upgraded, you might change phone provider and get a new number etc. So I want it to be unique, but it does not fundamentally define the employee in question.

If you're interested, there is also a good thread on the underlying indexes etc here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=difference-between-unique-index-and-primary-key-index

Rating

  (1 rating)

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

Comments

thanks for the reply

binoj daniel, January 22, 2018 - 11:52 pm UTC

Thanks for the replay Connor,

but still... if not referred with a foreign key , Primarykey-EmpId and Unique key-Mobilenumber could be changeable.

So how can we say primary key is immutable ?

*implementation* could be defined more clear?

Thanks
Binoj Daniel



Connor McDonald
January 23, 2018 - 2:00 am UTC

By "implementation" I mean that to provide the *functionality*, ie, stop people from entering a duplicate, we will implement the primary key and unique key in a similar way, ie, with an underlying index.

But the concept is different - one identifies the row, the other ensures that the a column cannot contain duplicates.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.