CODEWITHSUNDEEP
pythonsqlite
Vishal Khanna
Vishal Khanna

Reputation: 53

Syntax error while using Foreign Keys in Sqlite 3 in Python.

I have a database schema file which I read in my Flask module.

PRAGMA foreign_keys = 1;

drop table if exists user;
create table user(uid integer primary key autoincrement, 
username text not null,
password text not null,
email text not null);

drop table if exists asset;
create table asset(aid integer primary key autoincrement,
assetname text not null,
releasedate text,
FOREIGN_KEY(owner) REFERENCES user);

When I remove the foreign key field, it works fine.

Error trace:

  File "main.py", line 75, in <module>
    create_table()
  File "main.py", line 30, in create_table
    conn.cursor().executescript(f.read())
sqlite3.OperationalError: near "(": syntax error

I can post the method which uses this script but I don't think the problem lies there.

Upvotes: 0

Views: 1177

Answers (2)

sozkul
sozkul

Reputation: 675

As @soon pointed you have a syntax error on FOREIGN KEY keyword, also you should define column name (owner).

drop table if exists asset; create table asset(aid integer primary key autoincrement, assetname text not null, releasedate text, owner integer, FOREIGN KEY(owner) REFERENCES user);

Upvotes: 1

awesoon
awesoon

Reputation: 33651

You should replace the underscore between the FOREIGN and KEY with a space. The following diagrams shows the constraint syntax:

enter image description here

And the foreign-key-clause syntax is:

enter image description here

Upvotes: 0

Related Questions