MySQ Lforeign keys-如何执行跨表一对一？
MySQ Lforeign keys-如何执行跨表一对一？
提问于 2018-07-26 01:37:41
有以下MySQL代码：
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 ;如何使用触发器来使用所提供的模式强制执行此约束？
Stack Overflow用户
发布于 2018-07-26 10:32:44
添加一个类型列并使其成为主键的一部分，在派生列中，为类型添加一个CHECK约束：
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 ;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100000424
复制相关文章
相似问题