Re: Foreign key problem
- From: paul c <toledobythesea@xxxxxxxx>
- Date: Wed, 14 Jun 2006 02:10:14 GMT
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
.
- References:
- Foreign key problem
- From: frebe73
- Re: Foreign key problem
- From: Tony Andrews
- Re: Foreign key problem
- From: Tony Andrews
- Foreign key problem
- Prev by Date: Re: Foreign key problem
- Next by Date: Re: Is SQL procedural or non-procedural?
- Previous by thread: Re: Foreign key problem
- Next by thread: Re: Foreign key problem
- Index(es):
Relevant Pages
|
