Yes this topic causes wars<g> but here is my take on your question. If you
have to work that hard to find a natural key then it usually is not the best
thing for this. Going to extremes with natural keys can cause the compound
key to be very large. The larger it gets the more bersome it is to work
with. Why propagate a 30,40 or more character key to many child tables when
you can just use a 4 byte INT instead? That large value is made up of many
smaller columns that are typically of no use logically in the children
tables. It gets even worse if you want to cluster the Key whether it be the
Primary on the Foreign ones.
Andrew J. Kelly
SQL Server MVP
"Donald Halloran" <archonATTquantumfireDOTTcom> wrote in message
news:3f0418ffduster.adelaide.on.net...to> Another issue arises under my favourite topic of discussion!
> What solutions do people have for creating a "natural" key that is both
> meaningful, and unique.
> Consider a situation where people create "things" that will be stored as
> unique entities, but where the user themselves have not predefined a
> natural, unique identifier. In other words, it is left up do the databaseit> create an identifier (heck, every "natural" key must have been created by
> someone or something at some point, right?)
> Well, a natural key might be defined at least partly by who is submittingof> and when. As a quick example, a table created in 2003 might be keyed as
> The idea is to have as much of the key as possible be meaningful, the x
> is there merely to guarantee uniqueness.
> Is there any way to efficiently and safely implement creating the unique
> (x) part of the key?
> By "efficient", I mean without causing serialized inserts. Is an insteadidentity> insert trigger the best solution (using either a temp table with ancould> and using that, or using the max+1 method (which I would rather avoid...
> like the plague)? Moreover, is there any _standard_ solution? (Are IOI
> triggers sql server specific?)
> By "safe", I mean that relying on serial inserts is surely bad. OK, wetier> have a stored proc to insert records one at a time, acting as a middleand> key generator. But what happens when Mr dbo comes along in a year's timea> performs a basic insert statement against the table from some other table?
> Or more generally, for some reason in the future a set based insert
> operation is required?
> As an almost total tangent, I have seen a few posts which recommend usingrecord> datetime key... eg this from JC...
> CREATE TABLE Foobar
> (trans_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
> PRIMARY KEY,
> product_id INTEGER NOT NULL,
> quantity INTEGER NOT NULL,
> Surely this fails Joe's own test regarding set based inserts as every> in the inserted set will get the same timestamp.