[SOLVED] All Firebird check constraints with it's condition

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'