multiple foreign keys to multiple tables
I'm wondering if what I am attempting is at all possible. I created the following test schema to illustrate
CREATE TABLE `general` (
`id` int(11) NOT NULL auto_increment,
`otype` tinyint(4) NOT NULL,
`title` varchar(32) NOT NULL,
PRIMARY KEY (`id`,`otype`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `news` (
`id` int(11) NOT NULL auto_increment,
`otype` tinyint(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`,`otype`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `images` (
`id` int(11) NOT NULL auto_increment,
`otype` tinyint(4) NOT NULL,
`owner_id` int(11) default NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`,`otype`),
KEY `new_fk_constraint1` (`owner_id`,`otype`),
CONSTRAINT `new_fk_constraint1` FOREIGN KEY (`owner_id`, `otype`) REFERENCES `general` (`id`, `otype`) ON DELETE CASCADE,
CONSTRAINT `new_fk_constraint2` FOREIGN KEY (`owner_id`, `otype`) REFERENCES `news` (`id`, `otype`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
if I have the following in the general and news tables
general:
id, otype, title
1, 1, gen1
2, 1, gen2
news:
id, otype, name
1, 2, news1
2, 2, news2
I'd like to insert as follows
INSERT INTO images (otype, owner_id, name) VALUES (1,1,'gen1');
which of course fails because only one of the constraints match, and thus the other doesn't and so it dies. Is there a way to tell MySQL/InnoDB that a group of FK/constraints are in an OR relationship?? fk1 or fk2?