SQL Check Constraints: Ensuring One Item Has One Owner

Items can be referenced in several tables in the database, which means they have multiple foreign keys. How do I ensure ownership of an item?

Specifying the problem more clearly, there are several database models in my ORM that can have an Item: Characters, NPCs, and Storage. Storage in particular may reference either a location's collection of Items, or a pocket space containing items that can be accessed more generally.

These three postgresql tables are referenced in SQLAlchemy by foreign keys in the Item table, and I want to ensure that there is only one "owner" of an item at a time. That way, there are no surprises for players who expect sole agency over an item in their inventory. Doing so at the database level provides protections against race conditions and bugs in the application code.

This is rather like having a big XOR (exclusive OR) check for all the ownership columns in the table. However, this method scales easily beyond the trivial case of a couple of columns.

SQLAlchemy CheckConstraint For Ensuring Exactly One Foreign Key Set In Row

My approach currently is to use a check constraint like the following:

class Item(Base):
    __tablename__ = "item"
    character_id = Column(Integer, ForeignKey("character.id"), index=True, nullable=True)
    id = Column(Integer, autoincrement=True, primary_key=True)
    npc_id = Column(Integer, ForeignKey("npc.id"), index=True, nullable=True)
    pocket_storage_id = Column(Integer, ForeignKey("pocketstorage.id"), index=True, nullable=True)

    __table_args__ = (
        CheckConstraint(
            "(character_id IS NOT NULL)::integer "
            "+ (npc_id IS NOT NULL)::integer "
            "+ (pocket_storage_id IS NOT NULL)::integer "
            "= 1",
            name="ck_owner_xor",
        ),
    )

The way this works is by adding up the non-null columns and ensuring that there is precisely one such column. To do so, I'm passing a SQL string into the CheckConstraint object that checks if a column is set. This returns a boolean value, which can be type-cast to an integer. Either 0, or 1, depending on whether it's false or true.

If we sum the type-cast booleans, we'll get a number that represents how many columns in the row are non-null. From there it's easy to check that it's what we want, either <= 1 or = 1 exactly.

In my case, objects with exactly 0 ownership relations would be entirely dead weight cruft, unable to be accessed. Thus, I only allow precisely 1 owner.

Hope this helps others, since it was a database trick I had to learn about, myself!