- changed milestone to 0.6.xx
Invalid syntax for 'create table' with sqlite referencing to tables in schemas
This problem shows up when an Sqlite db is composed of multiple sub-DBs via the 'attach' statement. This is useful for mimicking schemata and allows tables to be addressed like <schema>.<table> (using common dot-syntax).
The following code reproduces the problem in SA (tested in 0.5.8 and 0.6.2):
from sqlalchemy import *
metadata = MetaData()
engine = create_engine('sqlite:///main.db', echo=True)
engine.execute('ATTACH "master.db" AS master')
owner = Table('owner', metadata,
Column('owner_id', Integer),
schema='master')
project = Table('project', metadata,
Column('project_id', Integer,
Sequence('seq_project_id', schema='master'), primary_key=True),
Column('name', String(20)),
Column('owner_id', Integer,
ForeignKey('master.owner.owner_id')),
schema='master')
metadata.create_all(engine)
which leads to a statement
CREATE TABLE master.project (
project_id INTEGER NOT NULL,
owner_id INTEGER,
PRIMARY KEY (project_id),
FOREIGN KEY(owner_id) REFERENCES master.owner (owner_id)
);
However this fails.
sqlalchemy.exc.OperationalError: (OperationalError) near "."
The problem is REFERENCES master.owner where sqlite complains about the dot in the referenced table name. The solution could be to use square brackets (i.e. REFERENCES master.owner ) or quotes (i.e. REFERENCES "master.owner" ) for sqlite. Unfortunately both solutions seem to be somehow unique to sqlite, they fail e.g. with postgres. I haven't tried other DBs.
Michael Bayer stated (in a google groups track) "that the likely solution here would be to not render the REFERENCES clause at all."
(This tickets relates somehow to ticket #1439)
(Ticket created by Ralph Heinkel)
Comments (5)
-
repo owner -
repo owner - changed milestone to 0.6.6
Here's the correct syntax:
CREATE TABLE master.t1 ( id INTEGER NOT NULL, PRIMARY KEY (id) ) CREATE TABLE master.t2 ( id INTEGER NOT NULL, t2_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(t2_id) REFERENCES t1 (id) )
the remote table is assumed to be in the same schema as the parent.
-
repo owner - changed status to resolved
-
repo owner closely related is d3ee4f6155acbc1b2df85fef3f4d2cdae9e1306c , which does in fact bypass the REFERENCES clause if the referenced table is in a different schema name.
-
repo owner - removed milestone
Removing milestone: 0.6.6 (automated comment)
- Log in to comment