使用自增变量作为外键 - 出现外键约束错误

Question

我想用一个自动递增的SQL变量作为外键。我的代码是用Python和SQL写的（这样说对吗？）不过，除非我在代码里填一个实际的数字，否则会出现外键约束错误。

我看过很多关于这些错误的帖子，并尝试使用里面的信息……

这是我的两个表。表1在上面，表2在下面。ID是外键。为了避免错误，我在代码里填了数字1。

+----+------+-------+
| id | name | class |
+----+------+-------+
|  1 | ONE  | TWO   |
|  2 | ONE  | TWO   |
|  3 | ONE  | TWO   |
|  4 | ONE  | TWO   |
+----+------+-------+

mysql> select * from table2;

+-----+------+-------+----+
| par | name | class | ID |
+-----+------+-------+----+
|   1 | SSS  | CAR   |  1 |
|   2 | SSS  | CAR   |  1 |
+-----+------+-------+----+

这是代码

sql= """CREATE TABLE IF NOT EXISTS table1 (
      `ID` integer NOT NULL AUTO_INCREMENT,
  `name` varchar(30) default NULL,
  `class` varchar(20) default NULL,
       PRIMARY KEY  (`ID`)
       )"""
cursor.execute(sql)

sql2 = """CREATE TABLE IF NOT EXISTS table2 (
  `par` integer NOT NULL AUTO_INCREMENT,
  `name` varchar(30) default NULL,
  `class` varchar(20) default NULL,
  `ID` integer NOT NULL,
  FOREIGN KEY (ID) REFERENCES table1 (ID),
  PRIMARY KEY (par)
  )"""          
cursor.execute(sql2)        

query = "INSERT INTO table1 (name, class) VALUES('ONE','TWO')"
cursor.execute(query)

query2 = "INSERT INTO table2 (name, class,ID) VALUES('SSS','CAR',1)"
cursor.execute(query2)

还有一件事——在FOREIGN KEY(ID)和REFERENCES之间加个逗号（Table2）会出错。？？

编辑：我觉得我的问题不太清楚。我想要的是让表2中的'ID'

`ID` integer NOT NULL,
      FOREIGN KEY (ID)

引用

    `ID` integer NOT NULL AUTO_INCREMENT,    
     PRIMARY KEY  (`ID`)

表1中的内容

编辑：LAST_INSERT_ID()适用于单行插入。MySQL文档描述了如何用于多行插入。表2确实有多行。

UPDATE table2 SET ID=LAST_INSERT_ID(ID+1);

我使用这种方法时收到的错误信息是：无法添加或更新子行：外键约束失败（db.table2, CONSTRAINT table2_ibfk_1 FOREIGN KEY (ID) REFERENCES table1 (ID)）

Answer 1

问题 1) MySQL 提供了一个叫做 LAST_INSERT_ID() 的功能。对于单行操作，它会返回最后生成的自增编号。对于多行插入，你需要自己记录插入了多少行，因为 LAST_INSERT_ID() 只会返回这次操作生成的第一个编号。

下面是一个一对一的例子：

query = "INSERT INTO table1 (name, class) VALUES('ONE','TWO')"
cursor.execute(query)

query2 = "INSERT INTO table2 (name, class,ID) VALUES('SSS','CAR',LAST_INSERT_ID())"
cursor.execute(query2)

这里是一个一对多的例子：

#Insert the parent record
query = "INSERT INTO table1 (name, class) VALUES('ONE','TWO')"
cursor.execute(query)

#get the autoincrement id
cursor.execute('select LAST_INSERT_ID()')
parentId = cursor.fetchone()[0]

#Assuming you have several child records as a list of lists
childVals = [['SSS','CAR'],['FOO','BAR']]

#define the template child insert SQL using parameter substitution
# many dbapi modules use ? as the parameter placeholder, yours may use %s, etc.
query2 = "INSERT INTO table2 (name, class,ID) VALUES(?,?,?)"

#insert the child records with the parent id
for vals in childVals:
    #pass in the child record values with the parent id
    cursor.execute(query2, vals + [id])

问题 2) REFERENCES 是告诉 FOREIGN KEY 应该去哪里查找相关记录，因此它们是同一个语句的一部分，不能用逗号分开。

Answer 2

在查询2中，当你看到数字1时，你必须在table1中插入一个ID的值。这就是外键（FK）的意思，如果你没有插入一个在被引用表中存在的值，就会报错。