0

I am new to Databases and while i was studying about constraints in SQL. I came across Primary Key, Unique Key, Not Null Constraints. As per my understanding a primary key is used to uniquely identify a column in a table and cannot have null values. A unique key can have one Null Values and cannot contain duplicate values. A not null constraint doesn't allow us to enter null value in the column.

My question is: Why should i apply Primary key on a column if i can get the same functionality by applying Unique and Not Null Constraints together on a column.

Can anyone explain this to me. Thanks in advance.

2
0

There's a bit if history involved here.

When you design your tables, you have a number of "candidate keys".

  • Column (a) can identify a row
  • So can columns (f,g)
  • So can column (i)

The late Edgar Codd decided that one of those should be considered "better", "more important", "stronger" or whatever than the others. This is the one you make the Primary Key.

What about the others? They are Alternate Keys. Unfortunately there's no constraint type with that name, so for this you use the UNIQUE constraint.

The concept of having to decide which of the candidate keys are "better" than the others has by some been considered a bad move. I think that Chris Date and/or Joe Celko has that viewpoint, for instance.

I've seen reasoning that the concept of "there can be only one" was rooted in physical storage aspect, so we get some type of coupling between the "better key" and some storage thingie (like a "primary index" or "clustered index"). Unfortunately Edgar Codd isn't alive, so we can't ask him whether there's some truth to this reasoning. So we just have to live with this design, made a few decades ago.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy