I'm no "seasoned veteran", but in the 6 years if been developing software fulltime, I've yet to hear anyone suggest anything but a unique sequential number for a primary key. (with the possible exception of records interfaced from different systemswhich can't use the same "key generation mechanism" and in this situation, I've just used a unique "source id" and created a concatenated key with the two columns)
I think there is good value in this article if it would just been entitled "Primary Keys for Beginners" and reworded things to focus on a "what makes a good primary key" angle sprinkled with some discussion of referencial integrity.
But it's a free read and a great site. Thanks!
Lyndal.
Discussion on:
View:
Show:
Sure, never have I used naturally occuring phenomenon as a primary key. Primary Keys, as you rightly pointed are not for users but for application to used.
Re-titling this articles is uncalled for.There is nothing "Biginners" about this artilce,even veterans need to be reminded from time to time about rationals behind using certain concepts in application development and this is one of those instance.
Nice article, give us more of those
Re-titling this articles is uncalled for.There is nothing "Biginners" about this artilce,even veterans need to be reminded from time to time about rationals behind using certain concepts in application development and this is one of those instance.
Nice article, give us more of those
Sure, never have I used naturally occuring phenomenon as a primary key. Primary Keys, as you rightly pointed are not for users but for application to used.
Re-titling this articles is uncalled for.There is nothing "Biginners" about this artilce,even veterans need to be reminded from time to time about rationals behind using certain concepts in application development and this is one of those instance.
Nice article, give us more of those
Re-titling this articles is uncalled for.There is nothing "Biginners" about this artilce,even veterans need to be reminded from time to time about rationals behind using certain concepts in application development and this is one of those instance.
Nice article, give us more of those
Not sure why primary key needs to be sequential. GUID's work fine, thought they're quite a bit slower to process than integers.
A nice addition to this topic would be to discuss scoping primary keys. This forum, for example, uses integers for threads and messages (probably referring to the same key value for the content store, along with parent_id). Is there also a board or site_id that can be used to uniquely identify the message on a given site?
A nice addition to this topic would be to discuss scoping primary keys. This forum, for example, uses integers for threads and messages (probably referring to the same key value for the content store, along with parent_id). Is there also a board or site_id that can be used to uniquely identify the message on a given site?
I have had designers/developers that I worked with or managed argue for natural primary keys.
The primary (:)) justifications cited were that they are "recognizable", as opposed to "just a number" (unless the PK is a type such as datetime, wherethe concept of "natural vs. surrogate" becomes a little gray, anyway) and that a surrogate key represents "a waste of space" in the database in those cases where a natural PK will suffice, i.e. - why add an additional column when the columns holding"real data" will suffice?
Excellent article.
The primary (:)) justifications cited were that they are "recognizable", as opposed to "just a number" (unless the PK is a type such as datetime, wherethe concept of "natural vs. surrogate" becomes a little gray, anyway) and that a surrogate key represents "a waste of space" in the database in those cases where a natural PK will suffice, i.e. - why add an additional column when the columns holding"real data" will suffice?
Excellent article.
I've had this argument many a time with seasoned vets. Some people I know still will NOT use auto incrementing columns / identity / sequences (depending on what db you are using) to this day. For many reasons, including "the database can make a mistake" (pretty sure they don't make mistakes anymore althought in the past this has been known to happen), "it's not a good way to identify things", etc.
But I keep trying to tell them the benefits of id numbering. I'll have to send them this article.
But I keep trying to tell them the benefits of id numbering. I'll have to send them this article.
In a multi-user setting, Access/Jet can make this mistake in that there can be enough time for two users to get, and try to save, the same counter result. To get around this, many of us generate those counter fields in our own VBA code.
Serial numbers are still the best idea, in my opinion.
Serial numbers are still the best idea, in my opinion.
Don't be relegious about this, be practical.
Surrogate (meaningless) keys are useful if the natural key is long and/or complex and if the table is referenced.
If you use a surrogate key (and an identity column is a good choice for a surrogate key)you should always put an unique constraint on the natural key columns. This way you prevent that the same data is entered twice with a different surrogate (primary) key.
If a table is not referenced, there isno point to add a surrogate key. It is awaste of space.
Surrogate (meaningless) keys are useful if the natural key is long and/or complex and if the table is referenced.
If you use a surrogate key (and an identity column is a good choice for a surrogate key)you should always put an unique constraint on the natural key columns. This way you prevent that the same data is entered twice with a different surrogate (primary) key.
If a table is not referenced, there isno point to add a surrogate key. It is awaste of space.
I've been developing RDBMS applications as a consultant for large corporations for some 15+ years and often I come in at some mid-way point in the database design by junior developers and management. The argument to use surrogate keys is often the first one I have to make as I look over the design that I'm supposed to take the "technical lead" on implementing. It usually takes several "horror" stories of lessons learned on legacy systems in my past experience and even then, management *reluctantly* goes along with my suggestion. This often happens at the expense of other potential suggestions I might like to make because any good consultant has to "pick his/her battles" so as not to seem like a non-team player. This article (as long asit's available) will remain as a favorite link in my browser at-the-ready for any future arguments I may have to make in favor of surrogate keys.
Selecting Surrogate keys is the simpleset decision, no need to think just add a column and set it identity or add a sequence may think a bit more and add rowid or Guid.
But there are cases, that Natural keys are better than Surrogate keys.It is where a good Natural key exists, like when a good code is introduced by the business logic or by a standard like ISO standard. I don't think a surrogate key can be better than ISO country code when you have a Country table, same Currency codes like IRR,SEK and USD are much better than 0,1 and 2. They are better for maintenance, exporting and importing. to be used as foreign key.
I saw many errors, when a programmer joined to tables by wrong pk/fk s and as both keys where numbers, for long time no one could find the problem, but what about this join
.
.
.
Currencies
inner join CurrencyRates on Currencies.Code = CurrencyRates.CurrencyRateID
the error shows up immediately.
I realy have found, many problems in such a n implementation that could be solved using its natural key.
But there are cases, that Natural keys are better than Surrogate keys.It is where a good Natural key exists, like when a good code is introduced by the business logic or by a standard like ISO standard. I don't think a surrogate key can be better than ISO country code when you have a Country table, same Currency codes like IRR,SEK and USD are much better than 0,1 and 2. They are better for maintenance, exporting and importing. to be used as foreign key.
I saw many errors, when a programmer joined to tables by wrong pk/fk s and as both keys where numbers, for long time no one could find the problem, but what about this join
.
.
.
Currencies
inner join CurrencyRates on Currencies.Code = CurrencyRates.CurrencyRateID
the error shows up immediately.
I realy have found, many problems in such a n implementation that could be solved using its natural key.
Okay!
If you are using Surrogate keys for 2 tables, Invoice and LineItem. Invoice contains info on an individual sale (an Invoice). The various items sold on that invoice appear in the table LineItem. What is the recommendation for the the composition of the PK for table LineItem? A single surrogate key with the Invoice PK FKd to Invoice OR a composite PK of BOTH surrogates?
Mike
If you are using Surrogate keys for 2 tables, Invoice and LineItem. Invoice contains info on an individual sale (an Invoice). The various items sold on that invoice appear in the table LineItem. What is the recommendation for the the composition of the PK for table LineItem? A single surrogate key with the Invoice PK FKd to Invoice OR a composite PK of BOTH surrogates?
Mike
You'd relate these too tables by mapping one key to the other, in this case, each LineItem row would have a field/column that is an index back into Invoice table. ex: item_invoice_key which contains the value in the Invoice primary key. Then this relationship can never be broken because that primary key will never change.
There are normally two approaches.
If orders will contain large numbers of related line_item records, it is often a good idea to use a composite key made up of invoice_ID (the primary key value of the invoice record) from the invoice table and aninteger field often called line_number with a unique constraint. This allows customers to refer to items on an invoice by line_number. Example: "We were short two items on line #562 of invoice #10364." One problem is the task of programmatically generating the line_number value for each new record in the line_item table. Another problem is the possibility of the same product being ordered more than once on the same invoice on different line_item records.
If invoices have fewer line items, a common solution is a composite key made up of the invoice_ID number and the product_ID number for that line_item. Accidentally trying to add the same product to the invoice more than once would be flagged as a primary key violation in the line_item table.
If orders will contain large numbers of related line_item records, it is often a good idea to use a composite key made up of invoice_ID (the primary key value of the invoice record) from the invoice table and aninteger field often called line_number with a unique constraint. This allows customers to refer to items on an invoice by line_number. Example: "We were short two items on line #562 of invoice #10364." One problem is the task of programmatically generating the line_number value for each new record in the line_item table. Another problem is the possibility of the same product being ordered more than once on the same invoice on different line_item records.
If invoices have fewer line items, a common solution is a composite key made up of the invoice_ID number and the product_ID number for that line_item. Accidentally trying to add the same product to the invoice more than once would be flagged as a primary key violation in the line_item table.
We still use serial numbers on the child tables. This allows for expansion that comes too often.
Expansion in this case would be if the line items had sub-line items under them. I would have InvoiceID in the Invoices table. Then, LineID AS PK in the LineItems table. Line items would have a field InvoiceFK that contains the PK of the corresponding Invoice.
Finally, I would have SubLineID as PK in the table SubLineItems. This table would contain a field LineFK that has the PK of the corresponding invoice.
I had to do this with most of this type of relationship at this office, because users kept finding more ways to break up the data. I'd complain, but I enjoy it too much.
Expansion in this case would be if the line items had sub-line items under them. I would have InvoiceID in the Invoices table. Then, LineID AS PK in the LineItems table. Line items would have a field InvoiceFK that contains the PK of the corresponding Invoice.
Finally, I would have SubLineID as PK in the table SubLineItems. This table would contain a field LineFK that has the PK of the corresponding invoice.
I had to do this with most of this type of relationship at this office, because users kept finding more ways to break up the data. I'd complain, but I enjoy it too much.
Using ProductID in conjunction with the InvoiceID as the PK of a line item constrains the structure to current business rules which can change. Suppose the business decides to allow separate line items for the same product (IT happens). Using a sequential line ID does not restrict you from applying the current business rules (1 instance of ProductID per Invoice). It is often feasible enforce the current business rules with a separate unique index. In this case, then, if the business rules change, the separate index can be altered or removed without affecting the fundamental 1:M relationship between the Invoice and its Line Items.
This error is all-too-common, using a line item as a combined-PK on a related table.
If the line number (item number, whatever) ever appears externally, it's a bad choice for *any* part of a primary key. What if the business changes the way lineitems are numbered or identified, or allows multiple entries for the same line number (example: a computer system, 1 line, configurable components, same line). The line number is a natural key at that point, not a pure surrogate.
If the line number (item number, whatever) ever appears externally, it's a bad choice for *any* part of a primary key. What if the business changes the way lineitems are numbered or identified, or allows multiple entries for the same line number (example: a computer system, 1 line, configurable components, same line). The line number is a natural key at that point, not a pure surrogate.
I would most definitely say A surrogate Key with the Invoice PK Fkd to invoice. I have worked with sales software, and this is the way it has always been implemented.
True, a system-generated primary key will mean that no two records will ever be exactly the same, but there is nothing to prevent the same customer being entered twice and ending up with two different customer ID's. Sure the records aren't exactly the same, but they really are the same person. Anyone that has been involved in "de-duping" a large table will attest to how much fun tracking this can be.
Another important consideration is how quickly new records must be generated. If you are creating new records quickly, auto-generated is the only way to go. If new record creation is more leisurely, a character field can be used. For example, a product part number might be part of a series and contain some information about the part itself.
At one place I worked the part numbers matched the original catalog numbers. TA17-580 might be an air brake cam for a semi-trailer. The cams were left and right handed. 580 would be the right and 581 would be the matching left. The TA stood for trailer axle. Each value in the part number had some relation to the specification of the part. The part numbers held meaning for the customers, system-generated numbers do not.
Postal codes have meaning in each digit as do Social Security numbers and amateur radio license numbers. Somewhere, these values are the primary key for a record, even though they are not meaningless system generated values.
When designing a database there are no rules as simple as the one you propose. Each relationmust be examined alone, candidate keys identified and then decisions made based on a wide number of factors including performance, maintainability, readability and confidentiality.
Another important consideration is how quickly new records must be generated. If you are creating new records quickly, auto-generated is the only way to go. If new record creation is more leisurely, a character field can be used. For example, a product part number might be part of a series and contain some information about the part itself.
At one place I worked the part numbers matched the original catalog numbers. TA17-580 might be an air brake cam for a semi-trailer. The cams were left and right handed. 580 would be the right and 581 would be the matching left. The TA stood for trailer axle. Each value in the part number had some relation to the specification of the part. The part numbers held meaning for the customers, system-generated numbers do not.
Postal codes have meaning in each digit as do Social Security numbers and amateur radio license numbers. Somewhere, these values are the primary key for a record, even though they are not meaningless system generated values.
When designing a database there are no rules as simple as the one you propose. Each relationmust be examined alone, candidate keys identified and then decisions made based on a wide number of factors including performance, maintainability, readability and confidentiality.
Even if you had a natural key, you can still have duplicates. How many times have you seen I.B.M. and IBM in the same company table?
SSN isn't a guaranteed to be unique. And postal codes, what happened when USPS changed to 9 digits?
Never usea guid for a primary key, it will kill system performance. An alternate key mabey(replication), but not a PK-FK relationship.
Brian Beatty
MCSD
SSN isn't a guaranteed to be unique. And postal codes, what happened when USPS changed to 9 digits?
Never usea guid for a primary key, it will kill system performance. An alternate key mabey(replication), but not a PK-FK relationship.
Brian Beatty
MCSD
There is nothing to stop you from adding a Unique clause that is not part of the PK. This should allow balance between a surrogate key and natural rules.
Two of the major factors in deciding on a natural or surrogate key is: (A) Can the value of the proposed natural key be modified in any way, at any time, by anyone or anything or any circumstance outside the complete control of the automated system you are developing? and, related to that, (B) Can it ever be the case that when you need to record the existence or possible existence of some entity, the value of its natural primary key is unknown?
If the answer to either (A) or (B) is Yes, then the proposed natural key is a poor choice for a physical key. For example, it's not unusual in the United States for a system to use the Social Security Number as the key for records about a person. (I hope it's less common than it used to be, but I'n sure there are still many such systems.) But SSNs are often unknown at the time a record ought to be created (depending on the purpose of your system you may not even know that the person exists - it may just be a suspicion, or an expectation); they can be misreported and need correction in your system; and, although it is rare, they are sometimes misassigned and later corrected by the government, necessitating their change in your system. So they are actually very poor natural keys.
Part Numbers defined within your system are a slightly more nuanced case, but there too, I would steer away from using them in the key. It may be the case today that you always assign them at the time of recording the part in the system, and never, ever, ever change them once assigned, but will that always be so? Think about the possibility of mergers or acquisitions, and what that may do to your numbering scheme. Or maybe, even though your design and manufacturing engineers are practically perfect in every way, just maybe, one of them makes a mistake one day and has to change it. If this natural key is the physical key, and the part number has been in use for a while and then has to be changed, you have a problem. even if it's "only" a performance problem.
If the answer to either (A) or (B) is Yes, then the proposed natural key is a poor choice for a physical key. For example, it's not unusual in the United States for a system to use the Social Security Number as the key for records about a person. (I hope it's less common than it used to be, but I'n sure there are still many such systems.) But SSNs are often unknown at the time a record ought to be created (depending on the purpose of your system you may not even know that the person exists - it may just be a suspicion, or an expectation); they can be misreported and need correction in your system; and, although it is rare, they are sometimes misassigned and later corrected by the government, necessitating their change in your system. So they are actually very poor natural keys.
Part Numbers defined within your system are a slightly more nuanced case, but there too, I would steer away from using them in the key. It may be the case today that you always assign them at the time of recording the part in the system, and never, ever, ever change them once assigned, but will that always be so? Think about the possibility of mergers or acquisitions, and what that may do to your numbering scheme. Or maybe, even though your design and manufacturing engineers are practically perfect in every way, just maybe, one of them makes a mistake one day and has to change it. If this natural key is the physical key, and the part number has been in use for a while and then has to be changed, you have a problem. even if it's "only" a performance problem.
Your primary-key debate article caught my eye because I had this exact debate a few years ago. I think the article made some good points but it omitted one very important point; the use of random/arbitrary keys can often allow users to create duplicate records. The fact that your key is always random allows otherwise duplicate records to be entered into the database multiple times.
To the few people in this thread who argue against surrogate keys because of the possibility of creating duplicate entries....
Your posts are well-constructed and provide a legitimate concern, however...
Did you not pick up on the concept of creating a unique index on those fields which you are concerned about? Creating a unique index, which will not allow duplicates, solves your every concern. Please re-read for comprehension before posting.
Your posts are well-constructed and provide a legitimate concern, however...
Did you not pick up on the concept of creating a unique index on those fields which you are concerned about? Creating a unique index, which will not allow duplicates, solves your every concern. Please re-read for comprehension before posting.
Because this is natural data that may not yet be available, you can even set the unique index to avoid NULLs. Then, make some queries, views, or stored procedures to audit for missing values.
Your HR staff will no longer have to wait for a Social Security Number to generate the welcome documents for that new employee. They can get the SSN from the employee and enter it on the start date.
Your HR staff will no longer have to wait for a Social Security Number to generate the welcome documents for that new employee. They can get the SSN from the employee and enter it on the start date.
Weak arguments to support their hypothesis. Yes, the natural key must be available at creation. Why create the record if the key isn't known? Why introduce the additional step of deriving the surrogate key each time a record is accessed?
Lets look at this common scenerio.
I ahve two tables tblorder_master and tblorder_detail. Lets say both tables have surrogated keys as primcary keys.
Now master has order_no as a unique column and order_detail is in FK relationship with master table using order_no.
In this case too, to change an order_no user has to go through all the pains (ie disable relationship, change order no in detail first and than in master) as in case of order_no being a primary key.
So whats the advantage? Its more work now you have to maintain two keys.
I ahve two tables tblorder_master and tblorder_detail. Lets say both tables have surrogated keys as primcary keys.
Now master has order_no as a unique column and order_detail is in FK relationship with master table using order_no.
In this case too, to change an order_no user has to go through all the pains (ie disable relationship, change order no in detail first and than in master) as in case of order_no being a primary key.
So whats the advantage? Its more work now you have to maintain two keys.
It's not a surrogate primary key if the the user has access to change it.
I am new to databases (so new I can't find a job), and in my (probably worthless here) opinion the most important part of the article is that the primary key cannot be changed. Well, if it is referenced it cannot be changed. That is the case here. The data has stronger links because the pk cannot be changed. That is the strength of the surrogate pk, the information in any particular row in a detail table that is useful to a user will ALWAYS directly REFERENCE the CORRECT information in the master table. (anyway, can you even think of why some user might bother changing the order_no? )
I am new to databases (so new I can't find a job), and in my (probably worthless here) opinion the most important part of the article is that the primary key cannot be changed. Well, if it is referenced it cannot be changed. That is the case here. The data has stronger links because the pk cannot be changed. That is the strength of the surrogate pk, the information in any particular row in a detail table that is useful to a user will ALWAYS directly REFERENCE the CORRECT information in the master table. (anyway, can you even think of why some user might bother changing the order_no? )
That is why the article recommends that the primary key is never seen by the user. In your example, you would not use the order_no as the primary key on order_master. The order_no would likely have a unique constraint, but the primary key would besome other unique identifier. The order_detail would have a foreign key refering back to the order_master's primary key, but would not contain the order_no value.
Anytime you need to display the order_no along with data from the order_detail, you would look up the order_no from the order_master table. If you needed to change the order_no, you change it in the order_master, and only in the order_master. You never need to change the value that is being used as the primary key.
Anytime you need to display the order_no along with data from the order_detail, you would look up the order_no from the order_master table. If you needed to change the order_no, you change it in the order_master, and only in the order_master. You never need to change the value that is being used as the primary key.
A word of caution re advocating that a surrogate primary key should never be seen by the user. It depends who you mean by "the user" of course, but relational theory DEMANDS that keys be part of the data row - and so they must be visible to the user in sone circumstances - for example, when the user is a DBA.
As Chris Date writes in An Introduction to Relational Databases, 7th Edition, page 61:
"...relational databases abide by a very nice principle, called The Information Principle: The entire information content of the database is represented in one and only one way, namely as explicit values in column positions in rows in tables . . . there are no pointers . . ."
In the relational world, we are all users. So be sure to define your terms very precisely. Thanks.
As Chris Date writes in An Introduction to Relational Databases, 7th Edition, page 61:
"...relational databases abide by a very nice principle, called The Information Principle: The entire information content of the database is represented in one and only one way, namely as explicit values in column positions in rows in tables . . . there are no pointers . . ."
In the relational world, we are all users. So be sure to define your terms very precisely. Thanks.
SSN's also violate the uniqueness rule for primary keys. For more information, see the following link:
http://www.cpsr.org/cpsr/privacy/ssn/SSN-addendum.html#NewDBs
http://www.cpsr.org/cpsr/privacy/ssn/SSN-addendum.html#NewDBs
I once replaced an employee that would enter false SSNs just to get people entered into the database. Just after I arrived, and before I had a chance to fix the SSN situation, we got a new employee that had one of these "false SSNs."
The SSN had the same two digits repeating all the way through, but it was on their Social Security Card. We all got a good laugh at the former employee's expense.
I was glad actually. The boss gave me several hours of (much needed) authorized over time to fix the database so that it did not depend on SSNs and to clear out the old bogus ones.
The SSN had the same two digits repeating all the way through, but it was on their Social Security Card. We all got a good laugh at the former employee's expense.
I was glad actually. The boss gave me several hours of (much needed) authorized over time to fix the database so that it did not depend on SSNs and to clear out the old bogus ones.
Social security numbers can be changed. In the event that your number has been compromised you can request that a new one be issued (although the cure may be worse than the disease).
The most common problem I have seen in the real world is a system has a single bucket for a tax number. As soon as the business starts doing business in Canada, they will just reuse that "SSN" attribute. Then one day there is an error that no one can figure out and it seems that a Canadian Social Insurance number has collided with a USA Social Security Number. They are both nine digits.
The most common problem I have seen in the real world is a system has a single bucket for a tax number. As soon as the business starts doing business in Canada, they will just reuse that "SSN" attribute. Then one day there is an error that no one can figure out and it seems that a Canadian Social Insurance number has collided with a USA Social Security Number. They are both nine digits.
In Canada, European Union member countries, and others around the world, you would be seriously violating data protection and privacy laws in using a personal key other than the purpose for which it was originally intended. National IDs (or those that come close to being such), credit card numbers, bank accounts, even e-mail addresses, should not be used as keys except in the systems or specific functions for which they were intended. And then, strict security should be put in place to prevent improper disclosure (like the embarrassing one at US Dept of Social Security).
As for the US, get ready, with all the corporate malfeasance these days, it's only a matter of time before legislation, and not just good business sense, forces IT depts to comply. Proactive design can save a lot of future expense and embarrassment. By the way, foreign laws apply when you are storing data on the citizens of countries with protection legislation. In the EU, data protection is actually enshrinedin the constitution!
As for the US, get ready, with all the corporate malfeasance these days, it's only a matter of time before legislation, and not just good business sense, forces IT depts to comply. Proactive design can save a lot of future expense and embarrassment. By the way, foreign laws apply when you are storing data on the citizens of countries with protection legislation. In the EU, data protection is actually enshrinedin the constitution!
I've lost count of the number of times I've had this debate with both data modellers and developers and, to be quite blunt, the conclusion of the article is just plain wrong.
Why is it wrong? It's incorrect simply because any conclusion that says (essentially) that there is only one way to solve a problem has to be wrong. Probably the only thing I've really learnt during my life is that there are many ways to solve a problem and the method to use should depend on the context rather than a dogmatic adherence some arbitrary principle.
If I was to suggest that all programs should be written in a particular language because that language is superior to all other languages then I'd probably get crucified (and deserve it) yet it seems OKto say that all databases must be designed a certain way and anything else is an "inferior" design.
Most of he arguments against Natural Keys seem to come down to problems with error correction. Fair enough, it's a difficult problem, but let's have a reality check here. If we've got an application where we have to do much error correction (more than a few transactions a year) then I'd argue that we've got a badly designed system
I could come up with lots of examples where natural keys are superior to surrogate keys and they all boil down to one thing. The choice of using a natural key or a surrogate key is a mere implementation detail and is something to be decided after all the analysis has been finished and most of the design as well.
Personally, I think I'm doing my employer a disservice by making any implementation decisions without already having a set of defined requirements, which include performance and usability requirements as well as data maintenance requirements.
Anyway, if the article had concluded that the design should use whichever key type was most appropriate I wouldn't have a problem with it.
Why is it wrong? It's incorrect simply because any conclusion that says (essentially) that there is only one way to solve a problem has to be wrong. Probably the only thing I've really learnt during my life is that there are many ways to solve a problem and the method to use should depend on the context rather than a dogmatic adherence some arbitrary principle.
If I was to suggest that all programs should be written in a particular language because that language is superior to all other languages then I'd probably get crucified (and deserve it) yet it seems OKto say that all databases must be designed a certain way and anything else is an "inferior" design.
Most of he arguments against Natural Keys seem to come down to problems with error correction. Fair enough, it's a difficult problem, but let's have a reality check here. If we've got an application where we have to do much error correction (more than a few transactions a year) then I'd argue that we've got a badly designed system
I could come up with lots of examples where natural keys are superior to surrogate keys and they all boil down to one thing. The choice of using a natural key or a surrogate key is a mere implementation detail and is something to be decided after all the analysis has been finished and most of the design as well.
Personally, I think I'm doing my employer a disservice by making any implementation decisions without already having a set of defined requirements, which include performance and usability requirements as well as data maintenance requirements.
Anyway, if the article had concluded that the design should use whichever key type was most appropriate I wouldn't have a problem with it.
Yep. Everything should be decided on a case by case basis. Unfortunately, judging by the responses to this point in the discussion there are enough people who don't understand the topic to justify the conclusion. The conclusion is based on ensuring the integrity of the data. Surrogate keys will preserve the data integrity in EVERY case. There are cases where natural keys are acceptable (and more efficient), but they are rare (I can think of an example where a composite primary key of a natural incremental value and the foreign key to the master table actually maintain the integrity of the data more efficiently). As stated, the pk is a detail, and should have no effect on the data. In this case, a standard on surrogate primary keys is surely reasonable.
...it's the unique index you're going to create on the natural key which will ensure it.
Second, the author seems to make the assumption that all RDBMS's support some kind of sequential incrementation field (not all do), and that all RDBMS systems treat that incremetated field the same (almost none do). When using system-generated keys,I've almost always had problems when it came time to enter data because it REQUIRES knowing the key before entering the key in related tables. That means somehow you have to GET your key before you can USE your key. Sometime try using a generated key (without a LOT of work) in a program that uses SQL Server stored procedures to do your data entry. Not as easy as it would seem, without a LOT of complex SQL programming. And yes, you can maintain your own incrementation logic, but that requires continued maintenance and strict adherence to usage across multiple programming languages and often multiple development groups. Trust me, it breaks down all too quickly.
Finally, the author depends on the use of unique indexes to restrict unique data entry when required. Again, not all RDBMS's support them, and most implement them differently. Also, the use of the unique index requires error interpretation in your programming logic. Yes, it can be done, but while primary key violations are reported fairly consistently, unique indexes errors aren't. That means more work for the programmer to interpret error messages and apply data entry management. And non-primary indexes are easier to get around ... when your supporting programs for years, that becomes an issue. Not to mention supporting applications that may move from one RDMBS to another (or even sometimes changing between versions). From experience, I have learned that dependence on anything beyond basic SQL capabilities creates ahuge window for problems in the future.
(continued in part 3...)
Finally, the author depends on the use of unique indexes to restrict unique data entry when required. Again, not all RDBMS's support them, and most implement them differently. Also, the use of the unique index requires error interpretation in your programming logic. Yes, it can be done, but while primary key violations are reported fairly consistently, unique indexes errors aren't. That means more work for the programmer to interpret error messages and apply data entry management. And non-primary indexes are easier to get around ... when your supporting programs for years, that becomes an issue. Not to mention supporting applications that may move from one RDMBS to another (or even sometimes changing between versions). From experience, I have learned that dependence on anything beyond basic SQL capabilities creates ahuge window for problems in the future.
(continued in part 3...)
I would have to agree with this comment that the conclusion in this article is wrong, and STRONGLY agree that any proposed "solution" has to be what's right for the problem, not an adherence to a philosophy that "only one way is right...".
As I see it, there were three primary flaws in the logic of this article. First, the author seemed to unknowingly mix the definitions of "Primary Key" and "Foreign Key". The purpose of a Primary Key is NOT to relate to another table but only to uniquely identify each record in the table. The Foreign Key is used to relate one table to another. In a well-designed relational structure, the "Parent" table in a relationship will have the same Primary and Foreign Key, and the "Child" uses a defined index (which will normally INCLUDE the foreign key as a part of the primary key, which makes the relationship mandatory).
(continued in part 2...)
As I see it, there were three primary flaws in the logic of this article. First, the author seemed to unknowingly mix the definitions of "Primary Key" and "Foreign Key". The purpose of a Primary Key is NOT to relate to another table but only to uniquely identify each record in the table. The Foreign Key is used to relate one table to another. In a well-designed relational structure, the "Parent" table in a relationship will have the same Primary and Foreign Key, and the "Child" uses a defined index (which will normally INCLUDE the foreign key as a part of the primary key, which makes the relationship mandatory).
(continued in part 2...)
I feel that anyone who uses this article as a basis for database design decisions, or as a "justification" for why to make a decision will be doing themselves and those they share it with a great disservice. While it does re-open a LONG running debate, it does not do so well, nor provide much of real use.
If you want to go back to the best definition of what a primary key is and how it is used, please go back to basic Cod (and yes, I'm gonna use one of the OLDEST bad dbms programmer puns here):
"When creating a primary key, remember that everything in the table must relate to the key, the whole key, and nothing BUT the key, so help me Cod!" And that is STILL the best summarization of normalization and definition of primary keys that I know!
...John Harden
INSTEC
If you want to go back to the best definition of what a primary key is and how it is used, please go back to basic Cod (and yes, I'm gonna use one of the OLDEST bad dbms programmer puns here):
"When creating a primary key, remember that everything in the table must relate to the key, the whole key, and nothing BUT the key, so help me Cod!" And that is STILL the best summarization of normalization and definition of primary keys that I know!
...John Harden
INSTEC
This addresses all the parts of your post:
Using a generated key in SQL Server is trivial. There are several ways to obtain the last assigned identity value - the most common being the connection scope variable @@IDENTITY (use it directly in script, or use a SELECT @@IDENTITY in your app).
It is not a common practice to refer to the primary key in the "parent" table as a foreign key - typically, the columnn with the "child" table's reference to the parent table is referred to as the Foreign Key. The Foreign Key must reference a key that is either the primary key or an alternate, unique key in the parent table.
In databases that do not support auto-incrementing columns, a simple trick to achieve the same would be to use the following (SQL Server like syntax - add error handling):
@bSuccess = 0
WHILE @bSuccess = 0
BEGIN
SELECT @lOldMax = Max(SeqNo)
FROM SeqTable
WHERE SeqID = @ThisSequence
UPDATE SeqTable
SET SeqNo = @lOldMax + 1
WHERE SeqID = @ThisSequence
AND SeqNo = @lOldMax
IF @@ROWCOUNT = 1
@bSuccess = 1
ELSE
@bSuccess = 0
END
If the database does not support a @@ROWCONT (the number of rows affected by the last SQL statement), it may be necessary to use a blocking technique, and if the database does not support stored procedures or triggers, it may be necessary to move the functionality to the application's code (in its data access tier). Or, you could generate a GUID in your app, and use it as the surrogate key.
Whenyou are not able to spell the last name of Dr. Edgar Frank Codd correctly, it does not instill great confidence in your knowledge of his work...
Using a generated key in SQL Server is trivial. There are several ways to obtain the last assigned identity value - the most common being the connection scope variable @@IDENTITY (use it directly in script, or use a SELECT @@IDENTITY in your app).
It is not a common practice to refer to the primary key in the "parent" table as a foreign key - typically, the columnn with the "child" table's reference to the parent table is referred to as the Foreign Key. The Foreign Key must reference a key that is either the primary key or an alternate, unique key in the parent table.
In databases that do not support auto-incrementing columns, a simple trick to achieve the same would be to use the following (SQL Server like syntax - add error handling):
@bSuccess = 0
WHILE @bSuccess = 0
BEGIN
SELECT @lOldMax = Max(SeqNo)
FROM SeqTable
WHERE SeqID = @ThisSequence
UPDATE SeqTable
SET SeqNo = @lOldMax + 1
WHERE SeqID = @ThisSequence
AND SeqNo = @lOldMax
IF @@ROWCOUNT = 1
@bSuccess = 1
ELSE
@bSuccess = 0
END
If the database does not support a @@ROWCONT (the number of rows affected by the last SQL statement), it may be necessary to use a blocking technique, and if the database does not support stored procedures or triggers, it may be necessary to move the functionality to the application's code (in its data access tier). Or, you could generate a GUID in your app, and use it as the surrogate key.
Whenyou are not able to spell the last name of Dr. Edgar Frank Codd correctly, it does not instill great confidence in your knowledge of his work...
You state that you "... could come up with lots of examples where natural keys are superior to surrogate keys". Instead of stating this, a few good examples would be valued - and could be evaluated by others to determine their actual validity.
Several good examples HAVE been posted arguing FOR surrogate keys (or rather against natural keys) along with the reasons why the choice was right (or wrong).
I have never seen any compelling reason for using natural keys, except for trivial lists without any related tables, where it may save a few bytes per row. This is why I believe a surrogate (or at least automatic) key is superior - in ALL cases. I will modify this with two caveats:
1: If the original design results in a performance problem due to the surrogate key (I haven't had such a situation) I would consider using the natural key instead.
2: I have seen several cases where an automatically generated key has become a "natural" key in further use. While the potential consideration that future business rule changes could affect its continued use as the "natural" key, I believe it would often be relatively painless to add a non-primary natural key to contend with the new business rules, so I would not neccessarily have to hide the generated key from the user.
Several good examples HAVE been posted arguing FOR surrogate keys (or rather against natural keys) along with the reasons why the choice was right (or wrong).
I have never seen any compelling reason for using natural keys, except for trivial lists without any related tables, where it may save a few bytes per row. This is why I believe a surrogate (or at least automatic) key is superior - in ALL cases. I will modify this with two caveats:
1: If the original design results in a performance problem due to the surrogate key (I haven't had such a situation) I would consider using the natural key instead.
2: I have seen several cases where an automatically generated key has become a "natural" key in further use. While the potential consideration that future business rule changes could affect its continued use as the "natural" key, I believe it would often be relatively painless to add a non-primary natural key to contend with the new business rules, so I would not neccessarily have to hide the generated key from the user.
Well said!
Good designs embody a judicious, pragmatic mix of approaches, based on:
(a) The reality of the needs of the organization
(b) A sound knowledge of relational theory (which is all too often lacking) - even if the target DBMS is non-relational, by the way.
(c) The reality of the needs of the organization
(d) A good grasp of the abilities and limitations of your DBMS.
(e) The reality of the needs of the organization
Good designs embody a judicious, pragmatic mix of approaches, based on:
(a) The reality of the needs of the organization
(b) A sound knowledge of relational theory (which is all too often lacking) - even if the target DBMS is non-relational, by the way.
(c) The reality of the needs of the organization
(d) A good grasp of the abilities and limitations of your DBMS.
(e) The reality of the needs of the organization
According to your "theory", you could have many duplicate records with just different "sequence number" keys. That's ridiculous.
Surrogate adds no value except one more layer in the table association. There is no point of entering record that you don't "know" the value of. Besides, from a programmer's standpoint, it should be fairly easy to change the value of a natural primary key even it violents the referential restrict or unique restrict. It really makes no sense that author keeps on arguing how difficult that is --- only if s/he has ever done some programming her/himself.
Just my 2 cents.
Just my 2 cents.
I think it's really irresponsible to publish an article like this in this fashion.
First of all, calling it a debate is misleading since only one person's view is printed.
Second, to publish such a shallow analysis does a serious disservice to readers who are trying to understand the issue.
This kind of article should have been clearly labeled "editorial" or "soapbox" since it made no attempt to present a balanced view of the issue.
First of all, calling it a debate is misleading since only one person's view is printed.
Second, to publish such a shallow analysis does a serious disservice to readers who are trying to understand the issue.
This kind of article should have been clearly labeled "editorial" or "soapbox" since it made no attempt to present a balanced view of the issue.
I wish people would study this before posting. Point one is that this is not one persons opinion, there are two authors. Point two, feel free to state your opinions. Point out whats incorrect. In essence join the DEBATE that has resulted from this article. Feel free to rubbish any of the article but provide your own opinion rather that just post for the sake of it. Lets have your thinking on the issue. I am always ready to listen and learn.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
