Treating `null` as a distinct value in a table unique constraint

Question

I have a table which I use to define default and custom options for a client. If the custom_id field has a value then it represents a record for a unique custom job. If it is empty then the record represents the default option for the client.

My issue is that I would like to enforce uniqueness in two situations:

custom_id, client and option are all non-null
client and option are non-null, but custom_id is null

The table definition below works in the first case, but not in the second, since null is not treated as a value. Is there a way to make it so that null is treated as a value?

class OptionTable(Base):
    __tablename__ = "option_table"
    __table_args__ = (
        UniqueConstraint("custom", "client", "option", name="uix_custom_client_option"),
    )

    id = Column(Integer, primary_key=True)
    custom_id = Column(Integer, ForeignKey("custom.id"), nullable=True)
    client = Column(String, nullable=False)
    option = Column(String, nullable=False)

Here is some sample data and the result when they are added in order:

+----+----------+----------+--------+---------------------------------------------+
| id | CustomID |  Client  | Option |                   result                    |
+----+----------+----------+--------+---------------------------------------------+
|  1 | 123      | MegaCorp | Apple  | OK                                          |
|  2 | 123      | MegaCorp | Apple  | not unique                                  |
|  3 | NULL     | MegaCorp | Apple  | OK                                          |
|  4 | NULL     | MegaCorp | Google | OK                                          |
|  5 | NULL     | MegaCorp | Google | this one should fail, but currently doesn't |
+----+----------+----------+--------+---------------------------------------------+

This related answer does what I'm looking for, using MySQL. The ideal solution would be to do this using sqlalchemy.

Can you include sample data which helps to explain what you are after here? — Tim Biegeleisen, Aug 25 '19 at 13:49
The [documentation](https://www.postgresql.org/docs/9.4/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS) suggests this is standard SQL behavior; NULL cannot violate a uniqueness constraint. You need a real value to indicate the lack of a custom ID. — chepner, Aug 25 '19 at 14:15
@chepner Yes, this is absolutely standard SQL behaviour. I'm asking if there is a way to get around it using sqlalchemy — Jamie Bull, Aug 25 '19 at 14:16
There is a related question which has before insert and before update statements for MySQL which achieve what I'm looking for. Added to the question. — Jamie Bull, Aug 25 '19 at 14:19
The problem is that your database itself (to which SQLAlchemy is just an interface) allows row 5. I'd suggest using a non-nullable Boolean-valued "has_custom" column instead of the custom ID itself in the uniqueness constraint. (Of course, then the problem becomes preventing a record from having a True/Null combination of the two columns.) — chepner, Aug 25 '19 at 14:25
@chepner Thanks for the pointers. I've found a solution using partial indexes. I'll add an answer once I have it worked up. — Jamie Bull, Aug 25 '19 at 14:29

score 5 · Accepted Answer · answered Aug 25 '19 at 14:42

Based on the approach recommended in this answer, the solution is to create two partial indexes.

Using sqlalchemy for the example in the question, this looks like:

class OptionTable(Base):
    __tablename__ = "option_table"

    id = Column(Integer, primary_key=True)
    custom_id = Column(Integer, ForeignKey("custom.id"), nullable=True)
    client = Column(String, nullable=False)
    option = Column(String, nullable=False)

    __table_args__ = (
        Index(
            "uix_custom_client_option", 
            "custom_id", 
            "client", 
            "option", 
            unique=True, 
            postgresql_where=custom_id.isnot(None)
        ),
        Index(
            "uix_client_option", 
            "client",  
            "option", 
            unique=True, 
            postgresql_where=custom_id.is_(None)
        ),
    )

Laurenz Albe · Answer 2 · 2019-08-26T09:05:06.417

I would do

CREATE UNIQUE INDEX ON atable
   (client, option, coalesce(custom_id, -42));

where -42 is a value that cannot occur for custom_id.

How does it work?

If there are two rows with identical client, option and custom_id, all of which are NOT NULL, it will work just like a regular unique index and will prevent the second row from being added.

If there are two rows with identical client and option that both have custom_id IS NULL, then the index will prevent the second row from being added, because it indexes -42 instead of NULL, and the two index tuples would be identical.

Treating `null` as a distinct value in a table unique constraint

2 Answers2

Linked