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

Issue #1851 resolved
Former user created an issue

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)

  1. Mike Bayer 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.

  2. Log in to comment