1

I'm trying to create a sqlite3 table using python. My code is given below:

def initDb():
    database = 'index.db'
    conn = sqlite3.connect(database)

    cur = conn.cursor()

    # Initialize database
    cur.execute('PRAGMA foreign_keys = ON')

    cur.execute('DROP TABLE IF EXISTS modules')
    cur.execute('DROP TABLE IF EXISTS files')
    cur.execute('DROP TABLE IF EXISTS modulesfiles')

    cur.execute(
        '''CREATE TABLE modules (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            label TEXT UNIQUE NOT NULL
        )'''
    )
    cur.execute(
        '''CREATE TABLE files (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            filename TEXT UNIQUE NOT NULL
        )'''
    )
    cur.execute(
        '''CREATE TABLE modulesfiles (
        module INTEGER UNIQUE NOT NULL,
        file INTEGER UNIQUE NOT NULL,
        PRIMARY KEY (module,file),
        FOREIGN KEY (module) REFERENCES modules(id) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (file) REFERENCES files(id) ON UPDATE CASCADE ON DELETE CASCADE
        )'''
    )

    cur.close()

    return conn

if __name__ == '__main__':
    conn = initDb()
    conn.commit()
    conn.close()

This code runs fine the first time I run it and my database is created. However, if I run it a second time, I get the following error:

    cur.execute('DROP TABLE IF EXISTS files')
sqlite3.OperationalError: no such table: main.modules

I have no idea what's going wrong. Can anybody help?

1 Answer 1

2

Dropping modules first makes the foreign key constraint in modulesfiles invalid.

Drop the child table first.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.