>>>>Hi,
>>>>
>>>>I have been looking on various sites for the code to drop a constraint if I know the name of the constraint. Each site has a different code. I am confused. I would like something that would work on SQL Server 2005 and SQL Server 2008. What would you recommend?
>>>>
>>>>TIA.
>>>>
>>>>UPDATE. This is the code that works on my SQL Server 2008. Does it look like the right one?:
>>>>
>>>>
>>>>IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK_table_name_fk_field]')
>>>> AND parent_object_id = OBJECT_ID(N'[dbo].[table_name]'))
>>>> BEGIN
>>>> ALTER TABLE [dbo].[table_name]
>>>> DROP CONSTRAINT [FK_table_name_fk_field]
>>>> END
>>>>
>>>
>>>I don't think you need an extra check for parent_object_id. I believe the constraint names should be unique across database, not table wise (I'm going to test this quickly).
>>>
>>>OK, I verified - the constraint name must be unique in the database.
>>
>>You are saying that if by accident I try to name constraints of two different tables with the same name I will get error?
>
>Yes, exactly. It will not let you create another constraint with the same name. That's why Pinal Dave recommends to include table name in the constraint name.
>
>This is the test I ran:
>
>create table test
>(id int identity, val decimal(10,2))
>
>alter table test add constraint pk_ID primary key (id)
>
>create table test2 (id int identity, val decimal(10,2))
>alter table test2 add constraint pk_ID primary key (id)
>
>and the error I got is:
>Msg 2714, Level 16, State 5, Line 2
>There is already an object named 'pk_ID' in the database.
>Msg 1750, Level 16, State 0, Line 2
>Could not create constraint. See previous errors.
Thank you for the example.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham