mysql备份外键丢失_MySQL外键约束消失

This is my table:

/* oefenreeks leerplan */

CREATE TABLE leerplan_oefenreeks (

leerplan_oefenreeks_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,

leerplan_id INT NOT NULL,

oefenreeks_id INT NOT NULL,

plaats INT NOT NULL

);

/* fk */

ALTER TABLE leerplan_oefenreeks ADD CONSTRAINT fk_leerp_oefenr_leerplan

FOREIGN KEY(leerplan_id) REFERENCES leerplan (leerplan_id) ON DELETE CASCADE;

ALTER TABLE leerplan_oefenreeks ADD CONSTRAINT fk_leerp_oefenr_oefenreeks

FOREIGN KEY(oefenreeks_id) REFERENCES oefenreeks (oefenreeks_id) ON DELETE CASCADE;

/* when I execute the nexline, my fk_leerp_oefenr_leerplan constraint vanishes/disappears*/

ALTER TABLE leerplan_oefenreeks ADD CONSTRAINT un_leerp_oefenr UNIQUE(leerplan_id, oefenreeks_id);

ALTER TABLE leerplan_oefenreeks ADD CONSTRAINT un_leerp_oefenr_plaats UNIQUE(leerplan_id, plaats);

When I go and check only 3 constraints exist. fk_leerp_oefenr_leerplan disappears. I don't understand why this happens.

解决方案

Just to clarify things, this is how the table looks like after the foreign key constraints have been put in place:

CREATE TABLE `leerplan_oefenreeks` (

`leerplan_oefenreeks_id` int(11) NOT NULL AUTO_INCREMENT,

`leerplan_id` int(11) NOT NULL,

`oefenreeks_id` int(11) NOT NULL,

`plaats` int(11) NOT NULL,

PRIMARY KEY (`leerplan_oefenreeks_id`),

KEY `fk_leerp_oefenr_leerplan` (`leerplan_id`),

KEY `fk_leerp_oefenr_oefenreeks` (`oefenreeks_id`),

CONSTRAINT `fk_leerp_oefenr_oefenreeks` FOREIGN KEY (`oefenreeks_id`) REFERENCES `oefenreeks` (`oefenreeks_id`) ON DELETE CASCADE,

CONSTRAINT `fk_leerp_oefenr_leerplan` FOREIGN KEY (`leerplan_id`) REFERENCES `leerplan` (`leerplan_id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

Notice the fk_leerp_oefenr_leerplan and fk_leerp_oefenr_oefenreeks indexes mysql adds automatically.

After you add the first unique key constraint :

ALTER TABLE leerplan_oefenreeks ADD CONSTRAINT un_leerp_oefenr UNIQUE(leerplan_id, oefenreeks_id);

Mysql removes the fk_leerp_oefenr_leerplan index as it is no longer needed to support the foreign key checks on leerplan_id column. At this point, the table looks like this:

CREATE TABLE `leerplan_oefenreeks` (

`leerplan_oefenreeks_id` int(11) NOT NULL AUTO_INCREMENT,

`leerplan_id` int(11) NOT NULL,

`oefenreeks_id` int(11) NOT NULL,

`plaats` int(11) NOT NULL,

PRIMARY KEY (`leerplan_oefenreeks_id`),

UNIQUE KEY `un_leerp_oefenr` (`leerplan_id`,`oefenreeks_id`),

KEY `fk_leerp_oefenr_oefenreeks` (`oefenreeks_id`),

CONSTRAINT `fk_leerp_oefenr_leerplan` FOREIGN KEY (`leerplan_id`) REFERENCES `leerplan` (`leerplan_id`) ON DELETE CASCADE,

CONSTRAINT `fk_leerp_oefenr_oefenreeks` FOREIGN KEY (`oefenreeks_id`) REFERENCES `oefenreeks` (`oefenreeks_id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

Long story short, Sqlyog only shows the indexes in it's tree view at the left, if you want to see the foreign keys, select the table in the tree view and press F10.