MySQL dilemma: composite unique key across tables

Question

I am attempting to implement an "extension" table structure for some stats that I am gathering from multiple sources.

My "parent" table looks something like this:

`test_parent` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `actions` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
)

My first "child" table looks somethinglike this (eventually I will have a child table for each source):

`test_child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_parent_id` int(11) unsigned NOT NULL,
  `external_id` int(11) NOT NULL,
  `external_actions` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `test_parent_id` (`test_parent_id`)
)
CONSTRAINT `test_child_ibfk_1` FOREIGN KEY (`test_parent_id`) REFERENCES `test_parent` (`id`)

All this will work fine in my implementation (I will be using Java/Hibernate); however, for the first child table I will need a composite unique key for external_id and date. I know that I cannot have a composite unique key across tables. I would rather not have one table to store all of the stats because the actual analytics I am collecting can vary greatly by source. I would be more open to getting rid of the "parent" table.

Is there some other way I can look at this problem? I am hoping to avoid using triggers to enforce uniqueness, if possible.

score 3 · Accepted Answer · answered Oct 20 '11 at 14:11

You need the date in the child table if you want to establish a unique constraint on it with external_id. You can also have date live in the parent table, and reference it via the foreign key. That will allow you to support date differently by other child tables in the future.

CREATE TABLE `test_parent` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `actions` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`, `date`)
);

CREATE TABLE `test_child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_parent_id` int(11) unsigned NOT NULL,
  `date` date NOT NULL,
  `external_id` int(11) NOT NULL,
  `external_actions` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_parent_id` (`external_id`,`date`),
  CONSTRAINT `test_child_ibfk_1` FOREIGN KEY (`test_parent_id`, `date`) 
    REFERENCES `test_parent` (`id`,`date`)
);

score -1 · Answer 2 · answered Oct 20 '11 at 14:05

-1

Move the date field to the child table and declare a unique key:

ALTER TABLE child ADD UNIQUE INDEX parent_date (parent_id, `date`);

answered Oct 20 '11 at 14:05

Johan

74,508
24
191
319

I'd rather not, all children would need date. But this can be a reasonable compromise between what I ideally want and completely axing the parent table. – smp7d Oct 20 '11 at 14:35

MySQL dilemma: composite unique key across tables

2 Answers2