Foreign key problem
Date: 12 Jun 2006 22:19:16 -0700
Message-ID: <1150175956.098492.15000_at_i40g2000cwc.googlegroups.com>
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)
My problem is the last table, fk_column. How can I define a foreign key for fk_column.fk_column (and pk_column) referencing the table columninfo? The column table_name is not an member of the table fk_column, but it could easily be joined from the fk table, but I don't think that helps me with defining the foreign key constraint?
The corresponding information_schema tables in MySQL looks like this
(simplified):
table_constraints(*constraint_name, table_name)
key_column_usage(*contraint_name, *table_name, *column_name,
referenced_table_name, referenced_column_name)
In this case it is possible to define foreign key constraints, but I don't consider these tables normalized. The value of table_name is given by the constraint_name and should not be repeated in the second table.
Somebody that have a pice of advice?
Fredrik Bertilsson Received on Tue Jun 13 2006 - 07:19:16 CEST