InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 02:10:46 GMT20http://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Michael Valentine Jones (10/15/2010)[/b][hr][quote][b]Tom.Thomson (10/15/2010)[/b][hr][quote][b]Michael Valentine Jones (10/14/2010)[/b][hr]Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).Any other approach will eventually get you in trouble.[/quote]Do you really believe that or are you just trolling?[/quote]Of course I believe what I say. [/quote]I'm somewhat surprised, because it seems a very extreme position. It makes exactly as much sense to me as saying "always use natural keys" (ie none at all).[quote]I didn’t want to get into a big debate because it never changes anyone’s mind, but I just thought this thread needed to have a counterpoint to what seemed to be a “use natural keys” lovefest so that new people are aware that is not a universal sentiment, and that the always use surrogate keys position is a valid approach.To briefly state my position:I always use surrogate primary keys for the simple reason that I have never had reason to regret that, but have regretted the use of natural keys (usually be other people) on many, many occasions when those natural keys turned out to be not so immutable or to even be unique.[/quote]I will stick to my own position, which is to use a surrogate key when it it not sensible to use a natural key, use a natural key when it is not sensible to use a surrogate key, and where each will work use whichever will work better. I've been badly bitten by a design (not mine - I just had to clear up the mess when the system fell in a heap) that used surrogate keys for everything, and equally badly bitten by a design (again not mine, I just ended up doing cleanup) that used natural keys for everything (someone used an 800 byte collection of columns as the clustering key for one table, as well as as a forign key in several tables referncing that one). In my experience it has often been useful to use a natural key than not, but not anything like 100% of the time.Sat, 16 Oct 2010 08:23:14 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Michael Valentine Jones (10/15/2010)[/b][hr][quote][b]David Portas (10/15/2010)[/b][hr][quote][b]Michael Valentine Jones (10/14/2010)[/b][hr]Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).[/quote]A primary key means exactly the same thing as an alternate key so as you've written it this advice doesn't make much sense. I expect you mean "avoid using business data in any foreign key references" but that's different. Anyway, using natural keys for foreign key references can be very useful, advantageous and sometimes necessary.[/quote]That seems like a bit of nit picking. I think it's obvious in the context that what I meant is to make the surrogate key the primary key constraint and to put unique constraints on the remaining keys.[/quote]In light of your later comments I think it's far from obvious what you mean. You say:[quote]I have regretted the use of natural keys (usually be other people) on many, many occasions when those natural keys turned out to be not so immutable or to even be unique.[/quote]But if a "key" is not unique then it isn't suitable as a natural key whether referenced by a foreign key or not! So how can you say that you are in favour of such natural keys but opposed to their use as foreign keys? Similarly for immutability - especially since immutability of keys is anyway an illusion or at least a rather arbitrary point of view. What matters is not whether key values can change but whether they accurately identify the things they are supposed to identify.Fri, 15 Oct 2010 18:19:00 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]David Portas (10/15/2010)[/b][hr][quote][b]Michael Valentine Jones (10/14/2010)[/b][hr]Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).[/quote]A primary key means exactly the same thing as an alternate key so as you've written it this advice doesn't make much sense. I expect you mean "avoid using business data in any foreign key references" but that's different. Anyway, using natural keys for foreign key references can be very useful, advantageous and sometimes necessary.[/quote]That seems like a bit of nit picking. I think it's obvious in the context that what I meant is to make the surrogate key the primary key constraint and to put unique constraints on the remaining keys.I do have to disagree with the statement that "using natural keys for foreign key references can be very useful, advantageous and sometimes necessary", especially the necessary part.Fri, 15 Oct 2010 12:33:26 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Tom.Thomson (10/15/2010)[/b][hr][quote][b]Michael Valentine Jones (10/14/2010)[/b][hr]Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).Any other approach will eventually get you in trouble.[/quote]Do you really believe that or are you just trolling?[/quote]Of course I believe what I say.I didn’t want to get into a big debate because it never changes anyone’s mind, but I just thought this thread needed to have a counterpoint to what seemed to be a “use natural keys” lovefest so that new people are aware that is not a universal sentiment, and that the always use surrogate keys position is a valid approach.To briefly state my position:I always use surrogate primary keys for the simple reason that I have never had reason to regret that, but have regretted the use of natural keys (usually be other people) on many, many occasions when those natural keys turned out to be not so immutable or to even be unique.Fri, 15 Oct 2010 12:24:27 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Michael Valentine Jones (10/14/2010)[/b][hr]Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).Any other approach will eventually get you in trouble.[/quote]Do you really believe that or are you just trolling?Fri, 15 Oct 2010 04:01:09 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Michael Valentine Jones (10/14/2010)[/b][hr]Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).[/quote]A primary key means exactly the same thing as an alternate key so as you've written it this advice doesn't make much sense. I expect you mean "avoid using business data in any foreign key references" but that's different. Anyway, using natural keys for foreign key references can be very useful, advantageous and sometimes necessary.Fri, 15 Oct 2010 02:01:30 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxI know Michael from another forum and have the greatest of respect for him, but I will respectfully disagree. The requirements of some systems necessarily require natural keys, for others they are preferable to surrogates.Fri, 15 Oct 2010 01:53:52 GMThallidaydhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxSo you are suggesting that you should never consider a Natural or Candidate Key as the primary and always use a surrogate key regardless of the circumstances?Thu, 14 Oct 2010 16:16:27 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxAlways use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).Any other approach will eventually get you in trouble.Thu, 14 Oct 2010 15:06:22 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]GilaMonster (10/14/2010)[/b][hr][quote][b]Craig Farrell (10/14/2010)[/b][hr]I've been carrying a really bad myth around with me for years and never got called on it because the conversation never came up before on that specific aspect. [/quote]Queue up another blog post for me to write sometime....[/quote]Heh, please do. I think I have about 20 or so older clients that I need to send some corrected code... :facepalm:Thu, 14 Oct 2010 15:04:58 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Craig Farrell (10/14/2010)[/b][hr]I've been carrying a really bad myth around with me for years and never got called on it because the conversation never came up before on that specific aspect. [/quote]Queue up another blog post for me to write sometime....Thu, 14 Oct 2010 15:02:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]David Portas (10/14/2010)[/b][hr]I don't understand your claim that an index alone provides better performance than a uniqueness constraint. Can you back that up with an example? I'm pretty sure you'll find they are always exactly the same, in which case the constraint still has all the advantages I mentioned before.[/quote]And:[quote][b]Tom.Thomson[/b][hr]Good heavens, has someone introduced a major new performance reducing feature into the MS SQL Server optimiser so that it no longer uses the index that supports a UNIQUE constraint in the way it has used it for at least the last 10 years?[/quote]I'm going to duck out the discussion in general, but since I got called on this directly... You're absolutely correct. I've been carrying a really bad myth around with me for years and never got called on it because the conversation never came up before on that specific aspect. Thank you both.Thu, 14 Oct 2010 14:56:55 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxReally? Three pages barely counts as a warm up for a surrogate Vs natural key thread ;-)Thu, 14 Oct 2010 13:55:34 GMThallidaydhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxThis post has gotten very long but a very informative exchange of information. :cool:Thu, 14 Oct 2010 13:42:04 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]GilaMonster (10/14/2010)[/b][hr][quote]I have a part-written blog post on why clustered indexes are NOT the best indexes for range queries.Must finish. Some day.[/quote]Soon please!Thu, 14 Oct 2010 13:39:42 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Tom.Thomson (10/14/2010)[/b][hr]The guidelines aren't really sound; for example "can be used on range queries" really means "make things which are used close together live close together in the index" which, in an OLTP system, may have nothing to do with range queries [/quote]I have a part-written blog post on why clustered indexes are NOT the best indexes for range queries.Must finish. Some day.Thu, 14 Oct 2010 13:36:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Tom.Thomson (10/14/2010)[/b][hr]"can be used on range queries" really means "make things which are used close together live close together in the index" which, in an OLTP system, may have nothing to do with range queries (for example if I have a small numer of interactive users and a system where menus are constructed from text in a database, it's a good idea to cluster on language - if someone's using French menus he or she will probably continue to do so, and having the same text in 104 languages in cache just because it's been pulled in in French isn't useful: there's no range query involved there); the same text example blows away the "high degree of uniqueness" guideline (if it said "sets of columns" which had that property it would be sound, but it says "coumns" not "sets of columns"). The statement about identity columns is just plain false (duplicates can and do occur if uniqueness is not enforced by a constraint).[/quote]I have made this point (far less well) several times on another forum. I have promised several times to write some proof but never have. I think this is the first time I have read someone express the same opinion\truth\ignorant nonsense**** delete according to your brigade allegiance.Thu, 14 Oct 2010 13:22:36 GMThallidaydhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]PaulB-TheOneAndOnly (10/14/2010)[/b][hr]Still amazes me - yet it doesn't surprise me anymore - how this "surrogate Vs natural key debate" goes on and on. [/quote]It neither surprises nor amazes me - there are so many people talking nonsense about it that lots of other people will be confused. Then you get the relational "fundamentalists" versus the relational "purists" versus the ISO SQL Standard worshippers versus the real SQL in (pick the dbms of your choice) brigade each chipping in with their take on the issue - and given that these groups can't even agree a story on something as simple as NULLs it's to be expected they will not agree on something more complex like surrogate keys. [quote]Please let me start by stating my position on the issue which is: "Use Natural keys whenever possible, use Surrogate keys whenever necessary" [/quote]That's something that should be dinned into every relational database developer and administrator and architect and designer before they are allowed to practise the DB trade - provided "possible" is interpreted as meaning "both possible and reasonable". It has strong support in all the camps I mentioned above (even a relational "fundamentalist" like Fabian Pascal takes pretty much that position, which is what makes it easy for a pragmatist like me to agree with him).[quote]Having said that I think I've figured out why this "debate" gets particularly intense in the SQL Server universe - it has to be with clustered indexes on identity columns, please allow me to explain further.Let me quote Microsoft recommendations on "Clustered Index Design Guidelines" where it reads...[quote][b]With few exceptions[/b], every table should have a clustered index defined on the column, or columns, that offer the following: - Can be used for frequently used queries.- Provide a high degree of uniqueness. - Can be used in range queries.. . .Generally, you should define the clustered index key with as few columns as possible. Consider columns that [b]have one or more [/b]of the following attributes:- Are unique or contain many distinct values- Are accessed sequentially- Used frequently to sort the data retrieved from a table- Defined as IDENTITY because the column is guaranteed to be unique within the table[/quote]I personally find these guidelines sound but I also think some DBA construct them as "you must have a clustered index on an identity column" missing a couple of key words in Microsoft's documentation like "[i]with few exceptions[/i]" and "[i]columns that have one or more of the following attributes[/i]".My interpretation of the guidelines goes like: a clustered index is helpfull if chosen column is used in certain ways and is already defined in certain ways - which may (or may not) include the fact of already being of the identity datatype. [/quote]The guidelines aren't really sound; for example "can be used on range queries" really means "make things which are used close together live close together in the index" which, in an OLTP system, may have nothing to do with range queries (for example if I have a small numer of interactive users and a system where menus are constructed from text in a database, it's a good idea to cluster on language - if someone's using French menus he or she will probably continue to do so, and having the same text in 104 languages in cache just because it's been pulled in in French isn't useful: there's no range query involved there); the same text example blows away the "high degree of uniqueness" guideline (if it said "sets of columns" which had that property it would be sound, but it says "coumns" not "sets of columns"). The statement about identity columns is just plain false (duplicates can and do occur if uniqueness is not enforced by a constraint). [quote]Hope this brings fresh air to the so called [i]debate [/i]- I know it's almost impossible to end it :-D[/quote]I think it's about as likely to end as our longest thread.Thu, 14 Oct 2010 11:43:31 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Craig Farrell (10/13/2010)[/b][hr]However, I don't work in the SQL standard. I work in MS SQL Server and T-SQL. Because of that, and because an index can give me performance where a constraint will not, but still takes up the same system space and engine mechanics as a declared index under the hood, I would prefer the index.[/quote]Good heavens, has someone introduced a major new performance reducing feature into the MS SQL Server optimiser so that it no longer uses the index that supports a UNIQUE constraint in the way it has used it for at least the last 10 years? That would be horrifying if true :sick: ! But I don't believe it :-D .Thu, 14 Oct 2010 10:58:29 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]hallidayd (10/14/2010)[/b][hr][quote]- Defined as IDENTITY because the column is guaranteed to be unique within the table[/quote]That's not even correct.[/quote]Ouch, yes that is a bit of a glaring error. I've seen production databases with duplicate values in IDENTITY columns, which had nasty consequences. Feedback sent.Thu, 14 Oct 2010 07:05:01 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxActually, I'm not taking it entirely off tack. My correction is born of a similar observation\ pet peeve as yours. Far too often I see primary key\ IDENTITY\ Autonumber (in the case of Access) used as though interchangeable. They are, of course, not.Thu, 14 Oct 2010 07:02:31 GMThallidaydhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxSorry - don't want to take this off tack, but a column defined as IDENTITY is not "guaranteed to be unique within the table". Only a unique index\ constraint (or some hacky trigger\ UDF) can guarantee that.Thu, 14 Oct 2010 06:57:46 GMThallidaydhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]hallidayd (10/14/2010)[/b][hr][quote]- Defined as IDENTITY because the column is guaranteed to be unique within the table[/quote]That's not even correct.[/quote]:blink: please do elaborate.Thu, 14 Oct 2010 06:52:23 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote]- Defined as IDENTITY because the column is guaranteed to be unique within the table[/quote]That's not even correct.Thu, 14 Oct 2010 06:44:50 GMThallidaydhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxStill amazes me - yet it doesn't surprise me anymore - how this "surrogate Vs natural key debate" goes on and on.Please let me start by stating my position on the issue which is: "Use Natural keys whenever possible, use Surrogate keys whenever necessary(*)"(*) Like in FACT-DIM relationships in a dimensional model.Having said that I think I've figured out why this "debate" gets particularly intense in the SQL Server universe - it has to be with clustered indexes on identity columns, please allow me to explain further.Let me quote Microsoft recommendations on "Clustered Index Design Guidelines" where it reads...[quote][b]With few exceptions[/b], every table should have a clustered index defined on the column, or columns, that offer the following: - Can be used for frequently used queries.- Provide a high degree of uniqueness. - Can be used in range queries.. . .Generally, you should define the clustered index key with as few columns as possible. Consider columns that [b]have one or more [/b]of the following attributes:- Are unique or contain many distinct values- Are accessed sequentially- Used frequently to sort the data retrieved from a table- Defined as IDENTITY because the column is guaranteed to be unique within the table[/quote]I personally find these guidelines sound but I also think some DBA construct them as "you must have a clustered index on an identity column" missing a couple of key words in Microsoft's documentation like "[i]with few exceptions[/i]" and "[i]columns that have one or more of the following attributes[/i]".My interpretation of the guidelines goes like: a clustered index is helpfull if chosen column is used in certain ways and is already defined in certain ways - which may (or may not) include the fact of already being of the identity datatype.Hope this brings fresh air to the so called [i]debate [/i]- I know it's almost impossible to end it :-DThu, 14 Oct 2010 06:32:43 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Craig Farrell (10/13/2010)[/b][hr][EDIT:] Aaaaaand I finally look at the top of the screen and realize I'm in the "Relational Theory" Forum... I'll shaddup now. [/EDIT][/quote]Yeah - I learned a few years ago not to get in a disagreement with David about relational theory unless I was absolutely certain of my ground. And even then it turned out I was wrong :blush:Just to throw in another tack on the surrogate Vs natural key debate, there is a certain class of relationship that can't be modelled declaratively using surrogates, only with natural keys. I've sometimes wondered how the "surrogate and surrogates only" brigade handle them.Thu, 14 Oct 2010 03:29:31 GMThallidaydhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Craig Farrell (10/13/2010)[/b][hr]I work in MS SQL Server and T-SQL. Because of that, and because an index can give me performance where a constraint will not, but still takes up the same system space and engine mechanics as a declared index under the hood, I would prefer the index.[/quote]I don't understand your claim that an index alone provides better performance than a uniqueness constraint. Can you back that up with an example? I'm pretty sure you'll find they are always exactly the same, in which case the constraint still has all the advantages I mentioned before.[quote]To me, a useful key is something that if I found it yesterday with that value, I'll find it tomorrow with the same value, and I can use it to connect to data throughout the design's structure in place of the existing 'Primary Key' if I so chose. While this may not fall properly under the definition of generic db design standards (that I can't seem to get my hands on), it does fall under the definition of what myself and a number of DBAs I've worked with understand them to be in practical usage.[/quote]However, database [i]users[/i] would disagree with you. End users don't use surrogate keys to identify data, they use natural keys (actually I much prefer the term "business keys" but it means exactly the same thing). Therefore identifying and enforcing the business keys is normally essential for the users to make sense of the data and to get correct results from it. That includes keys that change. If keys are being used by the business process as identifiers then they are least as important as other keys that don't change - perhaps more so. Keys therefore need to be enforced and properly identified in the database if you want to ensure correct results from it.Furthermore, the distinction you are trying to make between changing and unchanging keys is ultimately impossible to determine. If all the key values on a row change then there is no sound basis for saying that [i]any[/i] of them "changed" - it is simply a different row because rows are identified by their keys. It is usually impractical to prevent data from being changed at some point during its lifetime and often you may not even know if it has been. Therefore the defining property of a key that really matters is uniqueness, not immutability.Thu, 14 Oct 2010 03:14:55 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxIt is usually preferable to use a natural key as opposed to a surrogate key in a relational Model. It depends on the situationIn a Dimensional Model you do not use the natural key a Surrogate key is used.I can provide additional clarification if necessary.Wed, 13 Oct 2010 22:25:08 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]David Portas (10/13/2010)[/b][hr]No it's not the same for the reasons I already stated: Constraints are standard SQL, unique indexes are not; Constraints are understood and used by more people; Some software tools will recognise keys declared as constraints but will not recognise those created using the CREATE INDEX syntax. In my view those are all very good reasons to use uniquness constraints and avoid creating unique indexes directly.[/quote]Here I feel we must disagree. Since finding a free copy of the 97 standard (or 99) has been dubious at best, I can't double check your comment, so I'll just assume it's accurate. Seems to make sense. However, I don't work in the SQL standard. I work in MS SQL Server and T-SQL. Because of that, and because an index can give me performance where a constraint will not, but still takes up the same system space and engine mechanics as a declared index under the hood, I would prefer the index.You make a good point though about 3rd party tools. I rarely use them so it rarely comes up as a difficulty for me, and that may be part of the difference.[quote]That's more or less what I thought you meant, but it's not technically correct. Two things make a key (aka "candidate key") and they are 1) uniqueness and 2) irreducibility.[/quote]...and is also known as the minimal superkey. I'm familiar with the theory. Practice, however, dictates other usage...[quote] Those are the two fundamental qualifications for a key. Stability is usually desirable as well but the fact that a key changes does not make it any less of a key than some other key that doesn't change. Of course that's just terminology I suppose, the effect is exactly the same whether you choose to call it a key or not - the same considerations apply. But I think it's worth saying because a lot of people do seem to get very confused about what keys are and why they are important.I'm avoiding the term "alternate key" because primary and alternate keys are really the same thing. All are candidate keys.[/quote]You are absolutely correct according to the dictionary, and theoretical design. I grant that the terminology is accurate, and I went and double checked just to make sure I remembered it correctly. :)This, perhaps, is where we are disconnecting in our discussion, because theoretical design is like the ISO standard. Nice to have, but not always practical. Identification of candidate keys are useless in practical design other then as the first step to identifying useful primary and alternate keys, or perhaps to assist in search mechanic selectivity to acquire more speed and better index usage.To me, a useful key is something that if I found it yesterday with that value, I'll find it tomorrow with the same value, and I can use it to connect to data throughout the design's structure in place of the existing 'Primary Key' if I so chose. While this may not fall properly under the definition of generic db design standards (that I can't seem to get my hands on), it does fall under the definition of what myself and a number of DBAs I've worked with understand them to be in practical usage.[EDIT:] Aaaaaand I finally look at the top of the screen and realize I'm in the "Relational Theory" Forum... I'll shaddup now. [/EDIT]Wed, 13 Oct 2010 16:37:16 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Craig Farrell (10/13/2010)[/b][hr][quote]In SQL Server a unique index on non-nullable columns is just a key by another name. Unique indexes and uniqueness constraints are simply two different ways to create keys. I would not recommend using a unique index instead of a uniqueness constraint. Constraints, not indexes, are the conventional, SQL standard and most obvious way to implement keys. [/quote]Same difference.[/quote]No it's not the same for the reasons I already stated: Constraints are standard SQL, unique indexes are not; Constraints are understood and used by more people; Some software tools will recognise keys declared as constraints but will not recognise those created using the CREATE INDEX syntax. In my view those are all very good reasons to use uniquness constraints and avoid creating unique indexes directly.[quote]Thus, while a unique [i]can be[/i] an alternate key, an alternate key [i]is always[/i] unique. The difference is not inconsequential, thus I separate the concept. The fact that some data is usually mobile in a 'natural key', say, like the company's name, is why I like surrogate keys, like identity fields. This means that if a user edits the company's name (which is unique, at least by state), we still haven't lost our unique locator that everything else hooks to it with.[/quote]That's more or less what I thought you meant, but it's not technically correct. Two things make a key (aka "candidate key") and they are 1) uniqueness and 2) irreducibility. Those are the two fundamental qualifications for a key. Stability is usually desirable as well but the fact that a key changes does not make it any less of a key than some other key that doesn't change. Of course that's just terminology I suppose, the effect is exactly the same whether you choose to call it a key or not - the same considerations apply. But I think it's worth saying because a lot of people do seem to get very confused about what keys are and why they are important.I'm avoiding the term "alternate key" because primary and alternate keys are really the same thing. All are candidate keys.Wed, 13 Oct 2010 16:07:40 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]David Portas (10/13/2010)[/b][hr]I doubt that a password would be a key. Most systems that I know of don't require passwords to be unique.[/quote]You're right, it was a bad example of the point regarding uniqueness.[quote]In SQL Server a unique index on non-nullable columns is just a key by another name. Unique indexes and uniqueness constraints are simply two different ways to create keys. I would not recommend using a unique index instead of a uniqueness constraint. Constraints, not indexes, are the conventional, SQL standard and most obvious way to implement keys. [/quote]Same difference. From: [url]http://msdn.microsoft.com/en-us/library/ms177420.aspx[/url][quote][b]The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. [/b]Therefore, if an attempt to insert a duplicate row is made, the Database Engine returns an error message that states the UNIQUE constraint has been violated and does not add the row to the table. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.[/quote][quote] What do you mean by a "non-keyed" index?[/quote]I meant non primary keyed and typed too fast and didn't re-read what I wrote to translate internal mental lingo into what would be definitive instead of vague. Yes, Unique columns/indexes/constraints can be used as 'alternate keys'. They're just rarely used as them, because usually the need for the constraint means that means the key can be changed, thus possibly destroying the key associations.Thus, while a unique [i]can be[/i] an alternate key, an alternate key [i]is always[/i] unique. The difference is not inconsequential, thus I separate the concept. The fact that some data is usually mobile in a 'natural key', say, like the company's name, is why I like surrogate keys, like identity fields. This means that if a user edits the company's name (which is unique, at least by state), we still haven't lost our unique locator that everything else hooks to it with.Of course, if you have the business' FedTaxID, or a SSN, that never changes, this is a proper natural key. Stability is incredibly important to a key, if not a primary factor in deciding to use it.Wed, 13 Oct 2010 15:32:17 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxI doubt that a password would be a key. Most systems that I know of don't require passwords to be unique.In SQL Server a unique index on non-nullable columns is just a key by another name. Unique indexes and uniqueness constraints are simply two different ways to create keys. I would not recommend using a unique index instead of a uniqueness constraint. Constraints, not indexes, are the conventional, SQL standard and most obvious way to implement keys. As far as I know unique indexes alone have no particular advantages over uniqueness constraints. Was there a reason why you mentioned unique indexes specifically? What do you mean by a "non-keyed" index?Wed, 13 Oct 2010 15:09:48 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]David Portas (10/13/2010)[/b][hr]Stability is usually a desirable property for a key and is certainly an important consideration but it isn't an absolute requirement. Sometimes it may be quite reasonable to implement keys that are unstable by design. One example is a login name attribute. The user may be allowed to change his login name at any time. The login name must remain unique though, so it's perfectly sensible to make it a key in the database.[/quote]David, I would agree with you in one way and disagree in another. I would say the login name is stable. Yes, it *could* be altered daily. Noone would though. Now, password, on the other hand, is an 'unstable' key. In theory it should be being altered by every user every 30 days. Not quite as unstable as, say, a LastUpdate field, but still unstable.So, while the ability to be unstable is there, I would say a login-name would be a stable field.To the other part of your statement (before I get shot for symantics about 'login-name'), Unique fields can be controlled very easily with a unique, non-clustered, non-keyed index and get out of the way of the real ones.Wed, 13 Oct 2010 14:53:16 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxTom, I agree with everything you wrote except your suggestion that keys should be stable:[quote]redo the model so that every relation has a stable natural key[/quote]Stability is usually a desirable property for a key and is certainly an important consideration but it isn't an absolute requirement. Sometimes it may be quite reasonable to implement keys that are unstable by design. One example is a login name attribute. The user may be allowed to change his login name at any time. The login name must remain unique though, so it's perfectly sensible to make it a key in the database.Wed, 13 Oct 2010 14:26:19 GMTsqlvogelhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Welsh Corgi (3/22/2010)[/b][hr]Listed below are some articles on why you should consider using a surrogate or an natural key.:-)[url]http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx[url]http://www.agiledata.org/essays/keys.html[/url][url]http://www.dbdebunk.com/page/page/626995.htm[/url][url]http://www.dbdebunk.com/page/page/626995.htm[/url][url]http://en.wikipedia.org/wiki/Surrogate_key[/url]:-)[/quote]The first URL quoted soesn't mention surrogate keys at all, the second returns an error (sometimes 404, but sometimes 500 so there may be something there sometimes), the last (wikipedia) is remarkably unenlightening (needs a rewrite: if I remember and have the time and the energy I may either edit it or put some suggestions into the talk page); that leaves the other URL (of which there are, for some reason, two copies in the list) as the only useful one (two?). I don't always agree with FP, but here I have to: there's only one sensible reaon for using a surrogate key, and that is that using the natural key is too messy (because it has too many colums, is too long) and makes things which are actually simple look complicated. There are of course two nonsensical reasons, which are often quoted as sensible reasons: (a) some of the columns in the natural key change quite often and (b) some relations have no natural key; (a) and (b) are nonsensical for the simple reason that if either occurs this is a clear indication that the entity modelling has been done incorrectly - the solution isn't to introduce spurious surrogate keys, but to redo the model so that every relation has a stable natural key (that of course doesn't mean that you have to use the natural key for an entity as the foreign key when referencing the entity: it may be more convenient to use a surrogate key to reduce apparent complexity).edit: spellingTue, 12 Oct 2010 10:58:11 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxSurrogate keys are always the answer if you are dealing with any successful Data Warehouse implementation. For instance take the case of any SCD transformation.Mon, 11 Oct 2010 23:58:57 GMTRaunak Jhawarhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspx[quote][b]Welsh Corgi (3/22/2010)[/b][hr]It many cases it is appropriate to use a surrogate key when you do not have a candidate key or the key would induce performance problems etc.[/quote]Being one of the "etc" the situation where doing dimensional modeling you want to take advantage of Oracle's star-transformation feature.Wed, 11 Aug 2010 11:29:35 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxGr8 !!! can you please post the summary of your learnings about surogate keys.Tue, 23 Mar 2010 00:34:21 GMTvidya_pandehttp://www.sqlservercentral.com/Forums/Topic887826-374-1.aspxI read an article today on this site today about Surrogate Keys.I attended my first relational database theory & design training in Princeton NJ.in 1988 and I'm familiar with the E. F. Codd & C.F Date Theories.I was very fortunate to have attended the class & my instructor was extremely intelligent and I will never forget her for I learned a lot from her. She was very interesting and as a result I developed a strong interest in Data Modeling & Database Design.But when Data Modeling I took what learned from the training and customized to make it work.I learned that something may look good on paper when implement it is often flawed and needs rework for it is not a solution to the problem.Despite the criticism of their theory, I learned a lot and I adapted their theory to deliver practical functional Database Applications. I attended the Oracle Data Modeling Course in 1997 and it was not so interesting because I had been performing Data Modeling on the Job for so long.It many cases it is appropriate to use a surrogate key when you do not have a candidate key or the key would induce performance problems etc.It has been my experience that it depends on the situation.I use Surrogate keys a lot but there are pros & cons.The decision should be based on the Business requirements, situation and a number of other factors. Listed below are some articles on why you should consider using a surrogate or an natural key.:-)[url]http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx[/url][url]http://www.agiledata.org/essays/keys.html[/url][url]http://www.dbdebunk.com/page/page/626995.htm[/url][url]http://en.wikipedia.org/wiki/Surrogate_key[/url]:-)Mon, 22 Mar 2010 21:18:06 GMTWelsh Corgi