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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Primary Key v. Unique Index

Author  Topic 

Kristen
Test

22859 Posts

Posted - 2004-12-09 : 06:55:10
Is there a benefit of a Primary Key over a Unique Index?

The fact that I have to DROP and CREATE a PK Constraint in order to change the FILLFACTOR seems to be a distinct disadvantage over being able to use DROP EXISTING on a Clustered Unique Index

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-09 : 07:00:37
A PK identifies the row rather than just a constraint.
It has a special meaning to sql server and values in it shouldn't be updated.

You can exist quite happily with all your PK's just constrained by a unique index but some featuers which rely on a PK won't work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-12-09 : 07:47:59
Like Replication? Anything else major? In particular anything which effects how the optimiser will "judge" my queries?

Kristen
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-09 : 08:00:59
PK does not allow nulls where unique constraint does.

Not a bad, quick, read over here:

http://dbforums.com/t410865.html
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-09 : 08:45:49
The optimiser will just use the index so won't be affected by the pk attribute.

A lot of audit trail and similar apps will use the PK.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-12-09 : 13:37:04
Cheers chaps, I'm going off the idea.

I just need a simple way to re-create indexes without major disruption to the site. I supose I should wait for the next major rollout, but there are some that are plain wrong that need fixing sooner rather than later.

The dbForums link is a good'un, thanks ehorn.

Thinking on a bit - can I just change the FILLFACTOR? I want to change it from 90% to default (i.e. 100% but there is a subtle difference between "default" and "100% - maybe I'd be better off with 100% than 90% and can then change to Default later)

Kristen
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-10 : 00:44:54
My $.02

Without a Primary Key defined, you've broken the relational model. My opinion, you absolutely need a primary key.

If your biggest downside is that its difficult to change FILLFACTOR, how often are you changing this value?

HTH

=================================================================
Hear the sledges with the bells - Silver bells!
What a world of merriment their melody foretells!
How they tinkle, tinkle, tinkle,
In the icy air of night!
While the stars that oversprinkle
All the heavens, seem to twinkle
With a crystalline delight;
Keeping time, time, time,
In a sort of Runic rhyme,
To the tintinnabulation that so musically wells
From the bells, bells, bells, bells,
Bells, bells, bells
From the jingling and the tinkling of the bells.

Happy Holidays!
Go to Top of Page

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2004-12-10 : 06:11:01
Table can have multiple unique constraints ..but only one PK.!!

------------------------
I think, therefore I am
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-12-10 : 16:41:16
"If your biggest downside is that its difficult to change FILLFACTOR, how often are you changing this value?"

Absolutely agree. However, right now I've found that the FILLFACTOR is broken, and its seriously mucking up the DEFRAGs each day. And we need to scheduled downtime to fix it - but that just is not possible until after the Christmas rush - but because of the Christmas rush we absolutely need to fix the problem :-(

I was kind of absorbed by that circular argument!

For the one table where we found that we could manually recreate the index (before all the user session timed out!) we haven't had to defrag it since - i.e. the Scan Density has been above 90% since. So I'm pretty sure it will make a big difference - I just wish to hell that I'd pursuaded the client to invest in some decent scripts for maintenance rather than letting the Maintenance Plan Wizard change the indexes to 90% fill factor ...

Kristen
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-12-10 : 17:19:07
quote:
Originally posted by nr
It has a special meaning to sql server and values in it shouldn't be updated.



Can you expand on that please Nigel? I know its an old battle ground for you and I, but this intrigues me.

The model I am working on has a core entity which has a natural key (2 columns). The problem is it can change and is fully audited. At the moment I have added a non changing artifical key for the auditing framework, but am having a hard time in convincing myself to use that key as the FK in the subsequent RI tables. The thing is, only one column in the key will change. Cascading is propogated to certain entities only to ensure temporal correctness (not being able to change an event after it has taken place).

Any ideas welcome.

DavidM

"Always pre-heat the oven"
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-10 : 17:40:41
quote:
right now I've found that the FILLFACTOR is broken, and its seriously mucking up the DEFRAGs
What about doing a full DBCC DBREINDEX and specify the new fillfactor?
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-10 : 19:37:14
>>> I just wish to hell that I'd pursuaded the client to invest in some decent scripts for maintenance rather than letting the Maintenance Plan Wizard change the indexes to 90% fill factor
<<<

I don't know of anyone who's happy with the Maintenance Plan Wizard. Sorry to hear that you've been wracked by it, also.

HTH

=================================================================

Happy Holidays!
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-10 : 20:55:19
I thought I had included updating PKs in
http://www.nigelrivett.net/BadThings.html
maint plans are there though.

>> The model I am working on has a core entity which has a natural key (2 columns). The problem is it can change and is fully audited.

Is it?
It means that the pk doesn't identify the row. If it is audited the row must be identified by the old PK and also reference the new PK - in the audit trail the new pk is an attribute of the old PK.
This can't be done by triggers as a trigger will have no way of knowing that a pk has changed.
What happens if another row is changed in the same transaction to have a new pk the same as the old pk that has just changed.
You can say that the business rules prohibit this but it's not prevented by the database structure if you allow updateable PKs - only by the application.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-12-10 : 21:41:27
"What about doing a full DBCC DBREINDEX and specify the new fillfactor?"

I want to set it to default, but if you specify ZERO to DBREINDEX it leaves the current fill-factor as-is, rather that setting it to "default" as CREATE INDEX ... WITH FILLFACTOR 0 would do :-(

"I don't know of anyone who's happy with the Maintenance Plan Wizard. Sorry to hear that you've been wracked by it, also"

Cheers! We've got our own set of maintenance routines, but we charge for them - it took us a lot of work to develop. We now require this for clients who have their own servers (if they use our shared hosting we chuck it in the mix); but until a little while ago we were lenient on this :-(

Kristen
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-11 : 02:54:49
Kristen,

Just so that you get both sides of the argument...

1) There is nothing in the relational model that says that a primary key cannot change. The whole point of a primary key is to have a means of uniquely identifying a row in a table. Changing one unique value for another unique value is perfectly legal and acceptable. There are details to remember to deal with. But changing keys is why foreign keys have a CASCADE ON UPDATE option.

2) Triggers absolutely can detect when a primary key has changed:

