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 |
|
scottpt
Posting Yak Master
186 Posts |
 Posted - 2004-02-27 : 11:29:42
|
| From a developer"I/we don't use Identitiy columns for performance purposes but rather for referential integrity purposes. You're going to have a hard time convincing me not to use them for referential integrity because if I don't use them in this way I have to rely completely on my business tier logic to guard against violations."Beside saying, "You should be fired because you can't code your way out of a paper bag."How would you reply? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-27 : 11:46:49
|
| That I don't understand the statement - how do identities help here?==========================================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. |
 |
|
|
scottpt
Posting Yak Master
186 Posts |
Posted - 2004-02-27 : 11:52:08
|
| Here is what they are doing Insert into TableA, indent is PK, capture @@IDENTITY, insert into TableB and TableC using the @@identity as the FK, and TableB and TableC having an indentity as the PK. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-27 : 12:49:13
|
| DOn't see how that helps - say you want another rec in TableB linked to the same rec in TableA - you have to get the PK from TableA whether it is an identity or not.The referential integrity should still be assured by a foreign key in the database - this just uses the PK from TableA, doesn't matter what it is.==========================================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. |
|
|
|
scottpt
Posting Yak Master
186 Posts |
Posted - 2004-02-27 : 13:56:20
|
| Let me add this 52 tables, all Ident PK. 45 of those tables have Unique indexs on 1 or 2 columns. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-02-27 : 17:18:00
|
| If they already know what makes a record unique within a table, then they already have candidate keys available in that table. To add another unique one doesn't really help, especially if it's just a meaningless autonumber.I would suggest getting their logical design and improving upon it yourself, and then showing them how and why you made the improvements. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-02-27 : 17:49:08
|
| I sometimes strongly suspect that about 40% of the developers and DBA's out there do not realize that more than 1 column can participate in a PK. It also often seems that about 75% don't realize that you can use more than 1 column in foreign key relationships.- Jeff |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-28 : 13:53:08
|
Well, I got an email recently from someone who thought you couldn't create an INDEX on a column because "it wasn't a number".Personally Jeff, I'd up the percentages to 95% and 99%, respectively. Nor would I personally "sometimes" "suspect" it, I ALWAYS KNOW it.  |
|
|
|
|
|
|
|
|
IDENTITIES used int referential integrity