[Python] Cloned table works causes foreign key error

I'm having trouble cloning tables (sqlite3 uner Python). I have two tables:

cursor.execute('''CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
date DATE,
description TEXT
)
''')

cursor.execute(''' CREATE TABLE IF NOT EXISTS details (
id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id INTEGER,
account TEXT,
amount REAL,
FOREIGN KEY (post_id) REFERENCES posts_base(id) ON DELETE CASCADE)
''' )

cursor.execute("PRAGMA foreign_keys = ON;")


In an attempt to clone (after the same CREATE TABLE for posts_temp, details_temp:

cursor.execute(''' INSERT INTO posts_temp SELECT * FROM posts ''')


and the same for details_temp.

Then

cursor.execute("INSERT INTO posts_base_temp (date, description) VALUES (?, ?)",
(post_date, 'foo'))
post_id = self.cursor.lastrowid

cursor.execute("INSERT INTO details_temp (post_id, account, amount) VALUES (?, ?, ?)",
(post_id, 'travel', 101.0))
conn.commit()


gives me FOREIGN KEY constraint failed, but the same code with posts and details works fine.

Thanks for your help.

Continue reading...