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

Question

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.

sozkul · Accepted Answer · 2016-07-01 11:48:55Z

1

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

answered Jul 1, 2016 at 11:48

sozkul

6754 silver badges10 bronze badges

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

Comments

awesoon · Accepted Answer · 2016-07-01 11:36:24Z

0

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

And the foreign-key-clause syntax is:

answered Jul 1, 2016 at 11:36

awesoon

33.8k14 gold badges81 silver badges103 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related