SELECT t.MyKey
FROM MyTable t
join Inserted i
on i.MyKey = t.MyKey
WHERE not exists (select MyKey from Deleted)

3) While it would be possible to have a second update change rowB's PK to have rowA's original PK value, the trigger would have already fired.

4) IN RE >>> We've got our own set of maintenance routines, but we charge for them <<<
Crack the whip on these clients. They are paying good money for your advice and expertise. They NEED a different maintenance plan. Maybethey don't need yours, but you'd be doing them a disservice to allow them to keep the one they have.

5) You're going to hear many comments and opinions on forums like this. The forums are a valuable tool. But not everything you hear is gospel.

HTH

=================================================================

Happy Holidays!
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-11 : 03:44:41
quote:
Originally posted by byrmol

Can you expand on that please Nigel? I know its an old battle ground for you and I, but this intrigues me.

The model I am working on has a core entity which has a natural key (2 columns). The problem is it can change and is fully audited. At the moment I have added a non changing artifical key for the auditing framework, but am having a hard time in convincing myself to use that key as the FK in the subsequent RI tables. The thing is, only one column in the key will change. Cascading is propogated to certain entities only to ensure temporal correctness (not being able to change an event after it has taken place).

Any ideas welcome.

DavidM

I have run into the same issues, and my conclusion was that the "natural" key that I first used as pk, was not stable enough. Stability is one criteria of pk's.
In those cases I use artificial keys as pk ( and use that for RI ), the "natuaral" key is degraded to "unique constraint" with not nulll columns of course.
PK not stable -> not good pk.

rockmoose
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-11 : 04:44:17
>> 2) Triggers absolutely can detect when a primary key has changed:

Yes but they can't find the before and after values of the row.
It is a logical delete and insert soI feel should be coded that way.

>> 1) There is nothing in the relational model that says that a primary key cannot change.
No, but if a PK uniquely identifies a row then changing it means that row no longer exists and you have a new row i.e. a delete and insert rather than an update. Just because something is allowed doesn't mean it's a good idea (cursors?).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-11 : 13:18:58
quote:
Originally posted by nr

>> 2) Triggers absolutely can detect when a primary key has changed:

Yes but they can't find the before and after values of the row.
It is a logical delete and insert soI feel should be coded that way.

>> 1) There is nothing in the relational model that says that a primary key cannot change.
No, but if a PK uniquely identifies a row then changing it means that row no longer exists and you have a new row i.e. a delete and insert rather than an update. Just because something is allowed doesn't mean it's a good idea (cursors?).




2) It is true that you can't guarantee matching the old and new PK in a multi-row update. You could with single row update and under certain conditions in a multi-row update.

1) I hear what you're saying; just don't agree. If I change the number on my house so that I have a new address, does this mean that I really have a new house? Heck, no. It's the same house. It just has a new means of differentiating it from the other houses.

At this point we're just splitting hairs.

Your opinion is that primary keys should not be changed. I've heard your defense of this premise. I remain unconvinced. I am convinced, however, you will find a way to live without my approval.

HTH

=================================================================

Happy Holidays!
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-11 : 13:57:34
>> If I change the number on my house so that I have a new address, does this mean that I really have a new house?

If the number identifies the house then yes. You would only have the same house if there is some other means of identifying differences in houses other than the number which is more important. Of course you don't have to make the connection between a house identifier and a PK in a database but if you do then a number not identifying the house means that it shouldbn't be the pk.

>> I am convinced, however, you will find a way to live without my approval.
Already found.
Your point of view is valid, I just feel that it can cause problems that could easily be avoided. If you feel that the problems will never arise or are prepared to deal with them then that's fine.
I feel I'm probably in the minority here - buts that's never caused me to lose sleep.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-12-11 : 16:27:51
Thanks rockmoose, Bustaz and Nigel,

Can anyone define "stable" in a practical sense?

Lets say on average 15% of the rows will undergo a key update. Only one column in the composite key is ever updated. 75% of the updating rows will be updated only once. Cascading is restricted to 3 tables, while the other RI related tables do not cascade ie: If any data makes it into these no cascading tables, any attempt at a key update will fail.. that's part of the business process it self..

So.. by using an artificial key, I can eliminate any cascading to those three table, plus multi-row update auditing is easier. But I do not want to lose the natural key to the non cascading tables.

That makes 4 candidate keys for this table... I have a unique constraint on all of them at the moment, but am yet to decide on the "Primary Key"

DavidM

"Always pre-heat the oven"
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-11 : 18:19:30
Nigel,

>>>If I change the number on my house so that I have a new address, does this mean that I really have a new house?

If the number identifies the house then yes.
<<<

If I have a new house, then why does my kitchen still need a fresh coat of paint?

>>>I feel I'm probably in the minority here - buts that's never caused me to lose sleep.
<<<

Well put. I just can't argue with that!

HTH

=================================================================

Happy Holidays!
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -