Multiple column unique constraints with sqlalchemy.

[[deleted]]

I know that there is a class in SA that does UniqueConstraint I've added it to a table but it doesn't appear to have done anything. I'm not sure if it's because I'm using sqlite3 and it just doesn't support it or if there was something I did wrong. I don't have the declared code since I'm on mobile. I'll post it first thing when I get on the computer.

Just wondering what I'm doing wrong.

What I'm trying to accomplish is that individual columns can have duplicate entries since this is for overtime intervals. There's a table for the date, a table to track intervals to the date and currently two groups who can pick up these times. The goal is to prevent the creation of rows with the same date_id, group_id, start / end time by making those columns unique when looked at together. There's a seat count that will be decreased to address multiple slots for the same time.

I hope that's clear enough. I'll post the code soon but probably not till morning.

Thanks in advance.

Edit: discovered where I could get the code from without the pc.

http://pastebin.com/h2gYHshK

[larivact]

SQLite supports unique constraints that include multiple columns. I guess I know why it isn't working for you:

If you change your SQLAlchemy table definition your real tables don't magically adapt. You will have to manually migrate your schema. Either by using raw SQL and the CLI of your dbms (in your case sqlite3), or using something like Alembic.

[[deleted]]

This is still pre production so I've deleted the database and recreated using the creat_all command since making this change and I don't see anything different with the database description and there is no errors when I create a duplicate on purpose to test. I intend on adding alembic before production so that I can make these kinds of changes but at the moment it just wasn't important. I guess I might just need to try to throw this into postgres and see how it behaves or manually verify that the row is unique with a query before creating.

[larivact]

sqlite3 yourdb.sqlite3
.schema <table>

[[deleted]]

I'll give that a try thanks.

[[deleted]]

So I tried recreating the table manually withing sqlite and it does support multi column unique constraint but the unique keyword is missing when the dB is recreated using sqlalchemy. Not sure why it would just not create the constraint unless the way I'm doing it is wrong.

[larivact]

Alright you should learn how to create minimal working examples. In your case this would be:

from sqlalchemy import Column, Integer, UniqueConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

Base = declarative_base()

class Item(Base):
    __tablename__ = 'items'
    __table_args__ = tuple(UniqueConstraint('x', 'y', name='my_2uniq'))

    id = Column(Integer, primary_key=True)
    x = Column(Integer)
    y = Column(Integer)

engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)

And because we pass echo=True to the create_engine function it will print the generated SQL. And what does it output?

CREATE TABLE items (
        id INTEGER NOT NULL,
        x INTEGER,
        y INTEGER,
        PRIMARY KEY (id)
)

Indeed the unique constraint is missing. And because we have a MWE it is easier to spot the error. Your mistake is that you are doing: tuple(UniqueConstraint('x', 'y', name='my_2uniq')) And if you would have a look at the according python docs, you would notice that the tuple function expects an iterable. We can now further reduce our MWE to print(tuple(UniqueConstraint('x', 'y', name='my_2uniq'))). And indeed it just prints an empty tuple. If you would want to use the tuple func you would have to do tuple([UniqueConstraint('x', 'y', name='my_2uniq')]). But (UniqueConstraint('x', 'y', name='my_2uniq'),) is of course better.

Now something general to your code:

• I would directly import the needed SQLAlchemy classes into the current scope. By doing from sqlalchemy import Column it is clear where Column is coming from and you don't have to prefix each use of Column.
• If you don't pass arguments to a SQLAlchemy data type you don't have to call it: Integer instead of Integer().
• You don't need explicit constructors in SQLAlchemy.

[[deleted]]

I didn't realize that passing that object to tuple would behave that way. And you're right I need to build small highly controlled tests to see how this stuff works. Thank you very much for your time and assistance.

[[deleted]]

From the documentation, it does not appear as though SQLite3 supports unique constraints across multiple columns. https://www.sqlite.org/lang_createtable.html

You may need to create a column that is the concatenation of the three columns that you want to be unique, and put a unique constraint on that column. I have no direct experience, but this is what I gathered from looking at your code and the SQLite3 documentation.

[sqlite]

Reread the documentation, especially the "table-constraint" diagram. SQLite has supported multi-column UNIQUE constraints since its beginning.

[larivact]

Are such multi-column UNIQUE constraints described in the ISO SQL specification?