Thanks. Makes sense. On Friday, May 29, 2015 at 10:12:07 AM UTC-5, Michael Bayer wrote: > > > > On 5/29/15 2:12 AM, [email protected] <javascript:> wrote: > > 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. > > > there's no difference between a UNIQUE constraint declared at the column > or table level from the database's perspective. SQLA focuses on table > level because that's where there's the most options, keeps things > simplest. > > > 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:/[email protected]/db <javascript:>' > 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, [email protected] 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:[email protected]/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, [email protected] 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 [email protected]. >>>> To post to this group, send email to [email protected]. >>>> Visit this group at <http://groups.google.com/group/sqlalchemy> >>>> http://groups.google.com/group/sqlalchemy. >>>> For more options, visit <https://groups.google.com/d/optout> >>>> 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 [email protected] <javascript:>. > To post to this group, send email to [email protected] > <javascript:>. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
