Two foreign keys on the same column from one table

Question

i have a project table which has a image_id field and a newsimage_id field.

Both are linked to the image table. But InnoDB doesn't allow me to set a foreign key for both fields to the same column (id).

Is there a way I can do this or is it not possible? I'm using MySQL through MAMP.

Thanks in advance!!

score 6 · Accepted Answer · answered Apr 14 '10 at 14:20

6

Here's how I did it (MySQL 5.0.45):

ALTER TABLE `job_dependency`
ADD FOREIGN KEY (`job`) REFERENCES `job` (`id`),
ADD FOREIGN KEY (`dependency`) REFERENCES `job` (`id`);

There are problems with ON DELETE CASCADE in this situation, so don't use it.

answered Apr 14 '10 at 14:20

pzr

1

@prz: you did the trick! when i'm not using ON DELETE CASCADE it works. Thanks!!! – makeflo Apr 14 '10 at 14:26
do you guys know what the issue is w/ ON DELETE CASCADE here? – quickshiftin Dec 16 '11 at 20:55

1 Answers1