Posted by Hugo Kornelis on 10/01/76 11:42

On 15 Mar 2006 14:17:11 -0800, pb648174 wrote:

>I think we are getting a little off topic from the original discussion,
>but I was thinking that you guys were saying to not use the identity at
>all, or to maybe only use it as a secondary method of getting at the
>data. But so far, you have proposed making the identity the primary key
>(which it is), using it as the foreign key (which it is) and putting a
>unique constraint on things that you wish to enforce uniqueness for
>(which I already do).

Hi pb648174,

I'd like to add to this that each table that uses an IDENTITY for it's
PRIMARY KEY, MUST also have at least one UNIQUE constraint on it's real
business key. The IDENTITY column should _NEVER_ be the only key.

>The numbering issue is just not a problem, when users need their own
>numbers we just let them type them in. 99% of the time we don't let
>users see the identity values, but every now and then we do, such as
>User Ids, just so we have a quick way to talk about a particular thing.

I think you should never expose your surrogate keys - and your example
of userids is about the worst possible example! Are you aware that
making it easy for users to guess how other userids look makes it easier
for hackers to get in?

Suppose I place an order with UPS and receive a tracking no 110056123.
I'd think nothing of it. But if I place another order the neext day and
the new tracking number is 110057044, I'll realise that all numbers in
between will probably be valid tracking numbers as well. And that gives
me an advantage if I should decide to try and spy on someone else's
shipment.

>Almost all of our tables have an identity as the primary key or some
>combination thereof and so far I haven't heard any reason not to do
>that. You are just saying to also put a UNIQUE constraint on the
>columns that need to be unique... not a problem.

Here's the second error. Using an IDENTITY surrogate key should never be
an automated action. It should be a deliberate choice. Always start with
the business key. If it's short (1, 2, or maybe 3 columns and no long
char or varchar columns) and stable (little to no chance of change once
it's in use), then you're already done. If it's not short but stable and
there are little (or no) references to this table, then you're also
already done.

You should only use a surrogate key if the business key is either not
stable, or both long (3 or more columns, and/or long [var]char data) and
referenced in other tables.

>My main point was just trying to determine how set you were on the
>evilness of the identity column and so far what you are saying to do is
>what we are currently doing...

An identity columnn is no more evil than a chainsaw. When used properly
and with care, it does it's job very good. But in the hands of the
inexperienced, it can quickly become very dangerous.

--
Hugo Kornelis, SQL Server MVP

[Back to original message]