Quoting Roman Neuhauser <[EMAIL PROTECTED]>:
> # [EMAIL PROTECTED] / 2003-09-25
> 14:59:33 +0100:
> > CREATE TABLE `pheno` (
> > `id` smallint(5) unsigned NOT NULL
> auto_increment,
> > `relevant` enum('y','n') default NULL,
> > `phenotype` varchar(50) NOT NULL default
> '',
> > PRIMARY KEY (`id`,`phenotype`),
> > KEY `id` (`id`),
> > CONSTRAINT `0_125` FOREIGN KEY (`id`)
> REFERENCES
> > `monogenic` (`phenotype_ID`) ON DELETE
> CASCADE,
> > CONSTRAINT `0_127` FOREIGN KEY (`id`)
> REFERENCES
> > `knockout` (`phenotype_ID`) ON DELETE
> CASCADE
> > ) TYPE=InnoDB
> >
> >
> > The problem is that I can't insert a record
> into this
> > table unless the value of 'id' is present in
> both the
> > mongenic and knockout tables. I receive the
> following error:
> >
> > Cannot add or update a child row: a foreign
> key
> > constraint fails
>
> what did you expect to happen instead, or,
> what did you expect the
> two FK's to do instead?
>
> --
> If you cc me or remove the list(s) completely
> I'll most likely ignore
> your message. see
> http://www.eyrie.org./~eagle/faqs/questions.html
>
I wanted the foreign key in the pheno table to reference
table monogenic OR knockout. Otherwise I have to
duplicate the pheno table - one copy with a foreign key
referencing knockout and the other referencing the
monogenic table. I'd rather not have to duplicate the
tables, but it looks like this is the only way as this
is how foreign keys work
cheers
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
