Unique constraint on multiple columns in sqlalchemy

user_12288003 0
Viewed 1364

I want to put a constraint on a table to limit the input of values in the table. I want to make sure that any combination of values can only be in once.

I.e (1,2) and (2,1) can not be in the same table.

For example I have a table with two columns (c1 and c2):
The following has to be followed.

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

Is there any way to do this in SQLALchemy?

I used UNIQUE(c1, c2) but that only says (1,2) and (1,2) can't be in the same table but as mentioned I also want to include that (2,1) can't be in the table either.

Thanks

sqlalchemy
1 Answers

Probably the easiest solution is to add a check constraint for c1 < c2 (or c1 <= c2 if they're allowed to be the same) so that (c1, c2) will always be in "ascending order":

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()
user_21443900
Related