firebirdfirebird2.5

All Firebird check constraints with it's condition


I want to list out all the Firebird check constraints and it's conditions.

I have used the below query, but this query is only showing the constraints name and its table. I want to see the constraint condition as well.

SELECT * FROM RDB$RELATION_CONSTRAINTS

Solution

  • Check constraints are implemented with triggers. The following query will show you the source of check constraints.

    select cc.RDB$CONSTRAINT_NAME, t.RDB$RELATION_NAME, t.RDB$TRIGGER_TYPE, t.RDB$TRIGGER_SOURCE
    from RDB$RELATION_CONSTRAINTS rc
    inner join RDB$CHECK_CONSTRAINTS cc using (RDB$CONSTRAINT_NAME) 
    inner join RDB$TRIGGERS t using (RDB$TRIGGER_NAME, RDB$RELATION_NAME)
    where rc.RDB$CONSTRAINT_TYPE = 'CHECK'
    

    This query will report the constraint twice, because in current versions of Firebird check constraints are implemented with two separate triggers: a before insert trigger (type = 1) and a before update trigger (type = 3). This is an implementation artifact.

    A query getting the source of just one of the triggers (as both are identical):

    select 
      rc.RDB$CONSTRAINT_NAME, 
      rc.RDB$RELATION_NAME, 
      (select first 1 RDB$TRIGGER_SOURCE
       from RDB$TRIGGERS
       where RDB$TRIGGER_NAME = cc.RDB$TRIGGER_NAME) as TRIGGER_SOURCE
    from RDB$RELATION_CONSTRAINTS rc
    inner join RDB$CHECK_CONSTRAINTS cc using (RDB$CONSTRAINT_NAME) 
    where rc.RDB$CONSTRAINT_TYPE = 'CHECK'