Re: Separate PK in Jxn Tbl?

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> wrote in message news:uTvWO%23%23XIHA.1532_at_TK2MSFTNGP04.phx.gbl...
> To that, I would add that the increased simplicity of using a surrogate
> (or artificial or autonumber) key as the primary key in place of a
> composite key is only half their advantage.
>
> The biggest problem that I have with composite keys is that they share the
> same fundamental problem as natural keys: using them as the primary key is
> allowing the fact that a primary key can change its value over time.
> IMHO, a primary key should never be allowed to change its value once it
> has been created; a assumption which will forbid the use of a composite
> key in many cases. (Of course, if you don't mind to see a primary key
> changing its value after its creation then you are not concerned by this
> argument.).

I have decided not to respond to this post in detail because there isn't a single point it makes that I agree with (as stated). I really don't know where to start. One specific comment I will make is that my progression has been the opposite of yours. I once used synthetic keys everywhere, but now try to limit my use of them, with wholly beneficial effects. That may be why I write about this with the fervor of a born-again convert.

I would have a lot more sympathy for these kinds of claims if the people making them would give any hint that they know what the alternatives are, and why their solutions make sense within application development tools. For example, I don't think I've ever seen anyone enthusing about the liberal use of synthetic keys who also noted that they are aware of the possibility of declaring a foreign key constraint WITH ON UPDATE CASCADE. If you can persuade me you've looked at it and had to reject for reasons X, Y, and Z, I can respect that. Or tell me you know about it but your particular product doesn't support it and I can respect that. Or tell me that you understand that the DBMS handles the problem almost trivially but the application development tools make you write extra code and I can respect that.

And what is all this tripe about composite keys making the SQL more complex? If I bodge up my tables with a spurious third synthetic key (skey) so that instead of writing:

select i.description, b.cause_of_damage
from orderitems i left join breakages b
using (ordernr,itemnr)

I can instead write:

select i.description, b.cause_of_damage
from orderitems i left join breakages b
on i.skey = b.skey

How much easier is THAT?? And at what cost?

If you don't convince me that you properly understand the problems, and crucially, where the problems *really* lie, then you aren't going to convince me that your solutions are anything but cut-and-paste rote-learned hackery that seems elegant/sensible only to those with limited knowledge of very limited products. You have to establish that you are credible. Merely claiming years and years of experience (as others have) could just mean they've been successfully getting away with being incompetent--and goodness knows, that happens, so they won't get the benefit of the doubt.

Roy Received on Sat Jan 26 2008 - 11:08:27 CET