SQLAlchemy Check Constraint With Join

Question

I have created a many to many relationship in SQLAlchemy using something like this:

b_c = Table('b_c', 
            Column('b_id', UUIDType(binary=False), ForeignKey('b.id'), primary_key=True),
            Column('c_id', UUIDType(binary=False), ForeignKey('c.id'), primary_key=True)
           )

Where c and b are tables with only an id column (UUIDType(binary=false)) and the models similar to this:

class A(object):
    __tablename__ = 'a'
    id = Column('id', UUIDType(binary=False), default=uuid.uuid4, primary_key=True)

class B(object):
    __tablename__ = 'b'
    id = Column('id', UUIDType(binary=False), default=uuid.uuid4, primary_key=True)
    a_id = Column(UUIDType(binary=False), ForeignKey('a.id'), nullable=False)
    a = relationship('A')

class C(object):
    __tablename__ = 'c'
    id = Column('id', UUIDType(binary=False), default=uuid.uuid4, primary_key=True)
    a_id = Column(UUIDType(binary=False), ForeignKey('a.id'), nullable=False)
    a = relationship('A')

This relationship works perfectly fine, and I'm able to filter the B and C objects to a parent A for my usage scenario. However, to ensure integrity of the data beyond the logic that uses these models, is there any best practice for requiring that for any relation b_c, b.a must equal c.a ?

Apologies if this is answered elsehwere, but any examples I have found are simple CHECK constraints against values in the table itself, and nothing requiring values of joined tables.

Thank you for pointing that out, missed that when trying to remove some of the unnecessary details - Updated the question — Miek, Feb 26 '18 at 18:43

score 8 · Accepted Answer · answered Feb 26 '18 at 19:39

From the documentation:

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. The system column tableoid may be referenced, but not any other system column.

What you are describing cannot be done via check constraints, but may be achieved via sql triggers before insert or update:

Here's a postgresql function & trigger definition to check for equality of the referred table's a_id foreign key.

CREATE FUNCTION ckref_b_c() RETURNS trigger AS $ckref_b_c$
  DECLARE
  bid uuid;
  cid uuid;
  BEGIN
    select a_id INTO bid FROM b WHERE id = NEW.b_id;
    select a_id INTO cid FROM c WHERE id = NEW.c_id;
    IF bid != cid THEN 
        RAISE EXCEPTION 'associated records do not refer to same parent in `a`';
    END IF;
    RETURN NEW;
  END;
 $ckref_b_c$ LANGUAGE plpgsql;

CREATE TRIGGER ckref_b_c BEFORE INSERT OR UPDATE ON b_c
  FOR EACH ROW EXECUTE PROCEDURE ckref_b_c();

You can execute these queries through the sqlalchemy engine after the tables are created. Sqlalchemy also has an event system which you could use to emit these queries automatically.

I think you've nailed it. As I was looking into this more today, it definitely felt like I was going down a very ugly road trying to perform a CHECK with subqueries. As I found out (and you also mention in your answer), that is not even allowed in postgres: https://stackoverflow.com/questions/10179121/sql-sub-queries-in-check-constraint This definitely appears to be the appropriate solution, as I have found virtually no one recommending the function "hack" to make a constraint. I've generally tried to avoid triggers, but it seems appropriate here. Thank you for your help! — Miek, Feb 26 '18 at 19:58

SQLAlchemy Check Constraint With Join

1 Answers1