python - MySQL 主键作为外键 - 约束错误

Question

假设我有 3 张 table 。

CREATE TABLE IF NOT EXISTS GROUP ( 
           ID_GROUP INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
           GROUP VARCHAR(50) NOT NULL, 
           INDEX(GROUP) 
         ) ENGINE=INNODB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci;)

CREATE TABLE IF NOT EXISTS USERZ (
            ID CHAR(8) NOT NULL, 
            GROUP VARCHAR(50) NOT NULL, 
            FIRST_NAME VARCHAR(100) NULL DEFAULT NULL , 
            LAST_NAME VARCHAR(100) NULL DEFAULT NULL, 
            DATE_CREATED TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, 
            PASSWORD VARCHAR(8) NULL DEFAULT 'HELLOWORLD', 
            PRIMARY KEY (ID), 
            INDEX (ID), 
            INDEX (GROUP), 
            FOREIGN KEY (GROUP) REFERENCES GROUP (GROUP) 
    ) ENGINE=INNODB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci;)


CREATE TABLE IF NOT EXISTS WORKERS (   
          ID CHAR(8) NOT NULL PRIMARY KEY,   
          DATE_START TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,   
          DATE_END TIMESTAMP NULL,   
          FOREIGN KEY (ID) REFERENCES USERZ (ID)   
) ENGINE=INNODB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci;)

我在表 USERZ 中插入数据没有问题。但是当需要向表 WORKERS 添加记录时，我收到错误消息:

mysql.connector.errors.IntegrityError: 1452 (23000): 无法添加或更新子行:外键约束失败 (DATABASENAME.WORKERS, CONSTRAINT WORKERS_ibfk_1外键(ID)引用USERZ(ID))

我确信这是 WORKERS 主键 (ID) 引用 USERZ (ID) 的内容。即使我知道 USERZ 中存在 USERZ id，它也不会让我向 WORKERS 添加数据。通过下面的示例，我知道 USERZ 表中存在“XXXXXXX1”，因此我想能够在 WORKERS 表中添加此用户。

示例:

"INSERT INTO WORKERS (ID) VALUES ('XXXXXXX1') "

谢谢你!

Answer 1

您不应该让主键 id 也成为外键。将其重构为这样

CREATE TABLE IF NOT EXISTS WORKERS (   
          ID CHAR(8) NOT NULL PRIMARY KEY, 
          USER_ID CHAR(8) NOT NULL,
          DATE_START TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,   
          DATE_END TIMESTAMP NULL,   
          FOREIGN KEY (USER_ID) REFERENCES USERZ (ID)   
) ENGINE=INNODB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci;)

然后你的插入应该是

INSERT INTO WORKERS (USER_ID) VALUES ('XXXXXXX1')