MySQL Forums
Forum List  »  InnoDB

multiple foreign keys to multiple tables
Posted by: Nathanael Noblet
Date: May 19, 2008 01:57PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
multiple foreign keys to multiple tables
29542
May 19, 2008 01:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.