Post reply

How to drop a constraint where the constraint name is variable?

  • April 21, 2006 at 1:45 am

    #112587

    Hi,

    In our product database we created a Primary Key Constraint without a specific name. The PK Constraint name may be different in different database servers. For e.g, PK__TestTable__753864A1 .

    Now we want to drop this table's constraint using a runtime SQL script during the product install. Something like as follows:

    DECLARE @val VARCHAR(50)

    select @val = name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'

    ALTER TABLE [dbo].[VOIP_CUEUsers] DROP CONSTRAINT @val

    The constraint's name will be a variable and how can we drop it? The above query throws an error as you know.

    Thanks in advance.

    Regards

    Unnic

  • April 21, 2006 at 1:51 am

    #633422

    You'll need to use dynamic SQL in this case, as an alter table can't take a variable.

    DECLARE @val VARCHAR(50)

    select @val = name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'

    EXECUTE ('ALTER TABLE [dbo].[VOIP_CUEUsers] DROP CONSTRAINT ' + @val)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 21, 2006 at 2:42 am

    #633428

    Thanks Gila..

    But the above query throws an error:

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near 'CONSTRAINT'.

    Any idea how can I solve it?

    Regards

    Unnic

  • April 21, 2006 at 3:28 am

    #633443

    If you print the string instead of executing it, what does it contain?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 21, 2006 at 3:43 am

    #633444

    Nothing is getting printed.

    For e.g:

    DECLARE @val VARCHAR(50)

    select @val = name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'

    PRINT ('ALTER TABLE [dbo].[VOIP_CUEUsers] DROP CONSTRAINT ' + @val)

    This yield nothing. But there is no error!

  • April 21, 2006 at 3:56 am

    #633445

    What does this return?

    DECLARE @val VARCHAR(50)

    SELECT name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'

    select @val = name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'

    PRINT ' @val has the value ' + ISNULL(@Val,'')

    PRINT 'ALTER TABLE [dbo].[VOIP_CUEUsers] DROP CONSTRAINT [' + ISNULL(@Val,'') + ']'

    Is there a pk with the name starting PK__TestTable? A better way to get the pk name of the table 'VOIP_CUEUsers' something like this

    SELECT name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsPrimaryKey') = 1 AND OBJECT_NAME(parent_obj) = 'VOIP_CUEUsers'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 21, 2006 at 4:03 am

    #633447

    Thank you very much Gila..

    It was my mistake that I used PK_TestTable, but the table, TestTable and the constraint was not existing. When I used an existing table it worked !

    Thanks

    Regards

    Unnic

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply