Invalid syntax for 'create table' with sqlite referencing to tables in schemas

[Former user]

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)

[Mike Bayer]

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.

[Mike Bayer]

bbe3f0a27c5b4cb6506d2f23d8a2654c80d6b481

[Mike Bayer]

closely related is d3ee4f6155acbc1b2df85fef3f4d2cdae9e1306c , which does in fact bypass the REFERENCES clause if the referenced table is in a different schema name.

[Mike Bayer]

Removing milestone: 0.6.6 (automated comment)