July 2, 2012 at 5:13 am
Dear All,
I’ve run the following script, but it still throws the error message.:
IFEXISTS (SELECT* FROM sys.foreign_keysWHERE object_id= OBJECT_ID(N'[dbo].["foreign key name Constraint name"]')
AND parent_object_id= OBJECT_ID(N'[dbo].["table name"]'))
ALTERTABLE [dbo].["table name"] DROP CONSTRAINT ["foreign key name Constraint name"]
GO
ALTERTABLE [dbo].["table name"] WITH CHECK ADD CONSTRAINT ["foreign key name Constraint name"]FOREIGNKEY(["coulmn name"])
REFERENCES [dbo].["table name" ](["coulmn name"])
GO
Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "foreign key name Constraint name". The conflict occurred in database "Database name", table "table name", column 'column name'.
ALTERTABLE [dbo].["table name"]CHECKCONSTRAINT [ "foreign key name"]
GO
Would anyone let me know what I'm doing wrong please?
Thank you in advance!
July 2, 2012 at 5:20 am
Hi, I'm guessing a bit, but I think the table you're trying to create the FK on contains some data that violates that FK.
Do the FK you're dropping & the one you're creating reference the same columns?
Thanks
July 2, 2012 at 5:25 am
Please do sp_help table_name and post results; we trying to figure out the already existing constraints in such a table.
Either constraint already exists or creation of the constraint is creating orphan rows which is a violation of referential integrity.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 2, 2012 at 5:30 am
Run one batch at a time. After first batch run the subquery in if exists to check whether the constraint is dropped or not.
Why do you need the 3rd batch?When you are creating the constraint you are spficying with check that means it is alredy checked for the violations. This is redudant.
ALTERTABLE [dbo].["table name"]CHECKCONSTRAINT [ "foreign key name"]
If you are dropping the constraint and then loading some data and then again creating the constaint. Then you should try to disable the constraint by using nocheck and enable it again with check.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply