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
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'