0

I have been working with a MySQL test container for my integration tests, and recently, it stopped working, claiming there was an issue with one of my foreign keys (and I don't believe I made any SQL changes since the last time I ran the tests, in which it ran fine).

I've ran through the checklist outlined here (MySQL Creating tables with Foreign Keys giving errno: 150 in the answer with 237 upvotes), and I cannot seem to find the solution. The (full) error message is:

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.

The SQL file used to initialize the test container is:

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);
halfer
  • 19,824
  • 17
  • 99
  • 186
Connor Butch
  • 648
  • 1
  • 10
  • 28
  • 1
    I appreciate that sometimes readers give quick-fire answers/comments that do not help. Nevertheless, we should still call those responses helpful; if readers were determined to be unhelpful they would not be here. I have therefore made an edit to the question trying to head off "quick answers" - that intro may be the reason for the downvote. It's best to keep questions succinct anyway. – halfer Feb 09 '20 at 20:27

1 Answers1

0

You ALTER TABLE QUESTION MODIFY COLUMN QUESTION_SID INT AUTO_INCREMENT; - you should have left as bigint.

halfer
  • 19,824
  • 17
  • 99
  • 186
P.Salmon
  • 17,104
  • 2
  • 12
  • 19