How to find which table column corresponds to a constraint in a Firebird database?

Question

I have a legacy Firebird (Version 2.5.2) database and am using FlameRobin (Version 0.0.2.1851) to inspect it.

I have run the following query on the RDB$RELATION_CONSTRAINTS table to search for the constraint named 'INTEG_172':

SELECT *
FROM RDB$RELATION_CONSTRAINTS a
WHERE RDB$CONSTRAINT_NAME = 'INTEG_172'

The query returns the RDB$RELATION_NAME column as being 'Meeting' which I am assuming is the Meeting table. But my question now is: how do I find out which column in the Meeting table this constraint belongs to?

Do you want a query, or do you want to know how to find this in Flamerobin? — Mark Rotteveel, Aug 11 '14 at 13:41
@MarkRotteveel Either a query or in Flamerobin would be suitable. — prajna, Aug 12 '14 at 04:00
The quick solution is to open the properties of the table in flamerobin, tab Constraints. It will show the name and columns. I had expected the answer by Ed Mendez to solve your problem as well. I will see if I can come up with a query later today. — Mark Rotteveel, Aug 12 '14 at 07:09
@praj I found this post very helpful in finding constraints across the whole database: https://www.firebirdnews.org/listing-the-foreign-keys-in-a-firebird-database/#comment-21541 — mickmackusa, Feb 05 '20 at 00:44

score 3 · Accepted Answer · edited May 23 '17 at 12:29

I initially thought it was not possible to infer the column name of some of the constraints, but it turns out the NOT NULL constraints are actually implemented as CHECK constraints.

I have expanded the query by Ed Mendez in his answer to also include check constraints which should give you all constraints and the tables and columns they use (with the exception of the target table+columns of a foreign key as I was too lazy to include RDB$REF_CONSTRAINTS):

SELECT RDB$CONSTRAINT_NAME, RDB$CONSTRAINT_TYPE, 
       RDB$RELATION_NAME, RDB$FIELD_NAME
FROM (
    SELECT REL_CONS.RDB$CONSTRAINT_NAME, REL_CONS.RDB$CONSTRAINT_TYPE, 
           REL_CONS.RDB$RELATION_NAME, ISEG.RDB$FIELD_NAME 
    FROM RDB$RELATION_CONSTRAINTS REL_CONS
    INNER JOIN RDB$INDEX_SEGMENTS ISEG
        ON ISEG.RDB$INDEX_NAME = REL_CONS.RDB$INDEX_NAME
    UNION ALL
    SELECT REL_CONS.RDB$CONSTRAINT_NAME, REL_CONS.RDB$CONSTRAINT_TYPE, 
           REL_CONS.RDB$RELATION_NAME, CH_CONS.RDB$TRIGGER_NAME 
    FROM RDB$RELATION_CONSTRAINTS REL_CONS
    INNER JOIN RDB$CHECK_CONSTRAINTS CH_CONS
        ON CH_CONS.RDB$CONSTRAINT_NAME = REL_CONS.RDB$CONSTRAINT_NAME
) a
WHERE a.RDB$CONSTRAINT_NAME = 'INTEG_172'

score 1 · Answer 2 · edited Aug 12 '14 at 18:22

1

Hopefully this is what you are looking for...

SELECT RDB$INDEX_SEGMENTS.RDB$FIELD_NAME 
FROM   RDB$RELATION_CONSTRAINTS
INNER JOIN RDB$INDEX_SEGMENTS 
    on ( RDB$INDEX_SEGMENTS.RDB$INDEX_NAME = RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME )
WHERE RDB$RELATION_CONSTRAINTS.RDB$CONSTRAINT_NAME = 'INTEG_172'

edited Aug 12 '14 at 18:22

Mark Rotteveel

82,132
136
114
158

answered Aug 11 '14 at 20:43

Ed Mendez

1,333
9
14

The query returned no data. Thanks for the help though. – prajna Aug 11 '14 at 23:29
@prajna This query works on my Firebird 2.5.3. Try dropping the where and include `RDB$RELATION_CONSTRAINTS.RDB$CONSTRAINT_NAME` in the select. The other possibility is that this is a `NOT NULL` constraint, for some reason those aren't directly associated with a column. – Mark Rotteveel Aug 12 '14 at 07:17

score 0 · Answer 3 · answered Aug 11 '14 at 12:22

0

I'd just do:

show table meeting; (via sql)

and read the constraint definition

answered Aug 11 '14 at 12:22

NickUpson

146
2

Thanks but the sql failed to run and got the following: Error: *** IBPP::SQLException *** Context: Statement::Prepare( show table Meeting ) Message: isc_dsql_prepare failed – prajna Aug 11 '14 at 23:28
this was to be run using the sql tool that is part of the firebird installation or 3-rd party tool, not via ibpp – NickUpson Aug 12 '14 at 08:56

How to find which table column corresponds to a constraint in a Firebird database?

3 Answers3

Linked