I am listing all FK constraints for a given table using
INFORMATION_SCHEMA set of views with the following query:
SELECT X.UNIQUE_CONSTRAINT_NAME, "C".*, "X".* FROM "INFORMATION_SCHEMA"."KEY_COLUMN_USAGE" AS "C" INNER JOIN "INFORMATION_SCHEMA"."REFERENTIAL_CONSTRAINTS" AS "X" ON "C"."CONSTRAINT_NAME" = "X"."CONSTRAINT_NAME" AND "C"."TABLE_NAME" = 'MY_TABLE' AND "C"."TABLE_SCHEMA" = 'MY_SCHEMA'
Everything works perfectly well, but for one particular constraint the value of
UNIQUE_CONSTRAINT_NAME column is wrong, and I need it in order to find additional information from the referenced Column. Basically, for most of the rows the
UNIQUE_CONSTRAINT_NAME contains the name of the unique constraint (or PK) in the referenced table, but for one particular
FK it is the name of some other unique constraint.
I dropped and re-created the FK - did not help.
My assumption is that the meta-data is somehow screwed. Is there a way to rebuild the meta data so that the
INFORMATION_SCHEMA views would actually show the correct data?
edit-1: sample db structure
CREATE TABLE MY_PARENT_TABLE ( ID INTEGER, NAME VARCHAR, --//... CONSTRAINT MY_PARENT_TABLE_PK PRIMARY KEY CLUSTERED (ID) ) CREATE UNIQUE NONCLUSTERED INDEX MY_PARENT_TABLE_u_nci_ID_LongName ON MY_PARENT_TABLE (ID ASC) INCLUDE (SOME_OTHER_COLUMN) CREATE TABLE MY_CHILD_TABLE ( ID INTEGER, PID INTEGER, NAME VARCHAR, CONSTRAINT MY_CHILD_TABLE_PK PRIMARY KEY CLUSTERED (ID) ,CONSTRAINT MY_CHILD_TABLE__MY_PARENT_TABLE__FK FOREIGN KEY (PID) REFERENCES MY_PARENT_TABLE (ID) ON UPDATE NO ACTION ON DELETE NO ACTION )
I expect the
UNIQUE_CONSTRAINT_NAME to be
MY_PARENT_TABLE_PK, but what I am
Having looked at the structure, I see that in fact there are 2
UNIQUE constaints on that column -
PK and the
MY_PARENT_TABLE_u_nci_ID_LongName. So the real question should probably be: why does it take some other unique index and not the PK?