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

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

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.

  • No time for a full answer now, but if you buy the bullet, convert the varchar(64) to an int (or even better smallint). I don't suppose you have more than 64K tables in there and 2 bytes vs 64 in an index is always good deal. Dec 19, 2012 at 15:39
  • 2
    foreign key doesn't make much sense for MyISAM engine...
    – a1ex07
    Dec 19, 2012 at 15:44
  • @a1ex07 +1 ty - I will change that to InnoDB
    – kfmfe04
    Dec 19, 2012 at 15:53

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.