CODEWITHSUNDEEP
firebirdfirebird2.5
prajna
prajna

Reputation: 1637

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

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?

Upvotes: 2

Views: 3356

Answers (3)

Ed Mendez
Ed Mendez

Reputation: 1683

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'

Upvotes: 1

Mark Rotteveel
Mark Rotteveel

Reputation: 108971

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'

Upvotes: 4

NickUpson
NickUpson

Reputation: 146

I'd just do:

show table meeting; (via sql)

and read the constraint definition

Upvotes: 0

Related Questions