Re: Foreign key problem

frebe73_at_gmail.com wrote:

> I am working on an application which need a data model that reflects
> the schema structure. My tables are ("*" indicates primary key column):
>
> tableinfo(*table_name)
> columninfo(*table_name, *col_name)
> fk(*fk_name, pk_table, fk_table)
> fk_column(*fk_name, *fk_column, pk_column)
>

I can tell you how we did it, and perhaps this will help.

Our tables look more like this:

TABLEINFO (*table_name)
COLUMNINFO (*table_name, *col_name)
FK (*table_name,*table_name_par)

TABFLAT(*table_name,*col_name)

A table is defined by making entries into either COLUMNINFO or FK. You put non-FK columns into a table by making entries into COLUMNINFO.

When an entry is made into FK, it establishes a foreign key but it also adds columns to the child table. The entry in FK signifies that the primary key columns from the parent table should become part of the structure of the child table, and should have RI to the parent table. Therefore you do not need to make entries into COLUMNINFO because the column definitions are *implicit* in the entry in FK.

The fourth table, TABFLAT, is a derived table that contains the flattened definitions of all tables. You must generate this table.

This approach allows us to have very tight definitions of tables. By convention we only make foreign keys to primary keys (not to other unique indexes), though others may wish to specify that a FK hits a different unique key on the parent table.

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)