时间戳

首页 > 解决方案 > MySQL：java.sql.SQLException：外键约束'INCORRECT_ANSWER_FK01'中的引用列和引用列不兼容

mysql - MySQL：java.sql.SQLException：外键约束'INCORRECT_ANSWER_FK01'中的引用列和引用列不兼容

问题描述

我一直在使用 MySQL 测试容器进行集成测试，最近它停止工作，声称我的一个外键存在问题（我不相信自上次运行以来我没有进行任何 SQL 更改测试，它运行良好）。

我已经浏览了这里列出的清单（MySQL Creating tables with Foreign Keys give errno: 150 in answer with 237 upvotes），我似乎找不到解决方案。（完整）错误消息是：

Caused by: org.testcontainers.ext.ScriptUtils$ScriptStatementFailedException: Script execution failed (sqlForIntegrationTest.sql:18): ALTER TABLE INCORRECT_ANSWER ADD CONSTRAINT INCORRECT_ANSWER_FK01 FOREIGN KEY(QUESTION_SID) REFERENCES QUESTION(QUESTION_SID) ON DELETE CASCADE ON UPDATE CASCADE
Caused by: java.sql.SQLException: Referencing column 'QUESTION_SID' and referenced column 'QUESTION_SID' in foreign key constraint 'INCORRECT_ANSWER_FK01' are incompatible.

用于初始化测试容器的 SQL 文件是：

CREATE TABLE ASSESSMENT(
    ISBN BIGINT NOT NULL
    , AUTHOR_FIRST_NAME VARCHAR(32)
    , AUTHOR_LAST_NAME VARCHAR(32)
    , TITLE VARCHAR(128) NOT NULL
    , NUMBER_OF_POINTS FLOAT NOT NULL
    , IS_VERIFIED BOOLEAN DEFAULT FALSE
    , READING_LEVEL FLOAT NOT NULL
    , CREATED_TEACHER_ID MEDIUMINT
) ENGINE=INNODB DEFAULT CHARSET=latin1;


CREATE UNIQUE INDEX ASSESSMENT_X01 ON ASSESSMENT(
    ISBN
);

ALTER TABLE ASSESSMENT
    ADD CONSTRAINT ASSESSMENT_PK PRIMARY KEY(
        ISBN
    );

CREATE INDEX ASSESSMENT_X02 ON ASSESSMENT(
    TITLE
);

CREATE INDEX ASSESSMENT_X03 ON ASSESSMENT(
    CREATED_TEACHER_ID
);


CREATE TABLE QUESTION(
    QUESTION_SID BIGINT NOT NULL 
    , ASSESSMENT_ISBN BIGINT NOT NULL
    , QUESTION_TEXT VARCHAR(256) NOT NULL
    , CORRECT_ANSWER VARCHAR(256) NOT NULL
    ) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE UNIQUE INDEX QUESTION_X01 ON QUESTION (
    QUESTION_SID
);

ALTER TABLE QUESTION 
    ADD CONSTRAINT QUESTION_PK PRIMARY KEY(
        QUESTION_SID
    );

ALTER TABLE QUESTION MODIFY COLUMN QUESTION_SID INT     AUTO_INCREMENT; 

ALTER TABLE QUESTION AUTO_INCREMENT=1; 


CREATE INDEX QUESTION_X02 ON QUESTION(
    ASSESSMENT_ISBN
);

ALTER TABLE QUESTION 
    ADD CONSTRAINT QUESTION_FK01 FOREIGN KEY(
        ASSESSMENT_ISBN
    ) REFERENCES ASSESSMENT(ISBN)
    ON DELETE CASCADE
    ON UPDATE CASCADE;


CREATE TABLE INCORRECT_ANSWER(
    INCORRECT_ANSWER_SID INT NOT NULL 
    , QUESTION_SID BIGINT NOT NULL
    , TEXT VARCHAR(256)
    ) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE UNIQUE INDEX INCORRECT_ANSWER_X01 ON         INCORRECT_ANSWER(
INCORRECT_ANSWER_SID
);

ALTER TABLE INCORRECT_ANSWER
    ADD CONSTRAINT ANSWER_PK PRIMARY KEY(
        INCORRECT_ANSWER_SID
    );

ALTER TABLE INCORRECT_ANSWER MODIFY COLUMN     INCORRECT_ANSWER_SID INT AUTO_INCREMENT;

ALTER TABLE INCORRECT_ANSWER AUTO_INCREMENT=1; 

ALTER TABLE INCORRECT_ANSWER
    ADD CONSTRAINT INCORRECT_ANSWER_FK01 FOREIGN     KEY(QUESTION_SID) REFERENCES QUESTION(QUESTION_SID) 
ON DELETE CASCADE
ON UPDATE CASCADE;  

INSERT INTO ASSESSMENT(ISBN, AUTHOR_FIRST_NAME, AUTHOR_LAST_NAME, TITLE, NUMBER_OF_POINTS, IS_VERIFIED, READING_LEVEL, CREATED_TEACHER_ID) VALUES(9781976530739, 'Herman', 'Melleville', 'Moby Dick', 65, FALSE, 10.8, 1);

标签： mysqlforeign-keysconstraintsintegration-testingtestcontainers

解决方案

你ALTER TABLE QUESTION MODIFY COLUMN QUESTION_SID INT AUTO_INCREMENT;- 你应该离开 bigint。

推荐阅读