1

What is the difference between a primary key and an attribute which is declared as UNIQUE and NOT NULL in the same table in a database?

Improve this question
3
  • You can only have one primary key per table. Commented Oct 5, 2013 at 17:03
  • 1
    also u might want to read the second comment in the above duplicate link ;) Commented Oct 5, 2013 at 17:05
  • Logically speaking there is no difference. In practice there may be some subtle differences depending on your DBMS software - usually to do with internal storage and indexing. Commented Oct 23, 2013 at 2:18

2 Answers 2

Reset to default
3

There can only be one primary key in a table and that cannot be null. You dont have to explicitly specify the NOT NULL attribute for primary key.

There can be more than one unique key in the table. Unique key can be null

When you specify any column as NOT NULL then it means that you cannot leave the column as NULL

EDIT:-

As per your comments, you cannot have more than one primary key in your table.

Also a unique key constraint does not imply the NOT NULL constraint in practice. Besides what is the use of writing NOT NULL for unique key as that is the only difference between Primary key and Unique key.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

if we specify a unique key as not null then does it serve same as primary key??and can we have more than one primary key in a table..
thank you Mr.Rahul i got it now
You are abusing the term "key" here. By definition no key (i.e. candidate key) may permit null values. If a column permits nulls then it definitely isn't a key column, whether or not it has a UNIQUE constraint on it. So it's right to say that there can be more than one key in a table but wrong to say that a key can be null.
0

It is unfortunate that that SQL constraint was named primary key because it confuses the related but different concepts of the Logical Entity primary key and the Physical Storage primary key of the entity. While it is certainly possible, and not infrequently the case, that the same fields are used for both the logical and physical primary key, this is not required and also frequently not desirable.

The Logical Entity primary key is the object referred to in Database textbooks discussing relational theory. It is this object that one refers to when Normalizing relation structures, and it is (of course) one of the candidate keys that exist for the Entity in the logical model.

The Physical Storage primary key is an attribute in the Physical Model, possibly in addition to those attributes defined in the Logical Model for the Entity, which is used by the DBMS to uniquely identify the storage for an instance of the Logical Entity. This is the object being referred to in the primary key constraint when defining the (physical) schema for an entity's storage. When this Physical Storage primary key differs from the Logical Entity primary key it is most commonly because an artificial key has been appended to the logical attributes in order to:

  • Enable update of fields in the Logical Entity primary key;
  • Improve performance by being a narrower key for foreign key reference refer to

All Logical Entity candidate keys should be defined in the Physical Model with uniqueness constraints. Only the Physical Storage primary key becomes the primary key constraint.

Improve this answer

4 Comments

The idea of "primary key" being a physical storage feature predates the relational model. The relational and SQL usage of the term is under discussion here and that's strictly a logical, not physical construct. It would be more accurate to say that some SQL DBMSs unwisely use the logical primary key as a cue for certain physical storage features, thus compromising the principle of physical data independence. This isn't universally the case however and it's a bad idea to assume that physical always follows logical; even worse to assume that logical ought to follow physical.
@sqlvogel: My point is that the two concepts are distinct, are often not the same set of attributes, and should thus be referred to by distinct names to avoid confusion. It is not clear to me whether you are agreeing or disagreeing.
PRIMARY KEY is not two concepts in a SQL DBMS because keys are a logical feature not physical. That is what I was disagreeing with. On re-reading your answer however, I think I understand that what you mean by "logical" is a preliminary design for a database table and what you mean by "physical" is an implementation of a database table. Is that right? I recommend you be very careful using such terms on SO because they often lead to confusion. In database-theoretic terms keys and tables are always logical features of a database.
@sqlvogel: EXACTLY! Yet the DBMS (ie physical) constraint is called the primary key constraint, which is a confusion of terms. I may not be completely up-to-date on the terminology, but was proposing one alternate terminology that would eliminate the confusion; and allow both more lucid thinking and clearer communication.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.