SQL Server Performance Forum – Threads Archive
Adding a constraint to a table fails.
Hi All, First, apologies if this has been asked in the forums before, but I have been havingtrouble in adding a constraint to a table. I had previously dropped all the constraints on a database (SQL 2000) and then copied
over my data, when I scripted out the constraints to add them back some of them were
update some were not. Below is what I was running in Query Analyzer: ALTER TABLE [dbo].[Reason] ADD CONSTRAINT [FK_Reason_ReasonType] FOREIGN KEY
(
[ReasonTypeId]
) REFERENCES [ReasonType] (
[ReasonTypeId]
)
GO Result: Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint ‘FK_Reason_ReasonType’. The conflict occurred in database ‘SecurityMaster3’, table ‘ReasonType’, column ‘ReasonTypeId’. Not sure if there was corrupted data on the ReasonType table but both databases were I am synchronizing to have the same data. I appreciate any help you can give.
– are the column names correct ?
– are the columns of the same datatype ? (and length)
May be data might be missing in the FK table… If you don’t care then try to create FK with NOCHECK option.. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
See thsi blog:http://blogs.msdn.com/mohamed_shara…e-when-you-create-foreign-key-constraint.aspx and try to correct the issues mentioned. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>