Thank Lucas. I've tried that as well. In all cases, SQLAlchemy always
emits a table constraint. i.e., an additional CONSTRAINT clause in the
CREATE TABLE command. Maybe I've poorly phrased my question and SQLAlchemy
always emits table constraints? Here's an updated example. In all three
cases a CONSTRAINT clause is used to create a table constraint, in none
does it extend the column definition with the constraint.
My reasons for wanting this are on the foolish side, so it's not critical.
If there's nothing obvious, I appreciate your help and will let this go.
Instead of:
CREATE TABLE test_3 (
id SERIAL NOT NULL,
alt_id INTEGER,
CONSTRAINT uq_test_3_alt_id UNIQUE (alt_id)
)
I was looking for:
CREATE TABLE test_3 (
id SERIAL NOT NULL,
alt_id INTEGER CONSTRAINT uq_test_3_alt_id UNIQUE
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import MetaData
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import UniqueConstraint
from sqlalchemy import create_engine
Base = declarative_base()
class Test1(Base):
__tablename__ = 'test_1'
id = Column(Integer, primary_key=True)
alt_id = Column(Integer, nullable=True, default=None, unique=True)
class Test2(Base):
__tablename__ = 'test_2'
id = Column(Integer, primary_key=True)
alt_id = Column(Integer, nullable=True, default=None)
__table_args__ = (UniqueConstraint('alt_id', name='uq_alt_id'),)
active_db_url = 'postgres://user:pass@10.10.10.10/db'
engine = create_engine(active_db_url, echo=False)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
convention = {
"ix": 'ix_%(column_0_label)s',
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s"
}
metadata = MetaData(naming_convention=convention)
Base = declarative_base(metadata=metadata)
class Test3(Base):
__tablename__ = 'test_3'
id = Column(Integer, primary_key=True)
alt_id = Column(Integer, nullable=True, default=None)
__table_args__ = (UniqueConstraint('alt_id'),)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
And the output:
CREATE TABLE test_1 (
id SERIAL NOT NULL,
alt_id INTEGER,
PRIMARY KEY (id),
UNIQUE (alt_id)
)
CREATE TABLE test_2 (
id SERIAL NOT NULL,
alt_id INTEGER,
PRIMARY KEY (id),
CONSTRAINT uq_alt_id UNIQUE (alt_id)
)
CREATE TABLE test_3 (
id SERIAL NOT NULL,
alt_id INTEGER,
CONSTRAINT pk_test_3 PRIMARY KEY (id),
CONSTRAINT uq_test_3_alt_id UNIQUE (alt_id)
)
On Wednesday, May 20, 2015 at 9:15:35 PM UTC-5, Lucas Taylor wrote:
>
> Unless you provide a name, the constraint will be anonymously named, so
> there is no difference between that and the shortcut.
>
> Provide a name argument to UniqueConstraint:
>
> __table_args__ = (UniqueConstraint('alt_id', name='uq_alt_id'),)
>
> You may also be interested in providing a naming convention to automate
> this:
>
> http://docs.sqlalchemy.org/en/latest/core/constraints.html#configuring-constraint-naming-conventions
>
>
>
>
>
> On Sunday, May 17, 2015 at 7:49:39 PM UTC-5, r...@rosenfeld.to wrote:
>>
>> Sorry it took my a while to test this, but I didn't see any difference in
>> the SQL emitted. What did I miss?
>>
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy import Column
>> from sqlalchemy import Integer
>> from sqlalchemy import UniqueConstraint
>> from sqlalchemy import create_engine
>>
>> Base = declarative_base()
>>
>>
>> class Test1(Base):
>> __tablename__ = 'test_1'
>>
>> id = Column(Integer, primary_key=True)
>> alt_id = Column(Integer, nullable=True, default=None, unique=True)
>>
>>
>> class Test2(Base):
>> __tablename__ = 'test_2'
>>
>> id = Column(Integer, primary_key=True)
>> alt_id = Column(Integer, nullable=True, default=None)
>>
>> __table_args__ = (UniqueConstraint('alt_id'),)
>>
>> active_db_url = 'postgres://user:pass@10.10.10.10/db'
>> engine = create_engine(active_db_url, echo=False)
>>
>> Base.metadata.drop_all(engine)
>> Base.metadata.create_all(engine)
>>
>> And here's what I see in the log:
>> CREATE TABLE test_2 (
>> id SERIAL NOT NULL,
>> alt_id INTEGER,
>> PRIMARY KEY (id),
>> UNIQUE (alt_id)
>> )
>>
>> REATE TABLE test_1 (
>> id SERIAL NOT NULL,
>> alt_id INTEGER,
>> PRIMARY KEY (id),
>> UNIQUE (alt_id)
>> )
>>
>>
>>
>>
>> On Sunday, May 3, 2015 at 10:23:31 PM UTC-5, Michael Bayer wrote:
>>>
>>> sure, use UniqueConstraint directly. It's better to use that than the
>>> unique=True flag in any case.
>>>
>>>
>>>
>>> On 5/3/15 10:29 PM, r...@rosenfeld.to wrote:
>>>
>>> Is there a way to control whether DDL emitted by SQLAlchemy uses a
>>> column and/or table constraint for uniqueness?
>>>
>>> It seems the following
>>> class Part(Base):
>>> __tablename__ = 'part'
>>> third_party_id = Column(Integer, nullable=True, default=None, unique
>>> =True)
>>>
>>>
>>> emits a table constraint
>>> CREATE TABLE part (
>>> third_party_id INTEGER,
>>> CONSTRAINT uq_part_third_party_id UNIQUE (third_party_id)
>>> )
>>>
>>>
>>>
>>>
>>> Is it possible to emit the following with a column constraint instead?
>>> CREATE TABLE part (
>>> third_party_id INTEGER CONSTRAINT uq_part_third_party_id UNIQUE
>>> )
>>>
>>> Thanks
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to sqlalchemy+...@googlegroups.com.
>>> To post to this group, send email to sqlal...@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>>
>>>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
