Sql-server – Constraint error – ‘Could not enable or disable the constraint. See previous errors’

backupsqlsql server

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.

Best Answer

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)