Re: Separate PK in Jxn Tbl?

Your argument about the use of a DRI WITH ON UPDATE CASCADE is an interesting argument and one that come back often; however it's not a silver buller. First of all, it's another level of complexity that you must add to the design of your database; ie, you must make sure that they are all there and no one is missing. Second, this DRI cannot be used with cyclic relationship with SQL-Server but with Oracle, you can. (From your example, I believe that you are working with Oracle). On SQL-Server, you must use triggers to implement such a feature when there is a cyclic relationship. Of course, when you are dealing with tens and hundreds of relationships, this can quickly translate into a nightmare. There is also the qestion of the diminution of performance and of general design: when you have to update multiples records on multiple tables for what should be the change of a single value in a single table make it hard to believe that this is a proper normalized database design and this situation quickly worsen if you have to take into account the correspondance with backups, reports and linked databases; all systems for which there is no automatic DRI.

But why make it simpler when you can make it harder?

Finally, I don't understand your example at all. You are introducing us to the NATURAL JOIN and USING statement that have been introduced by Oracle in its 9i version (also in MySQL and Postgres, I believe) but I fail to see what this has to do with the subject of this thread; the use of a separate PK in a junction table and its highly related topic, ie. the use of natural keys versus the use of surrogate keys. There is no relationship at all between a NATURAL JOIN and a natural key and the Natural Join can be used as easily with a surrogate key than with a natural key. The only thing that is important with the Natural Join is the name of the key. (BTW, if you were to ask me what I'm thinking about this little monstruosity, I would tell you that this is a perfect example of a Pandora box.).

And finally, a for your request of asking me to convince you that I'm properly understand the problem here of to etablish that I'm credible: I can tell you that I have absolutely no intention of doing it and that I have absolutely no interest at all about what you are thinking of me. The only things that are of interest to me are the arguments that I'm seeing posted here - whatever the people who might write them - but for someone who has just make a confusion between a natural key and the NATURAL JOIN, asking for such a thing make it looks very strange.

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Roy Hann" <specially_at_processed.almost.meat> wrote in message 
news:w5adnaS22JoBmwbanZ2dnUVZ8uadnZ2d_at_pipex.net...


> "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

>