UNIQUE index spanning multiple tables

Question

I have the following schema. How do I ensure that all values in child are unique for a given child.group_id, child.type, and parent.status? Please note the 1-to-1 relationship between parent and child. If parent and child was one table, a simple UNIQUE index would work, however, I wish to keep the two tables separate. Ideally, stored procedures wouldn't be used, however, I am open to them if necessary. I am using the latest version of MySQL. Thank you

CREATE  TABLE IF NOT EXISTS group (
  id INT NOT NULL AUTO_INCREMENT ,
  moreData VARCHAR(45) NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS parent (
  id INT NOT NULL AUTO_INCREMENT ,
  status VARCHAR(45) NOT NULL ,
  moreData VARCHAR(45) NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS child (
  parent_id INT NOT NULL ,
  group_id INT NOT NULL ,
  type INT NOT NULL ,
  moreData VARCHAR(45) NULL ,
  PRIMARY KEY (parent_id) ,
  INDEX fk_child_group1_idx (group_id ASC) ,
  CONSTRAINT fk_child_parent
    FOREIGN KEY (parent_id )
    REFERENCES parent (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_child_group1
    FOREIGN KEY (group_id )
    REFERENCES group (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Answer 1

I think you're looking for something along these lines.

Create an overlapping constraint in "parent". (The column "id" is unique, so {id, any-other-column} must also be unique.)

CREATE  TABLE IF NOT EXISTS parent (
  id INT NOT NULL AUTO_INCREMENT ,
  status VARCHAR(45) NOT NULL ,
  moreData VARCHAR(45) NULL ,
  PRIMARY KEY (id),
  UNIQUE (id, status) 
)
ENGINE = InnoDB;

Add the status column in "child".

CREATE  TABLE IF NOT EXISTS child (
  parent_id INT NOT NULL ,
  parent_status VARCHAR(45) NOT NULL ,
  group_id INT NOT NULL ,
  type INT NOT NULL ,
  moreData VARCHAR(45) NULL ,

Primary key constraint doesn't have to change.

  PRIMARY KEY (parent_id) ,
  INDEX fk_child_group1_idx (group_id ASC) ,

Reference the pair of columns.

  CONSTRAINT fk_child_parent
    FOREIGN KEY (parent_id, parent_status )
    REFERENCES parent (id, status )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_child_group1
    FOREIGN KEY (group_id )
    REFERENCES group (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION) ,

Whether you ought to cascade updates in fk_child_parent is application-dependent. Give that some thought.

And add a unique constraint on the set of columns you say should be unique.

  CONSTRAINT uq_child
    UNIQUE (group_id, type, parent_status)
    REFERENCES group (id)) 
 ENGINE = InnoDB;

Answer 2

One option is to create before insert trigger to do this verification, check constraints are not supported by mysql so that's not an option.