MySQL外键-如何跨表强制一对一? [英] MySQL foreign keys - how to enforce one-to-one across tables?

问题描述

如果我在MySQL中有一个代表基类的表，并且有一堆代表派生类中字段的表，每个表都用外键引用回基表，那有什么办法使MySQL强制派生表和基表之间的一对一关系，还是必须在代码中完成?

If I have a table in MySQL which represents a base class, and I have a bunch of tables which represent the fields in the derived classes, each of which refers back to the base table with a foreign key, is there any way to get MySQL to enforce the one-to-one relationship between the derived table and the base table, or does this have to be done in code?

以下面的快速'n'脏模式为例，是否有任何方法可以获取MySQL以确保product_cd和product_dvd中的行不能共享相同的product_id?有没有更好的方法来设计架构以允许数据库强制执行这种关系，还是根本不可能?

Using the following quick 'n' dirty schema as an example, is there any way to get MySQL to ensure that rows in both product_cd and product_dvd cannot share the same product_id? Is there a better way to design the schema to allow the database to enforce this relationship, or is it simply not possible?

CREATE TABLE IF NOT EXISTS `product` (
    `product_id` int(10) unsigned NOT NULL auto_increment,
    `product_name` varchar(50) NOT NULL,
    `description` text NOT NULL,
    PRIMARY KEY  (`product_id`)
) ENGINE = InnoDB;

CREATE TABLE `product_cd` (
    `product_cd_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `product_id` INT UNSIGNED NOT NULL ,
    `artist_name` VARCHAR( 50 ) NOT NULL ,
    PRIMARY KEY ( `product_cd_id` ) ,
    INDEX ( `product_id` )
) ENGINE = InnoDB;

ALTER TABLE `product_cd` ADD FOREIGN KEY ( `product_id` ) 
    REFERENCES `product` (`product_id`) 
    ON DELETE RESTRICT ON UPDATE RESTRICT ;

CREATE TABLE `product_dvd` (
    `product_dvd_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `product_id` INT UNSIGNED NOT NULL ,
    `director` VARCHAR( 50 ) NOT NULL ,
    PRIMARY KEY ( `product_dvd_id` ) ,
    INDEX ( `product_id` )
) ENGINE = InnoDB;

ALTER TABLE `product_dvd` ADD FOREIGN KEY ( `product_id` ) 
    REFERENCES `product` (`product_id`) 
    ON DELETE RESTRICT ON UPDATE RESTRICT ;

@ Skliwz ，您能否提供更多有关如何使用触发器通过提供的架构强制执行此约束的详细信息?

@Skliwz, can you please provide more detail about how triggers can be used to enforce this constraint with the schema provided?

@ boes ，听起来不错.在您有孩子的情况下，它如何工作?例如，如果我们添加了product_movie并将product_dvd设为product_movie的子级?使product_dvd的检查约束也必须考虑所有子类型是否会成为可维护性的噩梦?

@boes, that sounds great. How does it work in situations where you have a child of a child? For example, if we added product_movie and made product_dvd a child of product_movie? Would it be a maintainability nightmare to make the check constraint for product_dvd have to factor in all child types as well?

推荐答案

要确保产品是cd或dvd，我将添加一个type列并将其作为主键的一部分.在派生列中，为该类型添加检查约束.在示例中，我将cd设置为1，并且可以为每个派生表设置dvd = 2，依此类推.

To make sure that a product is or a cd or a dvd I would add a type column and make it part of the primary key. In the derived column you add a check constraint for the type. In the example I set cd to 1 and you could make dvd = 2 and so on for each derived table.

CREATE TABLE IF NOT EXISTS `product` (
`product_id` int(10) unsigned NOT NULL auto_increment,
'product_type' int not null,
`product_name` varchar(50) NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`product_id`, 'product_type')
) ENGINE = InnoDB;

CREATE TABLE `product_cd` (
`product_id` INT UNSIGNED NOT NULL ,
'product_type' int not null default(1) check ('product_type' = 1)
`artist_name` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `product_id`, 'product_type' ) ,
) ENGINE = InnoDB;

ALTER TABLE `product_cd` ADD FOREIGN KEY ( `product_id`, 'product_type' ) 
REFERENCES `product` (`product_id`, 'product_type') 
ON DELETE RESTRICT ON UPDATE RESTRICT ;

这篇关于MySQL外键-如何跨表强制一对一?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！