Difference between Primary key and Unique

[rajiv07]

Hi to all

I know i am asking simple question, but i get confusing in the two concepts.
Actually the questions is what is the difference between Primary key and unique.I think both are used to identify each row in the table and both r non repeatable.Both can declared more than one column in table.So my doubt is where the primary key and unique are getting differenciated.

Please help me with examples.

Reards

Rajiv

[ronverdonk]

The answers can be found in the wikipedia HERE:

In relational database design, a unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns. Depending on its design, a table may have arbitrarily many unique keys but at most one primary key.

A unique key must uniquely identify all possible rows that exist in a table and not only the currently existing rows. Examples of unique keys are Social Security numbers (associated with a specific person) or ISBNs (associated with a specific book). Telephone books and dictionaries cannot use names or words or Dewey Decimal system numbers as candidate keys because they do not uniquely identify telephone numbers or words.

A primary key is a special case of unique keys. The major difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is. Thus, the values in a unique key columns may or may not be NULL. Another difference is that primary keys must be defined using another syntax.The relational model, as expressed through relational calculus and relational algebra, does not distinguish between primary keys and other kinds of keys. Primary keys were added to the SQL standard mainly as a convenience to the application programmer.

Unique keys as well as primary keys can be referenced by foreign keys.

Ronald

[debasisdas]

Primary key enforces both unique and not null where as unique key enforces only uniqueness. Please follow the link in the previous post for details.

[rajiv07]

Thanks for your replies.I will go through it.

Rajiv

[ronverdonk]

Thanks for your replies.I will go through it.

Rajiv

You are welcome. See you next time.

Ronald

[manojkra]

Differences between primary key or unique key constraints and unique indexes
It is important to understand that there is no significant difference between a primary unique key constraint and a unique index. The database manager uses a combination of a unique index and the NOT NULL constraint to implement the relational database concept of primary and unique key constraints. Therefore, unique indexes do not enforce primary key constraints by themselves because they allow null values. (Although null values represent unknown values, when it comes to indexing, a null value is treated as being equal to other null values.)

Therefore, if a unique index consists of a single column, only one null value is allowed-more than one null value would violate the unique constraint. Similarly, if a unique index consists of multiple columns, a specific combination of values and nulls can be used only once.

Please check the below site for more details.

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Fc0020180.html