how to deal with foreign keys with fixed values (on a db-table-basis)

Question

I have a control table that looks like this:

CREATE TABLE `tbl_control` (
  `tm_db_tbl` varchar(64) NOT NULL,
  `tm_key` int(11) unsigned NOT NULL,
  /* a bunch of other fields here */
  PRIMARY KEY (`tm_db_tbl`,`tm_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

From the perspective of my other tables, when I want to join on this control table, I join on the tm_key in my table along with a string literal designating my database and table against the tm_db_tbl field above. With the client table below, I would join WHERE ct.tm_db_tbl='my_db.some_client_table' AND ct.tm_key=sct.tm_key.

CREATE TABLE `some_client_table` (
  `tm_key` int(11) NOT NULL,
  /* a bunch of other fields here */
  PRIMARY KEY (`tm_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is a tiny bit of optimization so I don't have a tm_db_tbl column in my client table with all the same values. The relationship here is many-client-tables-to-one-control-table.

But now, I run into a bit of a problem when I want to define a foreign key on my client table because the corresponding tm_db_tbl is a string-literal.

Should I just bite-the-bullet and add tm_db_tbl filled all with the same value to my table or is there a better way around this problem?

If it makes any difference, I am using mysql.

Answer 1

The bottom line is that you can't define a foreign key constraint that uses anything other than just the columns in the primary key that you are referencing.

One of the ways that people sometimes use to consolidate keys across multiple tables is to use a GUID as the primary key. If the tm_key in each of your tables was a GUID then you could impose the foreign key using declarative referential integrity without carrying the tm_db_tbl column in your some_client_table.

Failing that, your options are:

1. Forego declarative referential integrity and just ensure that you have indexes that make your joins efficient.

2. Impose referential integrity procedurally.

3. Take ypercube's suggestion and find a more efficient way to represent some_client_table.tb_db_tbl.