InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 16:13:24 GMT20http://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspxI think Hugo said it best many pages ago, but it bears repeating. [b]For every table in which you use a surrogate key, you should also have at least one additional unique constraint.[/b] Without it, users can create row upon row of duplicate data except for the surrogate key (I've seen it too many times). This is probably the most common mistake made by new database developers. Using a surrogate key does not obviate the need for another key that enforces uniqueness comprised of user viewable values since we [b]never[/b] show the surrogate to the user.Sat, 08 Jan 2011 23:43:05 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]JJ B (10/25/2010)[/b][hr]Hugo wrote:[quote]And consider what so many people call a junction table - a table that represents the many-to-many relationship between two tables. I will usually model these as just the two foreign keys to the two tables in the relationship, and a primary key over the combination of those columns. Again, a PK with business meaning. And again, I see no advantages to be gained by adding a surrogate, only a few disadvantages.[/quote]I can think of a giant advantage--at least to my programs. Suppose I have a such a junction table (I call it a link table) with a primary key on the two FKs as you describe. Now suppose the user in the front-end tries to change one of the values in an [i]existing[/i] row and unwittingly tries to change one of the FK values to a value that would result in a duplicate row. [/quote]Of course, you wouldn't think about creating such a table without also having a unique constraint across the two foreign keys right? As far as I can tell, assuming you use a proper design, your problem is no different whether you have a surrogate key or not.If you have a junction table with nothing but a set of FK values (i.e. no other columns), then it represents a child list to the parent object. Your code to save the user's data would look something like:Find a rows with the values entered by the user (notice I don't care if there is a duplicate)If you find that row, then update it and delete the old rowIf you do not find that row, the insert a new one and delete the old one.In SQL 2008, I believe you could do this entire operation in a MERGE statement.Sat, 08 Jan 2011 23:10:30 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]David Portas (11/5/2010)[/b][hr]I highly recommend you avoid using rhetoric like "twelfth normal form". It might be (mis)interpreted as a lack of respect for data integrity and sound design practices. Data integrity is one of the biggest challenges in our industry and lack of it is one of our biggest problems. It is not to be taken lightly. Obviously normalization helps make sure you achieve all of your stated a), b) and c).[/quote]point taken. I will revise my post; I was just having a 'moment', it was early in the morning when I posted that.... reading on a new day, I can see that I could have expressed my point better.I was only intending to highlight that this discussion is supposed to be about surrogate keys vs natural keys, not normalization; while they're related, they're not the same. As I think you pointed out, if not in those exact words.Fri, 05 Nov 2010 17:02:44 GMTRyan C. Pricehttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Ryan C. Price (11/4/2010)[/b][hr]I don't understand why any discussion about surrogate vs natural keys always degenerates into an idealogical 'flame-war'.[/quote]Part of the problem is a widespread lack of understanding about relational databases in general and keys in particular. Some people unwisely assume that a table can / should have only one key, therefore they see natural keys as a "threat" because they think they are being forced to choose between either a surrogate or a natural key. That's nonsense of course. Surrogates and natural keys achieve different things and a table should have as many keys as needed to model the busines domain accurately. Surrogate keys alone can't achieve that because they won't stop business data being duplicated in a table. That doesn't mean DON'T use surrogates, it just means DO use natural keys.[quote]To me, the *prime* obligation of the database designer is not to design databases to 'twelfth normal form'*, but to design databases that a) accurately model the end users' data requirements b) help our developers to be effective in producing applications that their end-users find user friendly, and c) help ourselves and our successors in database maintenance, by making our designs sensible, reliable, simple but well normalized, as 'future-proof' as we can practically make them and as 'self documenting' as possible.[/quote]I highly recommend you avoid using rhetoric like "twelfth normal form". It might be (mis)interpreted as a lack of respect for data integrity and sound design practices. Data integrity is one of the biggest challenges in our industry and lack of it is one of our biggest problems. It is not to be taken lightly. Obviously normalization helps make sure you achieve all of your stated a), b) and c).Fri, 05 Nov 2010 15:14:59 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspxI don't understand why any discussion about surrogate vs natural keys always degenerates into an idealogical 'flame-war'. At least this particular thread has remained fairly tame.So a further 2c...What we're in danger of forgetting in these 'enthusiatic' discussions, is that databases don't exist for their own benefit, and that the people who pay us (directly or indirectly) to create said databases (i.e. end users) don't care about database theory (no matter how much we try and convince them that they should).Then, in between the end users and our databases is a 'abstraction layer' known as the 'application software developer', these good people have a moral obligation to understand basic database theory, but also have the task of converting 'Data' into 'Information' for their end users.To me, the *prime* obligation of the database designer is to design databases that a) accurately model the end users' data requirements b) help our developers to be effective in producing applications that their end-users find user friendly, and c) help ourselves and our successors in database maintenance, by making our designs sensible, reliable, simple but well normalized, as 'future-proof' as we can practically make them and as 'self documenting' as possible.If we forget these, we are failing in the duty of care we have for our clients and our co-workers.To this end, as someone who operates regularly at all 3 levels (user, developer and db designer), and usually working on someone else's long neglected application, my view is that it doesn't matter so much which methodology (surrogate vs natural) you choose to use in your database design, as long as you normalize your database structure properly, and apply your preferred methodology consistently. Do that, and I can work with what you give me.If your choice of key (be it natural or surrogate) for any given table makes it difficult for your software development team to deliver good information to their end-user, then you have failed. For example, if your product development team need to use an application generation tool that works better with surrogate keys than with natural keys, then to me it would make sense to implement surrogate keys. To not do so just because it would 'corrupt' your database structure would seem to me to be counter-productive.I've seen databases that work using both techniques, and I can't say that I have a really good argument that one is better than the other. Although I *do* have a previously stated preference for surrogates, with a recent experience of migrating a client's data from an old accounting package to a new ERP package highlighting why this is - I would have saved an awful lot of time if both systems used surrogate keys instead of slightly different sizes of 'natural' key, that my client decided they wanted to keep. But that's a different story./Ryan[EDIT] Just in case someone misses this in my points: My main concern here is about 'practical implementation' - a theoretical data model should always have relationships based on 'natural' attributes of the relevant entities.[EDIT] Took out some unneeded, distracting 'rhetoric' (Thanks David)P.S. my 'rhetoric' was trying (badly) to point out that many people fall into the trap of equating 'surrogate vs natural keys' with 'normalization', as if they are the same discussion. They are not, IMHO; quality of normalization has little to do with whether surrogate or natural keys are used. I've seen both bad and good designs from both 'camps'. Since I can't figure out where to put this point within my original text, I've added it as a postscript.Thu, 04 Nov 2010 15:07:29 GMTRyan C. Pricehttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]David Portas (11/4/2010)[/b][hr][quote]As already stated, in the relational model a key is only required to be unique and irreducible. Stability is never a requirement[/quote]Exactly so. Too many developers are so used to the convenience of using a PK as a rowid to allow single-row updates that they conflate the terms, and erroneously believe a key can't be updated and remain a key.You're also spot-on with the statement that normalization relates to relationships, not tables. A relationship (or the somewhat more fuzzy term 'entity') can be captured within one table, or in multiple tables...but in all cases, normalization defines a rule for the that relationship is expressed. It's not something that's "specific to one table at a time".Thu, 04 Nov 2010 09:35:22 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Robert Frasca (11/4/2010)[/b][hr][quote]What's seems to be confusing a number of you is that you keep referring to entities as "tables". A data entity is NOT a table.[/quote]However, in the relational database model, normalization applies specifically to [i][b]relations[/b][/i], not "entities" (or tables for that matter). The idea of distinguishing between entities and relationships is an ER modelling concept that came along well after the relational model and normalization were invented. Many people find ER modelling useful but not everyone uses it. So I think it's best to avoid ER modelling terms if we are going to discuss relational database normalization. There is too much ambiguity in the term "entity" unless you make it clear that you are talking specifically and only about ER modelling concepts rather than just relational databases.[quote][b]Robert Frasca (11/4/2010)[/b][hr][quote]there are other ways to enforce the uniqueness of the natural key. It doesn't need to be the primary key. By the way, a natural key that can change isn't a key.[/quote]If uniqueness is enforced declaratively then by definition that is a key. SQL provides at least two ways to implement keys, using either a PRIMARY KEY constraint or a UNIQUE constraint (on non-nullable columns of course). They both achieve the same thing.As already stated, in the relational model a key is only required to be unique and irreducible. Stability is never a requirement because in principle a tuple is identifiable only by attributes and it is arbitrary and pointless to say that one tuple "cannot" be replaced by a different one in the same relation. Therefore keys are no more or less updateable than any other attribute. SQL is not truly relational of course but in this respect the SQL standard fortunately follows the relational model and allows keys to be updated.Thu, 04 Nov 2010 09:24:12 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]David Portas (11/4/2010)[/b][hr][quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr]Still trying to see what desirable qualities that natural keys have. Could you point out some examples?[/quote]A natural key enforces a data integrity rule that stops some meaningful data being duplciated. A surrogate key does not. I already discussed the example of a unique login name where it's desirable to have a key that is unique but changing. Not really sure what other example you need.[/quote]David, there are other ways to enforce the uniqueness of the natural key. It doesn't need to be the primary key. By the way, a natural key that can change isn't a key. It's just another attribute. I didn't see your example but a login name isn't a key. It's an attribute of a user account. In Windows, for example, there is a surrogate key in active directory called a SID (Security ID). An excellent example of the power and flexibility of a surrogate key.Thu, 04 Nov 2010 09:08:46 GMTRobert Frascahttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr][quote][b]Michael Ebaya (11/4/2010)[/b][hr][quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr]I am really curious to know why it's a good idea to not enforce the immutability of the key![/quote]This has already been covered in excruciating detail. First of all, a surrogate reduces the likelihood of an update, but it doesn't eliminate it entirely. More importantly, immutability is a desirable quality of key, but its not a requirement of a key. There are other desirable qualities that natural keys have (in some cases), and surrogates do not (again, in some cases). Neither is best in any and all cases.[quote]No, you are an anonymous poster who claims that he knows a great deal about databases, and not only that make claims about normal forms that aren't true.[/quote]Such as what? Your little myth that "no normal form affects more than one table" has already been roundly debunked.[/quote]Actually, it hasn't. As has been pointed out to you a number of times, the formal definition of the normal forms relates to the tables themselves. You seem to be confused with how the normal forms work. Note that I'm not saying that what you say is a bad idea - if you have two tables that do the same thing, then that is quite silly and you should of course rationalize the table design.[/quote]What's seems to be confusing a number of you is that you keep referring to entities as "tables". A data entity is NOT a table. For example: An Invoice entity might look something like this:Invoice NumberInvoice TypeAccount NumberInvoice DateFiscal PeriodBilling cycle numberAmountWhile it looks like a "table" this is an abstraction of a business object, i.e. an entity. It can be normalized into a number of other entities, each with their own set off attributes including:InvoiceInvoiceTypeAccountFiscalDateBillingCycleWhen I drill into each of these entities I may normalize into even more entities in order to create relationships to other areas in the data model or I may choose to make each of these a table when I ultimately create the physical model. In other words, many of the attributes of my invoice entity might actually turn out to be foreign keys to other entities.Getting back to the original topic for a moment, if I use surrogate keys as the primary keys of these entities I am not bound to existing naming or numbering conventions. For example, my "natural" account number might be numeric; however, I may acquire another business that uses alphanumeric account numbers. By using surrogate keys I can merge the new business into my existing account structure by simply changing the datatype of my AccountNumber attribute without having to make changes to my relational infrastructure.Thu, 04 Nov 2010 08:57:42 GMTRobert Frascahttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr]Still trying to see what desirable qualities that natural keys have. Could you point out some examples?[/quote]A natural key enforces a data integrity rule that stops some meaningful data being duplciated. A surrogate key does not. I already discussed the example of a unique login name where it's desirable to have a key that is unique but changing. Not really sure what other example you need.Thu, 04 Nov 2010 08:48:16 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Michael Ebaya (11/4/2010)[/b][hr][quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr][quote]the formal definition of the normal forms relates to the tables themselves. You seem to be confused with how the normal forms work. Note that I'm not saying that what you say is a bad idea - if you have two tables that do the same thing, then that is quite silly and you should of course rationalize the table design.[/quote]Good god, did they stop teaching data modelling entirely in college? Or have people just stopped going in the first place? [/quote]Which particular normal form do you believe helps you rationalize particular tables? I'm seriously interested in which one you refer to. Please, just either answer:a. 1st normal form,b. 2nd normal form,c. 3rd normal formI won't go into further normal forms, unless you want to, that is.Also, which basic primer on normalization would you recommend? I'm curious which one supports your idea.Thu, 04 Nov 2010 08:38:04 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Michael Ebaya (11/4/2010)[/b][hr][quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr]I am really curious to know why it's a good idea to not enforce the immutability of the key![/quote]This has already been covered in excruciating detail. First of all, a surrogate reduces the likelihood of an update, but it doesn't eliminate it entirely. More importantly, immutability is a desirable quality of key, but its not a requirement of a key. There are other desirable qualities that natural keys have (in some cases), and surrogates do not (again, in some cases). Neither is best in any and all cases.[/quote]Still trying to see what desirable qualities that natural keys have. Could you point out some examples?Thu, 04 Nov 2010 08:35:53 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr][quote]the formal definition of the normal forms relates to the tables themselves. You seem to be confused with how the normal forms work. Note that I'm not saying that what you say is a bad idea - if you have two tables that do the same thing, then that is quite silly and you should of course rationalize the table design.[/quote]Good god, did they stop teaching data modelling entirely in college? Or have people just stopped going in the first place? I've already not only explained why this is false, I even gave examples of normalization that required modifications to multiple tables simultaneously. I suggest you read them....or just read a basic primer on normalization.Thu, 04 Nov 2010 08:34:28 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]David Portas (11/4/2010)[/b][hr][quote][b]Michael Wang (11/4/2010)[/b][hr][quote][b]David Portas (11/4/2010)[/b][hr][quote][b]Michael Wang (11/4/2010)[/b][hr]Two problems keep surfacing from this thread: confusion between a primary key and unique key constraints, and more fundamentally why the two are required in the first place. A primary key is required to identify a data item - it's purely physical and has nothing to do with the real world but everything to do with the data storage. This is where the physical model is insulated from the real world. [/quote]No, this is not a helpful distinction in any way. ....[/quote]Either you see it or you don't: the distinction is to separate your model world from the real world. It's where the real line is drawn.[/quote]Sure but in the words of Chris Date it's a "purely psychological" distinction, calling a key "primary" being just an aide-mémoire or a convenient label. It's only as important as you want it to be. What I objected to was your implication that this was somehow a definition of a primary key - it obviously isn't because the name does not define any special type of key.[/quote]OK, I accept your criticism: by primary key I meant a surrogate key and by unique key I meant natural unique key.Thu, 04 Nov 2010 08:32:05 GMTMichael Wanghttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Michael Wang (11/4/2010)[/b][hr][quote][b]David Portas (11/4/2010)[/b][hr][quote][b]Michael Wang (11/4/2010)[/b][hr]Two problems keep surfacing from this thread: confusion between a primary key and unique key constraints, and more fundamentally why the two are required in the first place. A primary key is required to identify a data item - it's purely physical and has nothing to do with the real world but everything to do with the data storage. This is where the physical model is insulated from the real world. [/quote]No, this is not a helpful distinction in any way. ....[/quote]Either you see it or you don't: the distinction is to separate your model world from the real world. It's where the real line is drawn.[/quote]Sure but in the words of Chris Date it's a "purely psychological" distinction, calling a key "primary" being just an aide-mémoire or a convenient label. It's only as important as you want it to be. What I objected to was your implication that this was somehow a definition of a primary key - it obviously isn't because the name does not define any special type of key.[quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr]Hi David, I'm a bit confused by what you mean here! Are you referring to a key that is a candidate key that is a primary key?[/quote]I'm referring to the fact that a primary key is just any one candidate key. All candidate keys are equal in the relational model and in SQL so the "primary" key doesn't imply anything fundamentally different from any other key.Thu, 04 Nov 2010 08:27:45 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Michael Ebaya (11/4/2010)[/b][hr][quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr]I am really curious to know why it's a good idea to not enforce the immutability of the key![/quote]This has already been covered in excruciating detail. First of all, a surrogate reduces the likelihood of an update, but it doesn't eliminate it entirely. More importantly, immutability is a desirable quality of key, but its not a requirement of a key. There are other desirable qualities that natural keys have (in some cases), and surrogates do not (again, in some cases). Neither is best in any and all cases.[quote]No, you are an anonymous poster who claims that he knows a great deal about databases, and not only that make claims about normal forms that aren't true.[/quote]Such as what? Your little myth that "no normal form affects more than one table" has already been roundly debunked.[/quote]Actually, it hasn't. As has been pointed out to you a number of times, the formal definition of the normal forms relates to the tables themselves. You seem to be confused with how the normal forms work. Note that I'm not saying that what you say is a bad idea - if you have two tables that do the same thing, then that is quite silly and you should of course rationalize the table design.Thu, 04 Nov 2010 08:20:34 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]David Portas (11/4/2010)[/b][hr]The phrase "unique key" is obviously a tautology and doesn't explain anything useful. Key = Candidate Key = Primary Key. There is no difference.[/quote]Hi David, I'm a bit confused by what you mean here! Are you referring to a key that is a candidate key that is a primary key?Thu, 04 Nov 2010 08:17:57 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]David Portas (11/4/2010)[/b][hr][quote][b]Michael Wang (11/4/2010)[/b][hr]Two problems keep surfacing from this thread: confusion between a primary key and unique key constraints, and more fundamentally why the two are required in the first place. A primary key is required to identify a data item - it's purely physical and has nothing to do with the real world but everything to do with the data storage. This is where the physical model is insulated from the real world. [/quote]No, this is not a helpful distinction in any way. ....[/quote]Either you see it or you don't: the distinction is to separate your model world from the real world. It's where the real line is drawn.Thu, 04 Nov 2010 08:17:34 GMTMichael Wanghttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr]I am really curious to know why it's a good idea to not enforce the immutability of the key![/quote]This has already been covered in excruciating detail. First of all, a surrogate reduces the likelihood of an update, but it doesn't eliminate it entirely. More importantly, immutability is a desirable quality of key, but its not a requirement of a key. There are other desirable qualities that natural keys have (in some cases), and surrogates do not (again, in some cases). Neither is best in any and all cases.[quote]No, you are an anonymous poster who claims that he knows a great deal about databases, and not only that make claims about normal forms that aren't true.[/quote]Such as what? Your little myth that "no normal form affects more than one table" has already been roundly debunked.Thu, 04 Nov 2010 08:15:00 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Michael Wang (11/4/2010)[/b][hr]Two problems keep surfacing from this thread: confusion between a primary key and unique key constraints, and more fundamentally why the two are required in the first place. A primary key is required to identify a data item - it's purely physical and has nothing to do with the real world but everything to do with the data storage. This is where the physical model is insulated from the real world. [/quote]No, this is not a helpful distinction in any way. A primary key is just any one candidate key. A "primary" key is not fundamentally different to any other key (unless you want it to be) and all keys serve the same purpose of enforcing uniqueness for some attributes in a table. Keys are therefore logical by definition. Indexes are physical structures but keys are logical constraints and keys need not have anything to do with storage because the DBMS may use some other identifier for data in storage (as indeed SQL Server uses RIDs).To say a primary key doesn't have meaning makes no sense as a general rule. If I create a table with only one key and it has meaning then how would that not qualify as a primary key? What difference would it make whether I called it "primary" or not? In fact it makes no difference at all.The phrase "unique key" is obviously a tautology and doesn't explain anything useful. Key = Candidate Key = Primary Key. There is no difference.Thu, 04 Nov 2010 08:10:23 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Michael Ebaya (11/4/2010)[/b][hr][quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr]You're getting a bit abusive here dude. Perhaps you should chill?[/quote]Thanks, but I'm not a "dude", and if you don't wish to be abused, perhaps you shouldn't be condescending ... especially when you're the one who is wrong.[/quote]No, you are an anonymous poster who claims that he knows a great deal about databases, and not only that make claims about normal forms that aren't true. You've been showing a lot of condescending behaviour yourself, so I'd suggest that those who live in glasshouses shouldn't throw stones. Like for instance, you just wrote "If that PK is a FK in 2 other tables, you change one column only in each table" - I'm going to assume that you didn't actually mean that but you mean that when you change a PK value and there are two FKs that reference it, then you need to change the FK values in the other tables. Of course, if you have an immutable PK, then you'll never [i]ever[/i] have to change the FKs in any tables. That's just good design - if you have a million row table and let's say 80% of your rows have values that reference your PK that you need to change, this could be a bit of a nightmare. And many folks reasonably add an index to the FK to improve performance, so every update to the base table will update the index - again, this is not a great way to go about doing things!And yet another consideration is the amount of effort you need to go towards actually ensuring that you update all the correct tables. Miss one and you could, again, be in trouble!Thu, 04 Nov 2010 08:05:32 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]David Portas (11/4/2010)[/b][hr][quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr][quote][b]ebaya (11/4/2010)[/b][hr]If your key changes, you update it.[/quote]Yeah, along with all the data in all the tables that rely on it. Nice one.[/quote]Sure, so if you have a key where you expect that to be a problem then you wouldn't reference it in other tables would you? You would use another key in the same table for the foreign key references (maybe a surrogate, maybe another natural key). So what you say is no argument at all against natural keys - it's just an argument for prudent design and choosing sensible foreign keys, which of course I agree with.[/quote]I'm glad you knew what I was talking about (obviously you don't change all the data in the table - can't believe I wrote that!) - :-)But I don't see the point of the key if it is expected to change. I am really curious to know why it's a good idea to not enforce the immutability of the key! I've not seen a really good argument yet.Thu, 04 Nov 2010 08:01:19 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr]You're getting a bit abusive here dude. Perhaps you should chill?[/quote]Thanks, but I'm not a "dude", and if you don't wish to be abused, perhaps you shouldn't be condescending ... especially when you're the one who is wrong.Thu, 04 Nov 2010 07:58:45 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Michael Ebaya (11/4/2010)[/b][hr][quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr][quote][b]ebaya (11/4/2010)[/b][hr]If your key changes, you update it.[/quote]Yeah, along with all the data in all the tables that rely on it. Nice one.[/quote]Given you were the poster who thought the rules of normalization only affect one table at a time, I guess I shouldn't be surprised by this comment. If your PK changes, you change the PK [b]only[/b]. Not the rest of the data in the table. If that PK is a FK in 2 other tables, you change one column only in each table. Not "all the data".Seriously, this isn't rocket science here.[/quote]I wrote that wrongly - indeed you need to change only the foreign key that refernce it. I of course know that, I managed to write my last comment fairly badly. You're getting a bit abusive here dude. Perhaps you should chill?Thu, 04 Nov 2010 07:47:22 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]ebaya (11/4/2010)[/b][hr][quote][b]Robert Mahon-475361 (11/4/2010)[/b][hr]That's what I don't get, an SSN doesn't uniquely identify a person.[/quote]Yes it does. You're still thinking at the database level. But a database is simply a model of some real world set of requirements. The real world is what counts here. In the real world, no two people have the same SSN.A duplicate SSN is a [b]data error.[/b] Changing your key doesn't "solve the problem". It simply pushes it up a level. You still have the data error -- but now you've let that error into your table, rather than keeping it out. To repeat, this is why that even if you choose to use a synthetic key for other reasons, you'd be wise to put a unique constraint on SSN anyway....Similarly, claiming "the natural key may change so I need a surrogate" is the hallmark of a lackwit. ....[/quote]Two problems keep surfacing from this thread: confusion between a primary key and unique key constraints, and more fundamentally why the two are required in the first place. A primary key is required to identify a data item - it's purely physical and has nothing to do with the real world but everything to do with the data storage. This is where the physical model is insulated from the real world. This key should never change (the example Ebaya used about migrating data from one physical to another entails resetting the primary key and has no bearing on the above statement).A unique key, or multiple unique keys, are required to identify a valid real world entity. This has everything to do with the real world and nothing to do with the data storage. This is governed by business logic. Where *all* data is required to be stored for forensic analysis, no unique key is required but a primary key is. Using SSN as the example, one may want to store all SSNs along with other info and then filter out the duplicates. Putting a unique key here creates a new problem: where do you store the duplicates?Only in rare situations where these two keys can be combined into one, such as a calendar. But this does not lend any legitimacy for mixing the two keys together.Thu, 04 Nov 2010 07:26:27 GMTMichael Wanghttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr][quote][b]ebaya (11/4/2010)[/b][hr]If your key changes, you update it.[/quote]Yeah, along with all the data in all the tables that rely on it. Nice one.[/quote]Sure, so if you have a key where you expect that to be a problem then you wouldn't reference it in other tables would you? You would use another key in the same table for the foreign key references (maybe a surrogate, maybe another natural key). So what you say is no argument at all against natural keys - it's just an argument for prudent design and choosing sensible foreign keys, which of course I agree with.Thu, 04 Nov 2010 07:26:19 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]ta.bu.shi.da.yu (11/4/2010)[/b][hr][quote][b]ebaya (11/4/2010)[/b][hr]If your key changes, you update it.[/quote]Yeah, along with all the data in all the tables that rely on it. Nice one.[/quote]Given you were the poster who thought the rules of normalization only affect one table at a time, I guess I shouldn't be surprised by this comment. If your PK changes, you change the PK [b]only[/b]. Not the rest of the data in the table. If that PK is a FK in 2 other tables, you change one column only in each table. Not "all the data".Seriously, this isn't rocket science here.Thu, 04 Nov 2010 07:24:21 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspxif you have to account for dead people then you'll probably need a multi-column PK. on the SSN column and on a name column or maybe add a bit column for alive/dead and use thatThu, 04 Nov 2010 07:19:03 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]ebaya (11/4/2010)[/b][hr]If your key changes, you update it.[/quote]Yeah, along with all the data in all the tables that rely on it. Nice one.Thu, 04 Nov 2010 07:17:58 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Robert Mahon-475361 (11/4/2010)[/b][hr]I see what you're saying, it's probably that I've written systems where duplicated SSN's were far more common than they should be and it was WANTED to allow them to be entered for later... purposes![/quote]In that case you should ask the user / business owner how they intend to identify employees in the organisation and make sure you implement the key(s) they need to do that. In organisations I have worked for, employees were identified with a company-specific employee number that was required to be unique. If you ignore the problem of defining an appropriate natural key then you just create problems for yourself and your users down the line.Thu, 04 Nov 2010 06:49:24 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspxI see what you're saying, it's probably that I've written systems where duplicated SSN's were far more common than they should be and it was WANTED to allow them to be entered for later... purposes!Why I've probably got the view I have and see it just as an attribute of an entity. I've never had the issues Natural Keys appear to have (and why there's so many articles about them I guess!)But many thanks to try and explain a different point of view. Thu, 04 Nov 2010 06:38:54 GMTRobert Mahon-475361http://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Robert Mahon-475361 (11/4/2010)[/b][hr]That's what I don't get, an SSN doesn't uniquely identify a person.[/quote]Yes it does. You're still thinking at the database level. But a database is simply a model of some real world set of requirements. The real world is what counts here. In the real world, no two people have the same SSN.A duplicate SSN is a [b]data error.[/b] Changing your key doesn't "solve the problem". It simply pushes it up a level. You still have the data error -- but now you've let that error into your table, rather than keeping it out. To repeat, this is why that even if you choose to use a synthetic key for other reasons, you'd be wise to put a unique constraint on SSN anyway.Saying "the natural key might have duplicates so I need a surrogate" is the hallmark of a lazy developer. It's a way for you to shift the buck, and allow you to claim your database is still technically functioning properly, because you allowed a bad row to get shoved in without throwing an error back up to the application. Similarly, claiming "the natural key may change so I need a surrogate" is the hallmark of a lackwit. Guess what? Data changes in databases. If your key changes, you [b]update[/b] it. Many times natural keys change so infrequently that this is irrelevant. Many times a natural key is only used in one or two particular tables. And even when the natural key changes often, and/or is used as a FK in dozens of other tables, using a synthetic is simply a performance optimization, not some hard-and-fast requirement.You might think the missing SSN is a reason not to use a natural key. Wrong again. If your particular application allows people to be identified without a SSN, [i]then SSN isn't a natural key to begin with[/i]. It's a dependent attribute of some other natural key. Maybe your business rules (NOT your database) has some other natural key for people. Likely it's some lengthy set of attributes, in which case you _could_ use it, but you'd be wise to substitute a surrogate key. Maybe it has no natural key at all, which is usually a real problem you need to take up with whoever's defining your business logic.Thu, 04 Nov 2010 06:17:18 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Robert Mahon-475361 (11/4/2010)[/b][hr]When WOULD a Primary Key's actual data (natural or surrogate) ever actually be changed? For a natural key I could see you'd have to, but a surrogate?[/quote]I alreadt gave an example of a natural key that might change (a login name). Surrogate keys can change for several reasons. For example if the schema has to be changed the data might need to be moved to new tables with pre-existing data, or if new data is merged from another database source then keys may need to be reallocated. To merge and deduplicate data from multiple sources you need a natural key.In the case of natural keys, key change may be easier to handle if foreign key references use a surrogate (i.e. in the situation where the natural key of a table changes but the surrogate doesn't). However, the natural keys are by definition still important from a data integrity perspective. Enforcing the natural keys prevents duplication of business data, which the surrogate key can never do. So it's more important to ensure that each table has appropriate natural keys defined on it and [i]then[/i] decide whether you need to implement a surrogate on it as well. In all cases you may need to consider whether and how you want to support key changes.Thu, 04 Nov 2010 06:08:45 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]David Portas (11/4/2010)[/b][hr][quote]Ultimately any key value (including a surrogate) can change but what makes keys useful and important is the fact that the database enforces their uniqueness and that therefore users can rely on them to identify data correctly in the database. [/quote]That's the bit that throws me. When Natural Keys have been discussed, the way they change, and the faffing about to change everything else hanging from those tables, and even other systems that have connected up, it's been a nightmare. When using a surrogate, it can't change can it? Once set, if it's a huge sequential INT, or a GUID, it's never going to be amended, all that logic on checking 'does this key exist' isn't needed. If if IS changeable, then you've either got the potential for duplicated data that does break normalisation, or you've got bits of data meaning different things in different places.I guess it's not AS bad if those changes can cascade, though I've seen accountants Spreadsheets go to hell when things were changed and some macro's that someone created long ago didn't take into account changing keys (will never use an int PrimaryKey as an invoice number again *ahem*). Just keeping it as that INT, it'll never change.When WOULD a Primary Key's actual data (natural or surrogate) ever actually be changed? For a natural key I could see you'd have to, but a surrogate?Thu, 04 Nov 2010 05:45:23 GMTRobert Mahon-475361http://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Robert Mahon-475361 (11/4/2010)[/b][hr]In what way out of curiosity?SSN's keep being mentioned, but they can change/be missed/be duplicates. Most other 'natural' keys end up falling to the same traps. Therefore, they're not valid keys and don't meet the rule. The linked article, in an aggressive style true, appears to be very valid though. What's specifically wrong?[/quote]A key is a set of attributes that have exactly two properties: Uniqueness and Irreducibility. The fact that a key value might change doesn't make it any less of a key, stability being a very useful property of a key but not an absolute requirement. Normalization is entirely orthogonal to changing keys because all normal forms are defined in terms of relations, not relation variables. So stable keys are not any requirement of normalization.Of course stability is an important design consideration but in some cases it makes perfect sense to enforce unqiueness of key values that might change. A good example is a user login name. The user may be allowed to change his/her login name at any time as long as long as the new name is not a duplicate with any other.Ultimately any key value (including a surrogate) can change but what makes keys useful and important is the fact that the database enforces their uniqueness and that therefore users can rely on them to identify data correctly in the database. To say that a natural key violates Normal Form suggests a profound misunderstanding of what keys and normalization are all about. If that were so then normalization would be useless as a design tool for analysing and designing databases and ensuring that dependencies are correctly represented in the database.Thu, 04 Nov 2010 03:43:27 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspxThat's what I don't get, an SSN doesn't uniquely identify a person. I've seen duplicates, missing data, or it's wrong. Is it an attribute of an entity? Aye. Can it be used as a key? Only if you have to then add things later to get around it not working all the time. In which case, why use it in the first place? If you put constraints on the SSN, then you have to deal with it at the application level when it already exists, should it be allowed (duplicates! so to fix that you add something else, but how do you handle Fred Smith with the same SSN when it's not the same person? (systems I've written recently encounter this frequently)).Thu, 04 Nov 2010 03:24:12 GMTRobert Mahon-475361http://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]ebaya (11/4/2010)[/b][hr][quote][b]Robert Mahon-475361 (11/4/2010)[/b][hr]In what way out of curiosity?SSN's keep being mentioned, but they can change/be missed/be duplicates. Most other 'natural' keys end up falling to the same traps. Therefore, they're not valid keys and don't meet the rule.[/quote]This just isn't true at all. Natural keys violate no normalization rules. A key is valid if it uniquely identifies the [b]entity[/b], not because it uniquely identifies a row in a table. Most people don't understand the difference. Nor does a change in a key value violation any normal form ... it just creates a bit of a headache for developers.Take your example. A SSN uniquely identifies a person, period. If you're seeing duplicate SSNs in your data, and you create a synthetic key, you have't "normalized" anything. You've simply pushed your data integrity issue up a level -- from the database up to the application. In general, that's a very bad thing ... which is why even if you did choose to use a synthetic key for other reasons, you'd be wise to set a unique constraint on SSN anyway. Synthetic keys have a lot of advantages. But anyone who tells you they should be used in any and all situations, or that natural keys "violate a normal form" is an ignorant, unshaven buffoon who either has never taken a class on data modelling, or slept through it if he did.[/quote]I guess my question is: under what scenario would a natural key be appropriate?Incidentally, natural keys by themselves cannot violate a normal form. 100% agree with ebay here!Thu, 04 Nov 2010 02:58:35 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]Robert Mahon-475361 (11/4/2010)[/b][hr]In what way out of curiosity?SSN's keep being mentioned, but they can change/be missed/be duplicates. Most other 'natural' keys end up falling to the same traps. Therefore, they're not valid keys and don't meet the rule.[/quote]This just isn't true at all. Natural keys violate no normalization rules. A key is valid if it uniquely identifies the [b]entity[/b], not because it uniquely identifies a row in a table. Most people don't understand the difference. Nor does a change in a key value violation any normal form ... it just creates a bit of a headache for developers.Take your example. A SSN uniquely identifies a person, period. If you're seeing duplicate SSNs in your data, and you create a synthetic key, you have't "normalized" anything. You've simply pushed your data integrity issue up a level -- from the database up to the application. In general, that's a very bad thing ... which is why even if you did choose to use a synthetic key for other reasons, you'd be wise to set a unique constraint on SSN anyway. Synthetic keys have a lot of advantages. But anyone who tells you they should be used in any and all situations, or that natural keys "violate a normal form" is an ignorant, unshaven buffoon who either has never taken a class on data modelling, or slept through it if he did.Thu, 04 Nov 2010 02:17:04 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspxIn what way out of curiosity?SSN's keep being mentioned, but they can change/be missed/be duplicates. Most other 'natural' keys end up falling to the same traps. Therefore, they're not valid keys and don't meet the rule. The linked article, in an aggressive style true, appears to be very valid though. What's specifically wrong?Thu, 04 Nov 2010 01:53:55 GMTRobert Mahon-475361http://www.sqlservercentral.com/Forums/Topic1009625-1448-1.aspx[quote][b]autoexcrement (11/3/2010)[/b][hr][quote][b]Robert Mahon-475361 (10/24/2010)[/b][hr]Anytime this gets brought up (and it does, time and time again), before we argue further, I /really/ recommend reading;http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6136&whichpage=2and quazibubble's posts. Yes he's argumentative, but puts up an impressive reasoning. [/quote]Thank you so much for this. I'm reading quazibubble's super-mega-long post now about creating the world's most perfect natural key and I don't think I've laughed this hard in months.[/quote][quote][b]quazibubble[/b][hr]Natural keys (even if a single column) violate normal forms 1, 2, and 3[/quote]What a load of rubbish. Please don't post links that might encourage others to read it.Wed, 03 Nov 2010 18:13:42 GMTsqlvogel