mysql - 使用约束和外键的 SQL 出现问题

Question

我正在学习使用 MYSQL(在带有 MariaDB 的 Mac OS X 的 Xamp 应用程序上)，但我陷入了困境......

当我想更改表以添加外键约束时，出现此错误:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`db_forum`.`#sql-2d52_2f4`, CONSTRAINT `CO_FK_message` FOREIGN KEY (`FK_message`) REFERENCES `T_message` (`ID_message`))

但是我之前在我的脚本中执行了确切的操作(对于另一个表)并且它工作得很好......

我已经看过这里:http://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html但根本没有帮助。

错误仅出现在最后 4 行。

这是我的脚本:

/* commentaire debggg drop */
DROP DATABASE IF EXISTS db_forum;
CREATE DATABASE IF NOT EXISTS db_forum;
USE db_forum;


CREATE TABLE T_message
(
ID_message int NOT NULL AUTO_INCREMENT,
text_MSG VARCHAR(255),
datecrea_MSG DATE NOT NULL,
PRIMARY KEY (ID_message)
)ENGINE = InnoDB ;


CREATE TABLE T_user
(
ID_user int NOT NULL AUTO_INCREMENT,
prenom_user VARCHAR(50),
nom_user VARCHAR(50),
datenaissance_user DATE NOT NULL,
email_user VARCHAR(30),
account_user VARCHAR(16),
password_user VARCHAR(16),
PRIMARY KEY (ID_user)
)ENGINE = InnoDB ;


CREATE TABLE T_thread
(
ID_thread int NOT NULL AUTO_INCREMENT,
datecrea_thread DATE NOT NULL,
titre_thread VARCHAR(30),
PRIMARY KEY (ID_thread)
)ENGINE = InnoDB ;

CREATE TABLE T_posted
(
ID_posted int NOT NULL AUTO_INCREMENT,
FK_user int NOT NULL,
FK_message int NOT NULL,
PRIMARY KEY (ID_posted),
CONSTRAINT CO_FK_message FOREIGN KEY (FK_message) REFERENCES T_message (ID_message),
CONSTRAINT CO_FK_user FOREIGN KEY (FK_user) REFERENCES T_user (ID_user)
)ENGINE = InnoDB ;

CREATE TABLE T_manyMSG
(
ID_manyMSG int NOT NULL AUTO_INCREMENT,
FK_thread int NOT NULL,
FK_message int NOT NULL,
PRIMARY KEY (ID_manyMSG),
CONSTRAINT CO_FK_thread FOREIGN KEY (FK_thread) REFERENCES T_thread (id_thread),
CONSTRAINT CO_FK_MSG FOREIGN KEY (FK_message) REFERENCES T_message (id_message)
)ENGINE = InnoDB ;


/* IMPORTE DATA */

/* table T_user/ AJOUT DATA */
LOAD DATA LOCAL INFILE '/Volumes/Local_SSD/Applications/XAMPP/xamppfiles/htdocs/forum/sql/import_data_user.csv'

    INTO TABLE T_user

    FIELDS TERMINATED BY ';'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (prenom_user,nom_user,datenaissance_user,email_user,account_user,password_user);

    /* ajout manuel de compte administrateur pour forum et compte test */   
INSERT INTO T_user (prenom_user, nom_user, datenaissance_user, email_user, account_user, password_user )
VALUES ('Joel', 'lawl', '1990-10-20','lol@lil.com','admin','1234'),
('bernard', 'plowz', '1980-11-22','lo@ll.com','test','12345');


/* table T_message / AJOUT DATA */
LOAD DATA LOCAL INFILE '/Volumes/Local_SSD/Applications/XAMPP/xamppfiles/htdocs/forum/sql/import_data_message.csv'

    INTO TABLE T_message

    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (text_MSG,datecrea_MSG);

/* AJOUT MANUEL POUR TEST */    
INSERT INTO T_message (text_MSG, datecrea_MSG)
VALUES ('bonjour','2016-01-20'),
('salut','2016-02-20');

/* Desactivation des FK_message pour ajout de data dans table / T_posted */
/* ATTENTION Ceci prend effet après 1 minutes */
/* ATTENTION, CES COMMANDES FONCTIONE PARFAITEMENT AVEC L'ENGINE SERVEUR MARIADB (SUR XAMP MAC OS X) */

ALTER TABLE `db_forum`.`T_posted` 
DROP FOREIGN KEY `CO_FK_message`;
ALTER TABLE `db_forum`.`T_posted` 
DROP INDEX `CO_FK_message` ;



/* Desactivation des FK_user pour ajout de data dans table / T_posted */
/* ATTENTION Ceci prend effet après 1 minutes */
/* ATTENTION, CES COMMANDES FONCTIONE PARFAITEMENT AVEC L'ENGINE SERVEUR MARIADB (SUR XAMP MAC OS X) */

ALTER TABLE `db_forum`.`T_posted` 
DROP FOREIGN KEY `CO_FK_user`;
ALTER TABLE `db_forum`.`T_posted` 
DROP INDEX `CO_FK_user` ;


/* table T_posted * AJOUT DATA */
LOAD DATA LOCAL INFILE '/Volumes/Local_SSD/Applications/XAMPP/xamppfiles/htdocs/forum/sql/import_data_posted.csv'

    INTO TABLE T_posted     

    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (FK_user,FK_message);

/* AJOUT MANUEL POUR TEST JOIN */
INSERT INTO T_posted (FK_user, FK_message)
VALUES ('128','1024'),
('129','1025');



/* THIS ONE WORK */ 

ALTER TABLE T_posted
ADD CONSTRAINT CO_FK_user
FOREIGN KEY (FK_user)
REFERENCES T_user(ID_user);

/* THIS ONE DONT WORK =/ */ 

ALTER TABLE T_posted
ADD CONSTRAINT CO_FK_message
FOREIGN KEY (FK_message)
REFERENCES T_message(ID_message);

Answer 1

问题不在于您的 ALTER TABLE 语句。

您在插入数据时，应该检查限制是否有问题。

您似乎正在插入一个不作为主键存在的外键。

您还应该在插入数据之前执行 ALTER 语句。

最诚挚的问候，