Orphan constraints in SQL Server database

Question

Running the following query results in all constraints in our client's database. However, several rows in the result set don't seem to have a parent, i.e. parent_object_id = 0 and OBJECT_NAME(parent_object_id) returns NULL.

SELECT name, type_desc, OBJECT_NAME(parent_object_id), parent_object_id
FROM sys.objects
WHERE is_ms_shipped = 0
AND type_desc LIKE '%_CONSTRAINT'

Does this mean there are orphan constraints in the database? If so, how do I remove these?

From their names, I can see they are leftovers from before a large amount changes were made to the structure.

This should be impossible. Have you run have DBCC lately? – gbn May 19 '11 at 12:35 — gbn, May 19 '11 at 12:35

bernhof · Accepted Answer · 2011-05-27T10:01:55.580

5

Using sp_helptext I can see they were created using a CREATE DEFAULT statement, e.g.:

CREATE DEFAULT dbo.MyDefault AS 2

This means they are simply unbound defaults, which can be bound using the sp_binddefault according to MSDN, and removed with a simple DROP DEFAULT statement:

DROP DEFAULT dbo.MyDefault

I wasn't aware of this syntax, which apparently will be removed in a future version of SQL Server, according to the aforementioned MSDN article.

edited May 27 '11 at 10:01

answered May 19 '11 at 14:04

bernhof

6,219
2
45
71

Good call. I've not used this feature for many years and didn't think of it. – gbn May 20 '11 at 13:41

Orphan constraints in SQL Server database

1 Answers1