Re: Foreign key problem

I didn't pay much attention to this until the thread got surprisingly long.

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

The schema-before-model bit confuses me; are you really stuck in this circumstance?.

> tableinfo(*table_name)
> columninfo(*table_name, *col_name)
> fk(*fk_name, pk_table, fk_table)
> fk_column(*fk_name, *fk_column, pk_column)

All one really needs - using your notation - is

  tables (*table_name)
  columns (*table_name, *col_name)
  foreign_keys (*fk_table_name, *fk_column,

                *pk_table_name, *pk_column)

You want to glue names to these? You might need

  constraints (name*)

and a column in foreign_keys constrained to hold only values which appear in constraints.name. name, then, is not a determinant and not a key; it's just a name:

  foreign_keys (*fk_table_name, *fk_column,

                *pk_table_name, *pk_column,
                name)

Or, as was done in the product you mentioned,

  table_constraints (*name, *table_name)

constraining values foreign_keys.name to those held in table_constraints.name and values in table_constraints.table_name to those held in tables.table_name.

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

As you've presented it, I'd say fk_column is a projection of my foreign keys.

> 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 Thu Jun 15 2006 - 00:48:50 CEST