CODEWITHSUNDEEP
sqldatabaseprimary-key
Shashankk Shekar Chaturvedi
Shashankk Shekar Chaturvedi

Reputation: 435

In SQL, why do we need a primary key if we can use NOT NULL and UNIQUE constraints in place of a primary key?

A primary key in SQL is a combination of both NOT NULL and UNIQUE constraints, with the only difference that only one primary key constraint can exist within a table.

Why can't we only manage with the NOT NULL and UNIQUE constraints? Even they both do the same thing.

Upvotes: 3

Views: 2074

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

You pointed a differences between a primary key and unique constraints, but this is not the only differences between them.

The key differences between a primary key and a unique key is - a clustered index is automatically created when a primary key is defined whereas a unique key generates the non-clustered index. You can do some Google search to get an idea on a clustered and non-clustered index.

Another differences is - a primary key is a unique field on a table, but it is special in the sense that the table considers that row as its key. This means that other tables can use this field to create foreign key relationships to themselves. A unique constraint simply means that a particular field must be unique.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The definition of a primary key is:

  • A primary key is unique.
  • A primary key is not null.
  • Table has only one primary key.

You are asking about the third condition. Well, that is the definition. The "primary key" is a single set of keys that have been explicitly chosen to uniquely identify each row in the table. The word "primary" implies that there is only one per table. Other keys or combinations of keys that meet the first two conditions are called candidate primary keys.

Although not strictly enforced, primary keys are the best method for referencing individual rows. They should be used for foreign key constraints, for instance (and any database that I come into contact with does enforce primary keys for foreign key constraints). Having multiple different keys refer to a single table confuses the data model. Think about Entity-Relationship modeling. The links should be primary keys.

To give a flavor of the use of primary keys, some databases (such as MySQL using the InnoDB storage engine) by default cluster tables based on the primary key. A table can only be clustered once, hence the use of a single key.

Upvotes: 5

Related Questions