I'm trying to create a foreign key constraint in database, but I got this error:
'Grievance (LRAT)' table saved successfully
'Lookup_Results (LRAT)' table saved successfully
'Grv_Step (LRAT)' table
- Unable to create relationship 'Grievance-Grv_Step'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Grievance-Grv_Step". The conflict occurred in database "LRAT_DEV", table "LRAT.Lookup_Results", column 'ResultID'.
I was using the Relationships button off of the table's definition in SSMS, when I tried to create my FK relationship.
So then I looked at Constraints in SSMS for the table LRAT.Lookup_Results. The only thing there is a default value constraint. I looked at the Keys and the only thing there was the definition for the primary key.
So then I went looking for a SQL script to list all foreign key constraints. I found one here on SSC, but it didn't list the FK constraint Grievance-Grv_Step. So next I went to find another SQL script that would list FK constraints and I came across this one:
SELECT RC.CONSTRAINT_NAME FK_Name
, KF.TABLE_SCHEMA FK_Schema
, KF.TABLE_NAME FK_Table
, KF.COLUMN_NAME FK_Column
, RC.UNIQUE_CONSTRAINT_NAME PK_Name
, KP.TABLE_SCHEMA PK_Schema
, KP.TABLE_NAME PK_Table
, KP.COLUMN_NAME PK_Column
, RC.MATCH_OPTION MatchOption
, RC.UPDATE_RULE UpdateRule
, RC.DELETE_RULE DeleteRule
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
from Stack Overflow (http://stackoverflow.com/questions/1229968/is-it-possible-to-list-all-foreign-keys-in-a-database
), but that also didn't list Grievance-Grv_Step.
In looking at what I was trying to do, I was trying to create a FK constraint which apparently already existed, even though when I first looked to see if there were any FK constraints on LRAT.Lookup_Results, I couldn't find any. So it looks to me as though there does exist a FK constraint definition, somewhere within this database, but I cannot find it and cannot list it. And yet if I try to create it, it will block it. Of course I could be wrong, but that's the way it looks to me at the moment.
I could use help in trying to figure out what's going on, please.
Connect with me on LinkedIn