How to create Foreign Key using MySQLdb

Question

After importing MySQLdb module I go ahead and connect to MySQl Maria db:

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

Now I want to create two tables: Writers and Readers. I want Readers to have a Foreign Key attribute "Writer_id" that would point to the corresponding Writer:

with db:
    cHandler=db.cursor()
    cHandler.execute( "CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT)")
    cHandler.execute( "CREATE TABLE Readers(Id INT PRIMARY KEY AUTO_INCREMENT, FOREIGN KEY(Writer_id) REFERENCES Writers(Id) ) ")

But the code fails with the error:

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

Where is the error?

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

you have to create writer_id in Readers table first and then set it as a foreign key after.

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)")