Primary Key/Unique Key (or) Unique Clustered Index

Question

Primary Key/Unique Key (or) Unique Clustered Index

pradyumna.bhat

Valued Member

Points: 60
More actions
March 19, 2008 at 1:57 am

#183513

Hello Group,
found you online.
have a question to ask you.
why a table is permitted have only one primary key or unique key
or
why only one unique cluster index is allowed on a table
if you can able to answer my query, i'll be grateful to you
you can guide me to resource where i can get answers to such Q?'s
sorry for taking undue advantage...
apologies
regards
pradyumna

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi.

A primary key uniquely identifies a row within a table and as such you are only allowed one primary key. In relational theory there can only be one key that uniquely identifies a row.

Unique constraints enforce uniqueness within a column or set of columns (if you're using multiple columns for a constraint). From a physical point of view unique constraints are the same as primary key constraints except that you can define mulitple unique constraints on a table and unique constraints allow NULL whereas primary keys don't.

You can only have one clustered index per table because a clustered index orders the data in the table based on the definition of that index. Its physically impossible to order data on anything other than one set of columns.

In terms of online resources I'd suggest you look for something on relational database design. Learning the theory behind relational databases and their design can help understand how it translates to physical models.

Hope that helps.

Karl
source control for SQL Server

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 2

A clustered index determines the order in which data is stored in the database files on disk. You cannot store the same data in the same file in two different orders. You can read more about it on http://msdn2.microsoft.com/en-us/library/ms188783.aspx

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 3

Hey pradyumna,

why a table is permitted have only one primary key or unique key
or
why only one unique cluster index is allowed on a table

Answer :

1st : A Table can have more than one unique constraints .

2nd : Clustered index is maximum one per table because of clustered index orders the data in the table based on the definition of that table.

Hope you it will work for you.

Cheers!

Sandy.

--

pradyumna.bhat Valued Member Points: 60 More actions · Answer 4

Hello Friends,

thanks for all the explanations...

appreciating you concern and support

thanks

pradi