Re: Foreign key problem

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

Standard SQL permits constraints of that form (more or less), but most DBMS products do not. If it were permitted then a more complex constraint could be built such as:

CHECK (NOT EXISTS
(SELECT fk.table_name, fkc.column_name FROM fk  JOIN fk_columns fkc ON fkc.constraint_name = fk.constraint_name  MINUS
 SELECT col.table_name, col.column_name FROM columns))

In the absence of such syntax we have to make do with what we have. Sometimes it is possible to use Materialized Views like this:

CREATE MATERIALIZED VIEW fk_column_mv AS SELECT fk.table_name, fkc.column_name FROM fk JOIN fk_columns fkc ON fkc.constraint_name = fk.constraint_name MINUS
SELECT col.table_name, col.column_name FROM columns

ALTER TABLE fk_column_mv
ADD CONSTRAINT fkc_col
CHECK (table_name IS NULL);

If the materialized view is set up so that it is always refreshed on commit, then the constraint on the MV effectively acts as a foreign key on table fk_columns, by preventing any rows from being added to fk_column that would result in a row being added to the MV. Received on Tue Jun 13 2006 - 13:55:39 CEST