Constraints issue 1

[Luvsql]

I have an accounting software package that I'm trying to upgrade but it keeps failing when it creates a table and tries to add a constraint to it. Instead of trying to fix the upgrade, I'd like to recreate the table and contraint in the new version, so the error will pass the next time I run the upgrade.


I have all the sql scripts to create the table and contraint. The new table gets created fine, however, when I try and run the create constraint script I get errors.

Here is the script to create the constraint:

if exists (select * from sysobjects where name = 'RM_Customer_MSTR_NA')
ALTER TABLE dbo.RM00101
DROP CONSTRAINT RM_Customer_MSTR_NA
GO
ALTER TABLE dbo.RM00101 WITH NOCHECK
ADD CONSTRAINT RM_Customer_MSTR_NA CHECK
((CPRCSTNM <> '' AND BALNCTYP = 0) or
(CPRCSTNM = '' AND BALNCTYP = 1) or
(CPRCSTNM = '' AND BALNCTYP = 0))

GO

Here is the error:

Msg 3733, Level 16, State 2, Line 3
Constraint 'RM_Customer_MSTR_NA' does not belong to table 'RM00101'.
Msg 3727, Level 16, State 0, Line 3
Could not drop constraint. See previous errors.
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'RM_Customer_MSTR_NA' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

I can't manually add the constraint because it says there is already an object with that name and I can't drop the constraint because it's not connected to the table.

Where is this already existing constraint so I can drop it?

 

[bborissov]

What that query returns:

select OBJECT_NAME(parent_obj) AS TableName,
       SCHEMA_NAME(uid) AS SchemaName 
from sysobjects WHERE name = 'RM_Customer_MSTR_NA'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.