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:[email protected]/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
<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
<http://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
|
classPart(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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
