Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ramdas
Posting Yak Master
181 Posts |
 Posted - 2003-02-21 : 22:24:24
|
| Hi folks,Our Prodn DBA insists we have a primary key on every table. I had a table with a unique clustered index but I had to change it to a primary key constraint. What is the differnece between the 2 scenarios. The unique clustered index gets picked up by the optimizer but what about PK constraints, what does it buy apart form enforcing uniqueness which can be done by unique clustered index.I am not able to figure out the difference here. Thanks for your help in advance.ByeRamdas NarayananSQL Server DBA |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-21 : 23:42:43
|
quote:
Our Prodn DBA insists we have a primary key on every table.
GOOD FOR THEM!!!Consider yourself lucky that your DBA cares enough about data integrity to insist. From Books Online, under "Unique Constraints":quote:
You can use UNIQUE constraints to ensure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of: -A column, or combination of columns, that is not the primary key. Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.-A column that allows null values. UNIQUE constraints can be defined on columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values.A UNIQUE constraint can also be referenced by a FOREIGN KEY constraint.
Those differences may be more subtle than you will actually experience, but nevertheless, a table without a primary key is not really a table. If you're not into the habit of having a primary key on your tables, get into that habit immediately. |
 |
|
|
sherrer
64 Posts |
Posted - 2003-02-22 : 08:44:49
|
| Also...By defining a primary key, you have explitly defined which column(s) to use as foreign keys in relational tables. If you have a table with two unique columns, then you have ensured uniqueness in your table, but which column are you going to define the relationship between tables when adding new relational tables?In your question you have asked what performance differences there are. In the example you gave, I don't think there are any, but I don't think you want to define a clusterd index on every table you create to get you best read performance. This will totally destroy the performance of inserting and deleting if you have to do such to anything but the last few records in the table.Hope this helps.Edited by - sherrer on 02/22/2003 08:45:55 |
|
|
|
ramdas
Posting Yak Master
181 Posts |
Posted - 2003-02-22 : 09:59:21
|
| Hi folks,Thanks to both of you for your insights, understood the importance of PK's.ByeRamdasRamdas NarayananSQL Server DBA |
|
|
|
|
|
|
|
|
Primary Key Constraint