January 11, 2017 at 10:41 am
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.
Kindest Regards, Rod Connect with me on LinkedIn.
January 11, 2017 at 10:46 am
I don't think that's what the error's saying. If you tried to create a foreign key constraint that already existed (their names have to be unique, you can have multiple on the same table and column), you'd get:
Msg 2714, Level 16, State 5, Line 1
There is already an object named 'Grievance-Grv_Step' in the database.
Check the tables and see whether there's data in the tables that would violate the constraint you're trying to create.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply