MySQL foreign keys - how to enforce one-to-one across tables?

Question

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?

Answer 1

Enforcing a 1:0-1 or 1:1 relationship can be achieved by defining a unique constraint on the foreign key's columns, so only one combination can exist. Normally this would be the primary key of the child table.

If the FK is on a primary or unique key of the referenced tables it will constrain them to values present in the parent and the unique constraint on the column or columns restricts them to uniqueness. This means that the child table can only have values corresponding to the parent in the constrained columns and each row must have a unique value. Doing this enforces that the child table will have at most one row corresponding to the parent record.

Answer 2

If you got rid of product-dvd-id and product-cd-id, and used the product-id as the primary key for all three tables, you could at least make sure that no two DVD or no two CD use the same product-id. Plus there would be less ids to keep track of.

And you maybe need some kind of type column in the products table.

Answer 3

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 ;