unique constraint

Question

unique constraint

Viewing 3 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Lynn Pettis (8/10/2012)
...
I like the idea of having a sid (a surrogate id) on my tables, even if I do have a natural key that can serve as a primary key. the problem I have run into with natural keys is when the business rules change and those changes cause the natural key to no longer be unique. The sid gives you an alternate key that uniquely identifies each row in the table.

Same here. Experience takes precedence over theory.

Although IMO it's rare to find a table which doesn't have a column or selection of columns which could constitute a PK (i.e. a candidate key), even when it's only one or two columns my first choice will be a surrogate key. I got bitten too many times developing in Foxpro, a language/"database" which promoted natural keys over surrogate keys and which didn't even have an auto-incrementing column until the last version (even then it was something of a workaround).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

...
Same here. Experience takes precedence over theory.
...

Actually, you wouldn't even need a lot of experience to choose surrogate PK over natural key. Use common sense or apply pure mathematical logic - the incremented whole number is guaranteed to be unique as n+1 <> n where n is whole number.

SQL Server not only have identity functionality for auto-incremented values, it now followed Oracle in implementing sequences, why they did it? ...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Eugene Elutin (8/13/2012)
...
Same here. Experience takes precedence over theory.
...
Actually, you wouldn't even need a lot of experience to choose surrogate PK over natural key. Use common sense or apply pure mathematical logic - the incremented whole number is guaranteed to be unique as n+1 <> n where n is whole number...

I reckon it's a little optimistic to expect an arbitrary attribute of a business to double up as a critical attribute of a database. Business attributes are database content.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden