mysql - 跨多个表的 UNIQUE 索引

Question

我有以下架构。对于给定的 child.group_id、child.type 和 parent.status，如何确保 child 中的所有值都是唯一的？请注意父子之间的一对一关系。如果 parent 和 child 是一个表，一个简单的 UNIQUE 索引就可以工作，但是，我希望将两个表分开。理想情况下，不会使用存储过程，但是，如有必要，我愿意接受它们。我正在使用最新版本的 MySQL。谢谢

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

我认为您正在寻找类似的东西。

在“parent”中创建重叠约束。 (“id”列是唯一的，因此 {id, any-other-column} 也必须是唯一的。)

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;

在“ 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 (parent_id) ,
  INDEX fk_child_group1_idx (group_id ASC) ,

引用这对列。

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

是否应该在 fk_child_parent 中级联更新取决于应用程序。考虑一下。

并对您认为应该唯一的列集添加唯一约束。

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