Re: Foreign key problem
Date: Tue, 13 Jun 2006 20:48:59 +0200
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)" )
> 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