Re: Foreign key problem

From: paul c <toledobythesea_at_oohay.ac>
Date: Wed, 14 Jun 2006 02:10:14 GMT
Message-ID: <aeKjg.23530$Mn5.22838_at_pd7tw3no>


Tony Andrews wrote:
> Tony Andrews wrote:

>> Your fk_column table *needs* table_name because it is part of the
>> primary key of the parent table columninfo.  This is not
>> denormalisation, any more than having column_name in 2 tables is
>> denormalisation, because the table_name is not redundant: it cannot be
>> derived from the column_name alone.  Add it with impunity!

>
> I now wish to object to my own previous post! As you say, the table
> name can be derived from the constraint name, and so it is redundant to
> hold it again in the fk_column table. Adding it there is commonly done
> simply because the types of inter-table constraints our DBMSs allow us
> to make are somewhat limited - i.e. it is usually a foreign key or
> nothing.
>
> One can therefore forget that a foreign key is nothing more than a
> handy shorthand for a check contraint (or "assertion") something like:
>
> CHECK (NOT EXISTS
> (SELECT child.parent_id FROM child
> MINUS
> SELECT parent.id FROM parent))
> ...

I go along with your comment about shorthand. In TTM's Algebra I imagine the constraint might be something like:

{child.parent_id} <AND> {parent.parent_id} = {child.parent_id} which seems even simpler to me, or in c-style: if ({child.parent_id} <AND>= {parent.parend_id} == TRUE)...

Fredrik's original schema was:

tableinfo(*table_name)
columninfo(*table_name, *col_name)
fk(*fk_name, pk_table, fk_table)
fk_column(*fk_name, *fk_column, pk_column)

I think one could also view table fk(*fk_name, pk_table, fk_table) by itself as nothing more than a constraint that limits the referenced and referencing columns to one table each. But I ask why? Without it, I think one could instead define fk_column along the lines of the mentioned mysql table:

fk_column(*fk_name, *pk_table, *pk_column, fk_name, fk_column) which seems normalized but could allow a row such as (in a loose notation) "fkname,notpktablename,notpkcolumnname,fktablename,fkcolumnname" where the values notpktablename and notpkcolumnname happen to be defined table and column names respectively.

Some might think such a schema non-sensical, perhaps because of associating foreign key constraints as some action that happens at 'insert time'. It is sometimes useful to ask when will the dbms do certain things but I wonder if this is one of those times. In fact, I question whether one really needs the fk_name column, given that the definition is just shorthand for telling the implementation to invoke some action in SQL or in some other lingo, when it sees fit to.

(I've thought about this off and on for half a day, so if it's nonsense at least I haven't wasted a whole day on it!)

I'll admit that when I suggest discarding the fkname column it is because I am something of an extremist, at least relative to db people I know. While I can see the advantage for system utilities in naming the fk constraint, ie., less code for their authors to write, I like to avoid adding anything I know the biz users don't have a need for.

(If anybody got this far, I guess I should mention that none of this has anything to do with pk_column being a primary key column - I've never understood why the referenced column(s) must be 'primary', seems arbitrary to me. Maybe I'm just saying in a long-winded way that 'foreign keys' as a notation don't seem to be intrinsic to the rm.)

p Received on Wed Jun 14 2006 - 04:10:14 CEST

Original text of this message