#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Cape Town, South Africa
    Posts
    51
    Rep Power
    11

    How to distinguish between 1:1, 1:m Fk's


    Hi All

    I am retreiving FK's from a FB database, but now i need to know weather the FK's are 1:1, 1:m or what ever.

    Here is the sql that i have at the momment. How would i go about retreiving only the 1:1 and then the 1:m in different SQL statements? In MS Sql 2000 there is flags in the Information schema tables that will give me this info, but how do i find this out with FB?

    select A.RDB$RELATION_NAME,
    A.RDB$CONSTRAINT_NAME,
    A.RDB$CONSTRAINT_TYPE,
    B.RDB$CONST_NAME_UQ,
    B.RDB$UPDATE_RULE,
    B.RDB$DELETE_RULE,
    C.RDB$RELATION_NAME as FK_Table,
    A.RDB$INDEX_NAME,
    D.RDB$FIELD_NAME as FK_Field,
    E.RDB$FIELD_NAME as OnField,
    I.RDB$INDEX_TYPE
    from RDB$REF_CONSTRAINTS B, RDB$RELATION_CONSTRAINTS A, RDB$RELATION_CONSTRAINTS C,
    RDB$INDEX_SEGMENTS D, RDB$INDEX_SEGMENTS E, RDB$INDICES I
    where (A.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY') and
    (A.RDB$CONSTRAINT_NAME = B.RDB$CONSTRAINT_NAME) and
    (B.RDB$CONST_NAME_UQ=C.RDB$CONSTRAINT_NAME) and (C.RDB$INDEX_NAME=D.RDB$INDEX_NAME) and
    (A.RDB$INDEX_NAME=E.RDB$INDEX_NAME) and
    (A.RDB$INDEX_NAME=I.RDB$INDEX_NAME)
    and (A.RDB$RELATION_NAME = 'JOB')
    order by A.RDB$RELATION_NAME, A.RDB$CONSTRAINT_NAME, D.RDB$FIELD_POSITION, E.RDB$FIELD_POSITION

    Thanks Wayne
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Cape Town, South Africa
    Posts
    51
    Rep Power
    11

    Smile


    Don't worry guys.

    I found the info that i needed.

IMN logo majestic logo threadwatch logo seochat tools logo