Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Question Unanswered: Can't drop constraint...?

    Hello, I have hit the wall here...can't make sense of this one.

    I have a script that creates a PRIMARY KEY constraint called PK_tblDRG
    CODE:

    ALTER TABLE [dbo].[tblDRG]
    ALTER COLUMN [record_id] Uniqueidentifier NOT NULL
    Go
    ALTER TABLE [dbo].[tblDRG]
    WITH NOCHECK ADD PK_tblDRG PRIMARY KEY CLUSTERED
    (
    [record_id]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]

    All is fine with that. I run this to verify:

    EXEC sp_pkeys @table_name = 'tblDRG'
    ,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'

    which returns this:

    TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
    Relational_05Q3 dbo tblDRG record_id 1 PK_tblDRG

    Now I want to drop the constriant if it exists:

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK__tblDRG]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
    ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG

    AND I get this in return:

    The command(s) completed successfully.

    So, lets double check:

    EXEC sp_pkeys @table_name = 'tblDRG'
    ,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'

    AND I STILL GET THIS:

    TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
    Relational_05Q3 dbo tblDRG record_id 1 PK_tblDRG

    Hmmmmm. Looks like the IF statement didn't do it's job. Ok fine. I'll just kill it myself:

    ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG

    AND I GET THIS?!?!?!?!

    Server: Msg 3728, Level 16, State 1, Line 1
    'PK__tblDRG' is not a constraint.

    What am I not getting here? Is it me...I can take If I am a bone head here.

    Any help would be appreciated. Thanks!

  2. #2
    Join Date
    Nov 2003
    Posts
    39

    Can't drop costraint.

    Nevermind...I am a moron.
    PK__tblDRG doesn't exists because it's PK_tblDRG!!!

    Sorry!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •