Post reply

drop index constraint

  • March 9, 2006 at 3:41 pm

    #167483

    I am working on a script to drop a table and recreate it. I get errors in QA that it can't create various constraints because they already exist. Do these not get deleted when the table is dropped? If I put a drop constraint prior to the drop table, won't that generate an error if the table or index doesn't exist? I'm confused.

    Here is part of the offending script:

    BEGIN TRANSACTION

    IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PartyAltID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    DROP TABLE [dbo].[PartyAltID]

    GO

    CREATE TABLE dbo.PartyAltID

     (

     PartyID int NOT NULL,

     AltIDType int NOT NULL,

     AltID varchar(100) NOT NULL,

     Note varchar(200) NULL,

     DateCreated smalldatetime NOT NULL,

     EmpCreated varchar(150) NOT NULL,

     DateUpdated smalldatetime NOT NULL,

     EmpUpdated varchar(150) NOT NULL,

     TmStamp timestamp NULL

    &nbsp  ON [PRIMARY]

    GO

    ALTER TABLE dbo.PartyAltID ADD CONSTRAINT

     DF_PartyAltID_DateCreated DEFAULT (getdate()) FOR DateCreated

    With this error:

    There is already an object named 'DF_PartyAltID_DateCreated' in the database.

  • March 10, 2006 at 12:31 am

    #625855

    You can put an IF EXISTS around the drop constraint, just as you did for the drop table.

    IF

    exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DF_PartyAltID_DateCreated]') and OBJECTPROPERTY(id, N'IsDefaultCnst') = 1)

    ALTER TABLE dbo.PartyAltID DROP CONSTRAINT DF_PartyAltID_DateCreated

    GO

    IF

    exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PartyAltID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    DROP TABLE [dbo].[PartyAltID]

    GO

    HTH

    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

  • March 10, 2006 at 9:47 am

    #626026

    To the best of my knowledge droping a table will drop all associated objects.

    Is the constraint DF_PartyAltID_DateCreated definately on the table PartyAltID?

    I think contraint names have to be unique within the database (or owner ?). Is DF_PartyAltID_DateCreated in sysobjects after yuo drop the table?

  • March 28, 2006 at 7:15 am

    #629147

     

    IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DF_PartyAltID_DateCreated]') and OBJECTPROPERTY(id, N'IsDefaultCnst') = 1)

    ALTER TABLE dbo.PartyAltID DROP CONSTRAINT DF_PartyAltID_DateCreated

    GO

    How do I alter this for a foreign key constraint? (couldn't find it in BOL)

    Sam

  • March 28, 2006 at 10:50 pm

    #629360

    IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PartyAltID_ChildColumn_ParentTable_ParentColumn]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

    ALTER TABLE dbo.PartyAltID DROP CONSTRAINT FK_PartyAltID_ChildColumn__ParentTable_ParentColumn

    Untested, but should work

    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

Viewing 5 posts - 1 through 4 (of 4 total)

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