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?

user2847546
  • 39
  • 2
  • You can only have one primary key per table. – Mihai Oct 05 '13 at 17:03
  • 1
    also u might want to read the second comment in the above duplicate link ;) – Vandesh Oct 05 '13 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. – nvogel Oct 23 '13 at 02:18

2 Answers2

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.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • 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.. – user2847546 Oct 06 '13 at 05:24
  • thank you Mr.Rahul i got it now – user2847546 Oct 06 '13 at 16:25
  • 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. – nvogel Oct 23 '13 at 02:23
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.

Pieter Geerkens
  • 11,775
  • 2
  • 32
  • 52
  • 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. – nvogel Oct 23 '13 at 02:36
  • @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. – Pieter Geerkens Oct 24 '13 at 03:55
  • 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. – nvogel Oct 24 '13 at 05:42
  • @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. – Pieter Geerkens Oct 25 '13 at 03:04