| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2004-08-12 : 14:10:04
|
| I have a table with a clustered index on some fields and an identity-field which has it's own normal index. Is there really a point to setting a primary key constraint on the identity-field? Why/why not?EDIT: The identity-field will never be a foreign key--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-12 : 14:49:15
|
| Who said this:If you don't have a primary key defined on the table, then you don't have a table. It was one of those database fundamentals guys.Tara |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-12 : 14:51:46
|
| I know it's good practice, but seriously..what's the use? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-12 : 14:54:05
|
| A primary key protects your data from duplicates. It's a constraint.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-12 : 14:56:43
|
| Data integrity. Intellectual property. The data integrity should be enforced and the meaning readily derived from the structure in the absence of the original designer. In other words, if you get ran over by a train and some moron developer rights an application against your structure, the structure should confine him enough to make sure he can't easily screw things up without making it obvious he's a complete idiot they need to fire.:)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-12 : 15:01:07
|
| I know what they are and how they work, but that's why I'm asking. I have a table that never ever in a million years will have any rows that will be referenced in any other table with a foreign key constraint. I do have an identity-field that allready protects my data from duplicates, is there any reason I should add a coinstraint just because "it's good practice"? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-12 : 15:03:58
|
An identity column does NOT prevent duplicates. Take a look:CREATE TABLE #Temp( Column1 int IDENTITY(1, 1) NOT NULL, Column2 int)INSERT INTO #Temp (Column2)SELECT 2UNION ALLSELECT 1SELECT *FROM #TempSET IDENTITY_INSERT #Temp ONINSERT INTO #Temp (Column1, Column2)SELECT 1, 4SET IDENTITY_INSERT #Temp OFFSELECT *FROM #TempDROP TABLE #Temp Tara |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-12 : 15:34:59
|
Hm, definetly very interesting...even though I would consider SET IDENTITY_INSERT #Temp ON to be "cheating" I see your point. I say "cheating" because it would not be something that happened unintentionally. I would think that this was something that would be done manually and only in rare cases. It would be just as easy to just drop the PK constraint as to do this... maybe I'm just beeing difficult here but that's how I see it in my narrow unexperienced mind  Nevertheless; you have me convinced! |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
Posted - 2004-08-12 : 15:57:20
|
| Many libraries/applications can use formally declared constraints to make code easier to use/write. For example, many ADO tools will do a lot of the work for you if you have a PK defined, but can't do it for you without one.Besides that, if you formally state what you intend (by declaring the PK), it removes any doubt as to what you meant. That alone is worth a bundle to me when I'm trying to finger out what on earth somebody had in mind when they created some mess!-PatP |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-12 : 16:06:48
|
| I will probably continue to declare PK's like have have always done but I'm a curious guy and I'd like to know what's going on and that's why I ask...and tara; I hope you weren't offended or anything by me saying the "cheating"-stuff, that was not my intention at all.No another question a little on the side: is it possible to store comment's like you do in sprocs and UDF's in table declarations? So if I script the table it will be visible with the rest of the table-declaration? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-12 : 16:12:20
|
| Nope wasn't offended. And yes it is cheating. But you can't assume that someone won't do it. Constraints are put in place to protect the data. You can put descriptions for the columns and a description for the table and these will be scripted when you run the generate SQL script wizard. You can't put a comment block though.Tara |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-12 : 16:16:36
|
| Excellent...nice to know if I "pull a stunt" like not having a primary key...allthough using the description would probably be a better idea. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-13 : 07:35:10
|
| I've come across a fair amount of bits & pieces (tools and the like) that don't work if there is no Primary Key (even though there may be a unique index or somesuch). Therefore if you have a natural primary key it is prudent to declare it as such.Doesn't replication require a PK?"[i]never ever in a million years will have any rows that will be referenced in any other table with a foreign key constraint[i]"Bet'cha need one by the middle of next week Kristen |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-13 : 08:06:34
|
quote:
Originally posted by LumbagoI do have an identity-field that allready protects my data from duplicates
But it wont though, not really. It will protect your rows from duplicates, not your data.-------Moo. :) |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-13 : 19:29:23
|
| Curious, what do You use the identity field for ???You should be concerned with putting at least one unique index on the table, and choose one of the unique indexes as primary key.The unique indexes should have a business meaning, i.e. constrain the data so that duplicates cannot enter the table.An identity that is unique will probably not accomplish that purpose if it is the only unique contstraint on the table./rockmoose |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-14 : 04:33:39
|
| Kristen: I think I'm gonna have to take you up on that bet I just hope you realise that I'm not saying this as a general rule or anything but with the table design I have for this one table I just happen to find the PK obsolete. mr mist & rockmoose: I have had this discussion a million times and so has probably everybody else here, if PK's should have a business meaning or not. I have been a professional webdeveloper for 5 years now and been designing databases (mostly pathetic small-scale ones but one large enterprise system on which I'm currently also working) and I have used identity-fields as PK's on all of them. The only tables I don't use identity-fields on as PK are junction-tables for obvious reasons. The part I like about this is that you only have to reference one column to identify a row in the table, and when doing joins this is a considerable advantage. I might have 3 or 4 tables in the database that have a single-column natural key, in the rest of them (about 40) it takes usually 3-6 columns to provide a natural unique key. Doing joins on this would make my life miserable, especially when some of my selects require up to 5 or 6 table deep joins. And I have to say this: I'm still only a "rookie" in comparison to most of the people here but I have *never ever* had any problems whatsoever doing my designs like this, and it would take some serious retorical skills to make me convert. In my previous job they used all natural keys on a HUGE system and I just about went nuts trying to write joins on the damn thing...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-14 : 05:52:29
|
| Mr Lumbago,I am not trying to convince you to only use natural keys on your tables.What I was saying is that all th tables must have at least one unique index that is "natural", "conveys business meaning" etc.This uc can be multiple columns etc.. and its purpose is to protect the integrity of the data.Then as You say, you can have nice identities that will join up the tables in the database, with nice one column references.But Your data will be protected from duplicates.In this particular discussion and table:What was the logic behind designing the table with an identity field in the first place ?/rockmoose |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-14 : 07:46:09
|
| What I was asking was not really why or why not I'm using identity-field. Let me put my question in to an "extreme" setting just for the sake of discussion: You have a website with articles only and a database with *one single* table formed like this: CREATE TABLE articles (ArtID int IDENTITY(1, 1), Heading varchar(200), Article varchar(8000)) where ArtiID has a clustered index. Are there any good reasons why one should make a pk/unique constraint on ArtID in this case when the logic behind IDENTITY keeps it unique? In norwegian we have a saying which loosely translates as "buttering the gravy" and I have feeling that a unique constraint on an identity-field would be just that. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-14 : 10:19:29
|
| Definitely put a UC or PK on the IDENTITY Field. Database 101 fundamentals.Cream:CREATE TABLE articles(ArtID int IDENTITY(1,1), Heading varchar(200), Article varchar(8000),CONSTRAINT pk_ArtId PRIMARY KEY CLUSTERED(ArtId))CREATE TABLE articles(ArtID int IDENTITY(1,1), Heading varchar(200), Article varchar(8000),CONSTRAINT uc_ArtId UNIQUE CLUSTERED(ArtId))Low Fat Milk:CREATE TABLE articles(ArtID int IDENTITY(1,1), Heading varchar(200), Article varchar(8000))CREATE CLUSTERED INDEX ix_ArtId ON Articles(ArtId)Buttering the Gravy or fixing the soup:CREATE TABLE articles(ArtID int IDENTITY(1,1), Heading varchar(200), Article varchar(8000))CREATE CLUSTERED INDEX ix_ArtId ON Articles(ArtId)ALTER TABLE articles ADD CONSTRAINT pk_ArtId PRIMARY KEY(ArtId)/rockmoose |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-14 : 14:02:58
|
quote:
Originally posted by Lumbago
What I was asking was not really why or why not I'm using identity-field. Let me put my question in to an "extreme" setting just for the sake of discussion: You have a website with articles only and a database with *one single* table formed like this: CREATE TABLE articles (ArtID int IDENTITY(1, 1), Heading varchar(200), Article varchar(8000)) where ArtiID has a clustered index. Are there any good reasons why one should make a pk/unique constraint on ArtID in this case when the logic behind IDENTITY keeps it unique? In norwegian we have a saying which loosely translates as "buttering the gravy" and I have feeling that a unique constraint on an identity-field would be just that.
Well, I don't really consider a simple table a database, but technically I guess it is. You don't track anything like date of publication, or author, etc. If you were to create a more advanced database design, you would be better able to see the advantages of using a PK. -ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-15 : 07:30:33
|
| I just find IDENTITY columns hard to work with.Say I ahve a table of PRODUCTS and PRICES. The PRICES table has a PK of ProductCode + CatalogueCode (where the Catalgoue represents, say, Spring 2004, Summer 2004, Summer Sale 2004 and so on.When I bump into some raw data like ProductCode "ABC123" and CatalogueCode "SUM2004" I know exactly what it is (assuming I am familiar with the product ABC123 of course). When I bump into ID=1234567 I ahve no idea what it is.The JOINS are a bit of a bind, but I have a little SProc I wrote that, for a given table, lists some syntax for a SELECT of all columns, and syntax for each JOIN given the FKs that exist, and a bunch of other stuff. Then I just cut&paste the bits that I need.But IDs would work just as well, and in some cases better. Its horses for courses I reckon.Kristen |
|
|
|
Next Page
|
To (primary) key or not to (primary) key