All geek questions in one place

MySQL foreign key constraint disappears

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, there are only 3 limitations. fk_leerp_oefenr_leerplandisappears. I do not understand why this is happening.

+3
source share
3 answers

To clarify the situation, here is what the table looks like after foreign key constraints:

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 ;

Note that the fk_leerp_oefenr_leerplan and fk_leerp_oefenr_oefenreeks mysql indices are automatically added.

After adding 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 because it is no longer needed to support foreign key validation in the 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 ;

, Sqlyog , , F10.

0

Konerak , MyISAM, MySQL . InnoDB, CREATE TABLE:

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;

- , 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)

- InnoDB:

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)
0

:

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 OP

, . leerplan oefenreeks:

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 instructions 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 use: Server version: 5.1.39-log MySQL Community Server (GPL) 64bit, kubuntu 9.04.

What else could be different on your side?

0
source

Source: https://habr.com/ru/post/1744920/

More articles:


All Articles