Constraint error - 'Could not enable or disable the constraint. See previous errors'

Question

Yet I get no 'previous errors'. The line it gives me (by double clicking in SSMS) shows the second alter statement as the culprit:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_A_Audit_AuditTypeID]') 
AND parent_object_id = OBJECT_ID(N'[dbo].[A_Audit]'))
ALTER TABLE [dbo].[A_Audit]  WITH CHECK ADD  CONSTRAINT [FK_A_Audit_AuditTypeID] FOREIGN KEY([AuditTypeID])
REFERENCES [dbo].[T_Type] ([TypeID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_A_Audit_AuditTypeID]') 
AND parent_object_id = OBJECT_ID(N'[dbo].[A_Audit]'))
ALTER TABLE [dbo].[A_Audit] CHECK CONSTRAINT [FK_A_Audit_AuditTypeID] 
GO

Now when I try to remove this piece of the script, I get the same exact error on a different alter constraint statement that's pointing to the same table (T_Type).

The same unaltered script works fine on our TEST database, which is a schema perfect match for our production database. This leads me to believe there is some external factor making this script work in test but not in production.

I was able to find two major differences between our production and test machines:

• The production database machine recently had CommVault software installed. On a different database within the same company, this CommVault installation broke other scripts. (Yes, I realize I could provide more information here - I'm working on getting it now)
• Production is SQL 2005 SP4, while test is SQL 2005 SP3 (9.0.5 vs 9.0.4053).

Any help is greatly appreciated.

Answer 1

I have to ask this first before digging into other details. Have you verified that there is no data in the dbo.A_Audit table that violates the foreign key constraint on the dbo.T_Type table? Does this bring up any data in Production?

SELECT * FROM dbo.A_Audit A
WHERE NOT EXISTS ( SELECT * FROM dbo.T_Type T WHERE A.AuditTypeID = T.TypeID)

Answer 2

This was caused by an application running on someone's dev machine that had a linked-server connection to our production database. This application was blocking the alter statement from running (somehow, I didn't ask).

Thanks for looking.