3

Based on the answer to sqlalchemy unique across multiple columns (I'm using the declarative version, given below) I'm trying to declare a multi-column uniqueness constraint.

class Location(Base):
    __tablename__ = 'locations'
    id = Column(Integer, primary_key = True)
    customer_id = Column(Integer, ForeignKey('customers.customer_id'), nullable=False)
    location_code = Column(Unicode(10), nullable=False)
    __table_args__ = (UniqueConstraint('customer_id', 'location_code', name='_customer_location_uc'),
                 )

However when I create multiple items with the same customer_id and location_code I don't get any uniqueness exceptions from SQLAlchemy, and the items are successfully created.

In the comments below the linked answer, there's a partial conversation about specifying UniqueConstraint being only part of the DDL. What's the other part that I need to do for this constraint to get enforced?

Ng Oon-Ee
  • 1,193
  • 1
  • 10
  • 26
  • 1
    Dammit, I knew it was something simple. One of my columns was nullable and all null values are unique! Voting to close, this will not be useful to anyone in future... – Ng Oon-Ee Apr 02 '18 at 07:04
  • Actually your question is good - others may find it useful. Mind answering it with your findings? – Patrick B. Jul 11 '18 at 12:03
  • The comment explains everything, really. All null values are assumed to be unique. – Ng Oon-Ee Jul 12 '18 at 12:24
  • Yeah, but a comment is not an answer, but your comment is the answer to your question, so you should make it one. Stackoverflow is good, because of that. – Patrick B. Jul 12 '18 at 15:09

1 Answers1

0

Postgres constraints will not enforce only one null value for a field:

https://www.postgresql.org/docs/9.4/ddl-constraints.html

It should be noted that a check constraint is satisfied if the check expression evaluates to true or the null value. Since most expressions will evaluate to the null value if any operand is null, they will not prevent null values in the constrained columns. To ensure that a column does not contain null values, the not-null constraint described in the next section can be used.

Therefore, in order for the constraint to work, as the author points out in their own comment, set the columns you want to constrain to nullable=False.

(Credit to Ng Onne-Ee for answering their own question, but thought it might be helpful to others to have an explicit answer).

David Henderson
  • 56
  • 3