CODEWITHSUNDEEP
pythonpython-3.xpostgresqlsqlalchemy
Miek
Miek

Reputation: 1228

SQLAlchemy Check Constraint With Join

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.

Upvotes: 5

Views: 2031

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28313

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.

Upvotes: 8

Related Questions