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

)

previewing specific records in a report

Hi,

I have a form which contains records about patients. the primiary is PatientID and is based on the national ID number.

I have compiled a query to show the patient's bill (invoice). from this query I have created a report. i have placed a button on the same form mentioned above that will open the report. right now the button opens the report and all the records are showing.

i would like it that when the user goes to a particular record in the form, the user can click on the button to preview just that record's bill and no others.

i'm using access 2000.

bajanElfi should also mention i was told that i could "fix" the query in sql, but i'm not to sure how to do that.|||Originally posted by bajan_elf
i should also mention i was told that i could "fix" the query in sql, but i'm not to sure how to do that. The simplest answer is to build a criteria that restricts the query to just the data for a particular employee or bill. Without knowing a good bit more about your application, I can't really give you any specifics. The basic process isn't tough, you just add a criteria in the query form where client_number = x or where bill_id = y.

-PatP|||hmmm... no worries Pat. i got it working. it seems i needed a piece of code inthe command button the opens the report. below is what i added.

DoCmd.OpenReport stDocName, acPreview, "", "[PatientID]=[Forms]![frmAppointments]![PatientID]"

so in fact i didn't really need sql. thanks for taking the time to look at my post anyway tho, it was greatly appreciated.

bajan elf