Where did it go?

  • hi all -

    I went to the diagram tool to create a foriegn key relationship, but when I went to save it says it already exists with the same name. I looked at the system table for sql 2000 sysforeignkeys (or something like that) and it has noting in it, so how could it be find a foreign key with the same name as mine?

  • Hi,

    I faced the similiar situation like yours and find a solution in a blog -

    Here is the query which is using INFORMATION_SCHEMA views that returns both sides of all foreign key relationships, as well as the name of the foreign key constraint

    SELECT

    FK_Table = FK.TABLE_NAME,

    FK_Column = CU.COLUMN_NAME,

    PK_Table = PK.TABLE_NAME,

    PK_Column = PT.COLUMN_NAME,

    Constraint_Name = C.CONSTRAINT_NAME

    FROM

    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

    INNER JOIN

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK

    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

    INNER JOIN

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK

    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME

    INNER JOIN

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU

    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

    INNER JOIN

    (

    SELECT

    i1.TABLE_NAME, i2.COLUMN_NAME

    FROM

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1

    INNER JOIN

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2

    ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME

    WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'

    ) PT

    ON PT.TABLE_NAME = PK.TABLE_NAME

    -- optional:

    ORDER BY

    1,2,3,4

    Hope it helps to find the name of your foreign key.

    MJ:cool:

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply