如何使用MySQLdb创建外键

Question

导入MySQLdb模块后，我继续连接MySQl Maria db：

import  MySQLdb
db = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="test")

现在我想创建两个表：Writers和{}。我希望Readers有一个外键属性“Writer_id”，它将指向相应的Writer：

^{pr2}$

但代码失败并出现错误：

_mysql_exceptions.OperationalError: (1072, "Key column 'x' doesn't exist in table")

错误在哪里？在

Answer 1

with db:
    cHandler=db.cursor()

    cHandler.execute("DROP TABLE IF EXISTS Writers")
    cHandler.execute("DROP TABLE IF EXISTS Readers")

    cHandler.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, name TEXT)")
    cHandler.execute("CREATE TABLE Readers(Id INT PRIMARY KEY AUTO_INCREMENT, name TEXT, Writer_id INTEGER, FOREIGN KEY(Writer_id) REFERENCES Writers(Id))")

Answer 2

必须先在Readers表中创建writer_id，然后再将其设置为外键。在

cHandler.execute( "CREATE TABLE Readers(Id INT PRIMARY KEY AUTO_INCREMENT, Writer_id INT ) ")
cHandler.execute( "alter table Readers
add foreign key (Writer_id)
references Writers(Id)")