Re: Foreign key problem

From: mAsterdam <mAsterdam_at_vrijdag.org>
Date: Tue, 13 Jun 2006 20:48:59 +0200
Message-ID: <448f0885$0$31652$e4fe514c_at_news.xs4all.nl>


Kenneth Downs wrote:
> 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)

This rules out more than one FK in one table referencing one other. Use /roles/ to name your FK's.

( possible with OP's "fk(*fk_name, pk_table, fk_table)" )

> 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.
Received on Tue Jun 13 2006 - 20:48:59 CEST

Original text of this message