已解决在向有外键表插入数据提示“foreign key constraint fails”

最新推荐文章于 2024-02-13 22:14:30 发布

weixin_32589873

最新推荐文章于 2024-02-13 22:14:30 发布

阅读量3.4w

点赞数 5

分类专栏：数据库学习文章标签：数据库

数据库学习专栏收录该内容

2 篇文章 0 订阅

订阅专栏

今天在做作业的时候，在建立了几个主表，一个有外键的表

USE SPJ;
CREATE TABLE S (SNO CHAR(4) NOT NULL,
                         SNAME CHAR(20) NOT NULL,
                         STATUS CHAR(10),
                         CITY CHAR(20),
                                        PRIMARY KEY (SNO));
CREATE TABLE P (PNO CHAR(4) NOT NULL,
                    PNAME CHAR(20) NOT NULL,
                    COLOR CHAR(8),   
                    WEIGHT SMALLINT,
                    PRIMARY KEY(PNO));
CREATE TABLE J (JNO CHAR(4) NOT NULL,
                   JNAME  CHAR(20),
                   CITY  CHAR(20),
                   PRIMARY KEY(JNO));
CREATE TABLE SPJ (SNO CHAR(4) NOT NULL,
                       PNO CHAR(4) NOT NULL,
                       JNO CHAR(4) NOT NULL,
                       QTY SMALLINT,
                       PRIMARY KEY (SNO,PNO,JNO),
                       CONSTRAINT `spj_ibfk_1` FOREIGN KEY (SNO) REFERENCES S(SNO) ON DELETE CASCADE ON UPDATE CASCADE,
                       CONSTRAINT `spj_ibfk_2` FOREIGN KEY (PNO) REFERENCES P(PNO) ON DELETE CASCADE ON UPDATE CASCADE,
                       CONSTRAINT `spj_ibfk_3` FOREIGN KEY (JNO) REFERENCES J(JNO) ON DELETE CASCADE ON UPDATE CASCADE);

生成了这几个表之后，在向SPJ表添加数据的时候就报错了，提示
“1452：INSERT INTO spj.spj (SNO, PNO, JNO, QTY) VALUES (‘S1’, ‘P2’, ‘J2’, ‘100’) 1452: Cannot add or update a child row: a foreign key constraint fails (spj.spj, CONSTRAINT spj_ibfk_1 FOREIGN KEY (SNO) REFERENCES s (SNO)) ”

查了很多的资料都未果
但是当找到了这里：https://stackoverflow.com/questions/12966626/mysql-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails
里面的方法解决了这个问题
在INSERT代码段前面添加

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

在代码段最后添加

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;