MySQL foreign key constraint disappearing

Question

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.

Answer 1

In addition to Konerak's suggestion, note that foreign key constraints are not created when using MyISAM, which is MySQL's default storage engine. You can use InnoDB instead, by specifying it in the CREATE TABLE statement:

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 
) ENGINE=InnoDB;

Test Case with default storage engine, MyISAM:

CREATE TABLE t1 (id int PRIMARY KEY);
CREATE TABLE t2 (id int, t1_id int);

ALTER TABLE t2 ADD CONSTRAINT fk_t1t2 
FOREIGN KEY(t1_id) REFERENCES t1 (id) ON DELETE CASCADE; 

SELECT   *
FROM     information_schema.KEY_COLUMN_USAGE
WHERE    table_name ='t2' AND
         constraint_name <>'PRIMARY' AND referenced_table_name is not null;

Empty set (0.08 sec)

Test Case with InnoDB storage engine:

CREATE TABLE t1 (id int PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (id int, t1_id int) ENGINE=InnoDB;

ALTER TABLE t2 ADD CONSTRAINT fk_t1t2 
FOREIGN KEY(t1_id) REFERENCES t1 (id) ON DELETE CASCADE; 

SELECT   *
FROM     information_schema.KEY_COLUMN_USAGE
WHERE    table_name ='t2' AND
         constraint_name <>'PRIMARY' AND referenced_table_name is not null;

+--------------------+-------------------+-----------------+---------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG |
+--------------------+-------------------+-----------------+---------------+
| NULL               | test              | fk_t1t2         | NULL          |
+--------------------+-------------------+-----------------+---------------+  ...
1 row in set (0.00 sec)

Answer 2

Try this way:

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

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

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; 

EDITED After OP's comment

I've tried your code and your result did not replicate. I simplified the leerplan and oefenreeks tables:

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 
) engine=innodb;


CREATE TABLE leerplan ( 
    leerplan_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, 
    leerplan_data            INT NOT NULL
)engine=innodb;


CREATE TABLE oefenreeks ( 
    oefenreeks_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, 
    oefenreeks_data            INT NOT NULL
)engine=innodb;

Then I ran all the alter table statements, and after it :

show create table leerplan_oefenreeks;


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`),
  UNIQUE KEY `un_leerp_oefenr_plaats` (`leerplan_id`,`plaats`),
  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

I'm using: Server version: 5.1.39-log MySQL Community Server (GPL) 64bit, kubuntu 9.04.

What else could it be different on your side?

Answer 3

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.