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?

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, can you please provide more detail about how triggers can be used to enforce this constraint with the schema provided?

@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?

解决方案

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 ;