sqlite does not support deferred keyword on (unique) constraint

[Martin Babka]

When using sqlite and setting

class Model(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    order = Column(Integer)

    __table_args__ = (UniqueConstraint('order', name='single_occurrence_of_order', deferrable=True),)

then creating the tables throws sqlite's syntax error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "DEFERRABLE": syntax error [SQL: '\nCREATE TABLE users (\n\tid INTEGER NOT NULL, \n\tname VARCHAR, \n\t"order" INTEGER, \n\tPRIMARY KEY (id), \n\tCONSTRAINT single_occurrence_of_order UNIQUE ("order") DEFERRABLE\n)\n\n']

Issue 2841 addresses the problem for MySQL, a similar solution may be done in case of sqlite.

I could resolve the problem as in this commit.

[Mike Bayer]

issue #2841 was specifically that the keyword was being ignored silently, and the fix was that it raises an error.

so here, the request is just that a nicer error be raised.

[Mike Bayer]

current behavior is to let the keyword fall through to the backend. the database is the authority on keywords that are / are not accepted and SQLAlchemy usually stays out of the way on that. MySQL:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Model(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(10))
    order = Column(Integer)

    __table_args__ = (UniqueConstraint('order', name='single_occurrence_of_order', deferrable=True),)


e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)

output:

#!


sqlalchemy.exc.ProgrammingError: (_mysql_exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 7") [SQL: u'\nCREATE TABLE users (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tname VARCHAR(10), \n\t`order` INTEGER, \n\tPRIMARY KEY (id), \n\tCONSTRAINT single_occurrence_of_order UNIQUE (`order`) DEFERRABLE\n)\n\n']