sqlalchemy中对多列的唯一约束

Question

我想在表上设置一个约束，以限制表中值的输入。我想确保任何值的组合只能出现一次。

I.e(1,2(和(2,1(不能在同一个表中。

例如，我有一个包含两列(c1和c2(的表：
必须遵循以下内容。

C1        C2
1         2         OK 
2         1         NOT OK
3         1         OK
1         2         NOT OK
1         4         OK
1         3         NOT OK

在SQLALchemy中有什么方法可以做到这一点吗？

我使用了UNIQUE(c1，c2(，但只说(1,2(和(1,2。

感谢

Answer 1

可能最简单的解决方案是为c1 < c2(或c1 <= c2，如果允许它们相同(添加一个检查约束，这样(c1，c2(将始终处于"；升序"：

import sqlalchemy as sa
connection_uri = (
"mssql+pyodbc://@localhost:49242/myDb?driver=ODBC+Driver+17+for+SQL+Server"
)
engine = sa.create_engine(connection_uri)
Base = declarative_base()

class So64232358(Base):
__tablename__ = "so64232358"
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
c1 = sa.Column(sa.Integer, nullable=False)
c2 = sa.Column(sa.Integer, nullable=False)
comment = sa.Column(sa.String(50))
sa.CheckConstraint(c1 < c2)
sa.UniqueConstraint(c1, c2)

Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)
"""SQL rendered:
CREATE TABLE so64232358 (
id INTEGER NOT NULL IDENTITY, 
c1 INTEGER NOT NULL, 
c2 INTEGER NOT NULL, 
comment VARCHAR(50) NULL, 
PRIMARY KEY (id), 
CHECK (c1 < c2), 
UNIQUE (c1, c2)
)
"""
Session = sessionmaker(bind=engine)
session = Session()
obj = So64232358(c1=2, c2=1, comment="no es bueno")
session.add(obj)
try:
session.commit()
except sa.exc.IntegrityError as ie:
print(ie)
"""console output:
(pyodbc.IntegrityError) ('23000', '[23000] [Microsoft]
[ODBC Driver 17 for SQL Server][SQL Server]The INSERT statement conflicted
with the CHECK constraint "CK__so64232358__429B0397". The conflict
occurred in database "myDb", table "dbo.so64232358".
(547) (SQLExecDirectW);
[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
The statement has been terminated. (3621)')
"""
session.rollback()
obj = So64232358(c1=1, c2=2, comment="bueno")
session.add(obj)
session.commit()  # no error
obj = So64232358(c1=1, c2=2, comment="duplicado")
session.add(obj)
try:
session.commit()
except sa.exc.IntegrityError as ie:
print(ie)
"""console output:
(pyodbc.IntegrityError) ('23000', "[23000] [Microsoft]
[ODBC Driver 17 for SQL Server][SQL Server]Violation of UNIQUE KEY
constraint 'UQ__so642323__E13250592117193A'. Cannot insert duplicate key
in object 'dbo.so64232358'. The duplicate key value is (1, 2).
(2627)(SQLExecDirectW);
[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
The statement has been terminated. (3621)")
"""
session.rollback()