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:

1. custom_id, client and option are all non-null
2. 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.

Answer 1

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)
        ),
    )