Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  ypercube Dec 19 '12 at 15:39
2  
foreign key doesn't make much sense for MyISAM engine... –  a1ex07 Dec 19 '12 at 15:44
    
@a1ex07 +1 ty - I will change that to InnoDB –  kfmfe04 Dec 19 '12 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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