Primary key vs Unique key

Hi,

Can anyone tell what are the specific scenarios where Unique key is recommened over primary key ?

While designing a database table in what all cases we should think about going for Unique key rather than a primary key.

Regards,

Amit

I sometimes find tables that have natural keys but are implemented using an identity column as the primary key. In most cases the natural key should be given a UNIQUE constraint.

|||

As per the Scenario:

Priamary Key

- When you want to keep a identifier for each row. So each row can be obtianed by the Key value.(since pk unique and not null)

Unique Key

- When you want to keep secondary identifier where already the primary exists in the same table. So each row can be obtained by your PK or UK. Unique will allow null but only once(what is the use to allow single null).

Note:

Both can be either clustered or non-clustered. When you create a constraint you are allowed to choose which type of

index you want to use for these keys.

|||

Is there something related to performance of DBMS in this regard?

What is recommended if you want to achieve optimal performance.

If PK and UK are almost similar to each other , then why do we define PK on table everytime?

Why cant we simply define UK over a table with a clustered index on it to have physical ordering of data. (PK by default defines clustered index on a table in SQL server )

What will happen if we replace PK by UK?

|||

Yes. Everyone knows that PK never allows null so we always perfers the PK for any row identifier columns. You can have UNIQUE + NOT NULL + CLUSTERED to work loke PK, the advantage is you can have more than one UNIQUE + NOT NULL key. But only one CLUSTERED is allowed per table.

If the table doesn;t have clustered index already then only the PK uses clustered index by default, otherwise the default is non-clustered.

Actualy when you create the primary key or unique key you can explicitly set the type of index. But since it is a optional clauase we always missing it..

(example)

Create Table Sampleadata

(

id int primary key nonclustered,

Name int unique clustered

)