Loading ...
Sorry, an error occurred while loading the content.

Help with Dropping Indexes?

Expand Messages
  • bgugler010101
    Hello... I am currently working on redesigning our company s database with another developer before we begin creating a new application in .NET. In addition to
    Message 1 of 3 , May 6, 2005
    • 0 Attachment
      Hello...

      I am currently working on redesigning our company's database with
      another developer before we begin creating a new application in .NET.

      In addition to modifying table/field names, etc, we wish to remove
      all constraints (PK, FK, defaults, etc), indexes, etc so that we can
      build them anew.

      I am running into a problem deleting an index/PK on a nonclustered,
      unique primary key. Below are the steps I took and their respective
      results:

      1) First, I tried to delete the PK constraint on the 'Party' table
      (all others worked just fine):

      ALTER TABLE party DROP CONSTRAINT PK_Party

      I receive the following error msg:
      Cannot drop index 'PK_Party' because it enforces the full-text key
      for table 'party'.

      2) I ran this query I found to see the indexes:

      EXEC sp_msforeachtable @command1= "PRINT '?' EXEC sp_helpindex
      @objname =
      '?'"

      3) Along with some other indexes for the party table (which I am able
      to successfully remove), I see this line:

      PK_Party nonclustered, unique, primary key located on PRIMARY ID

      Thinking that it might be an index, I try this:

      DROP INDEX party.PK_Party

      And receive this:
      An explicit DROP INDEX is not allowed on index 'party.PK_Party'. It
      is being used for PRIMARY KEY constraint enforcement.

      I have tried many things to resolve this (note to self: newts have no
      place in database work) ...so if anyone has ideas, you would surely
      help me preserve my sanity....

      Thanks!
      Bonnie
    • Mike Good
      Full text index not same as normal index. Lookup How to remove full-text indexing on a table (Enterprise Manager) in BOL. Drop the full-text index, then
      Message 2 of 3 , May 6, 2005
      • 0 Attachment
        Full text index not same as normal index. Lookup "How to remove full-text
        indexing on a table (Enterprise Manager)" in BOL. Drop the full-text index,
        then your original alter table drop constraint stmt will work.

        -----Original Message-----
        From: tampasql@yahoogroups.com [mailto:tampasql@yahoogroups.com]On
        Behalf Of bgugler010101
        Sent: Friday, May 06, 2005 11:52 AM
        To: tampasql@yahoogroups.com
        Subject: [tampasql] Help with Dropping Indexes?


        Hello...

        I am currently working on redesigning our company's database with
        another developer before we begin creating a new application in .NET.

        In addition to modifying table/field names, etc, we wish to remove
        all constraints (PK, FK, defaults, etc), indexes, etc so that we can
        build them anew.

        I am running into a problem deleting an index/PK on a nonclustered,
        unique primary key. Below are the steps I took and their respective
        results:

        1) First, I tried to delete the PK constraint on the 'Party' table
        (all others worked just fine):

        ALTER TABLE party DROP CONSTRAINT PK_Party

        I receive the following error msg:
        Cannot drop index 'PK_Party' because it enforces the full-text key
        for table 'party'.

        2) I ran this query I found to see the indexes:

        EXEC sp_msforeachtable @command1= "PRINT '?' EXEC sp_helpindex
        @objname =
        '?'"

        3) Along with some other indexes for the party table (which I am able
        to successfully remove), I see this line:

        PK_Party nonclustered, unique, primary key located on PRIMARY ID

        Thinking that it might be an index, I try this:

        DROP INDEX party.PK_Party

        And receive this:
        An explicit DROP INDEX is not allowed on index 'party.PK_Party'. It
        is being used for PRIMARY KEY constraint enforcement.

        I have tried many things to resolve this (note to self: newts have no
        place in database work) ...so if anyone has ideas, you would surely
        help me preserve my sanity....

        Thanks!
        Bonnie










        Yahoo! Groups Links
      • Bonnie Gugler
        Hi Mike, I had tried that, but it is greyed out.which further led to my confusion. Am I just doing things in the incorrect order perhaps? Thanks, Bonnie _____
        Message 3 of 3 , May 6, 2005
        • 0 Attachment

          Hi Mike,

           

          I had tried that, but it is greyed out…which further led to my confusion.  Am I just doing things in the incorrect order perhaps?

           

          Thanks,

          Bonnie

           


          From: tampasql@yahoogroups.com [mailto:tampasql@yahoogroups.com] On Behalf Of Mike Good
          Sent: Friday, May 06, 2005 11:59 AM
          To: tampasql@yahoogroups.com
          Subject: RE: [tampasql] Help with Dropping Indexes?

           

          Full text index not same as normal index.  Lookup "How to remove full-text
          indexing on a table (Enterprise Manager)" in BOL.  Drop the full-text index,
          then your original alter table drop constraint stmt will work.

          -----Original Message-----
          From: tampasql@yahoogroups.com [mailto:tampasql@yahoogroups.com]On
          Behalf Of bgugler010101
          Sent: Friday, May 06, 2005 11:52 AM
          To: tampasql@yahoogroups.com
          Subject: [tampasql] Help with Dropping Indexes?


          Hello...

          I am currently working on redesigning our company's database with
          another developer before we begin creating a new application in .NET.

          In addition to modifying table/field names, etc, we wish to remove
          all constraints (PK, FK, defaults, etc), indexes, etc so that we can
          build them anew.

          I am running into a problem deleting an index/PK on a nonclustered,
          unique primary key.  Below are the steps I took and their respective
          results:

          1) First, I tried to delete the PK constraint on the 'Party' table
          (all others worked just fine):

          ALTER TABLE party DROP CONSTRAINT PK_Party

          I receive the following error msg:
          Cannot drop index 'PK_Party' because it enforces the full-text key
          for table 'party'.

          2) I ran this query I found to see the indexes:

          EXEC sp_msforeachtable @command1= "PRINT '?'  EXEC sp_helpindex
          @objname =
          '?'"

          3) Along with some other indexes for the party table (which I am able
          to successfully remove), I see this line:

          PK_Party nonclustered, unique, primary key located on PRIMARY ID

          Thinking that it might be an index, I try this:

          DROP INDEX party.PK_Party 

          And receive this:
          An explicit DROP INDEX is not allowed on index 'party.PK_Party'. It
          is being used for PRIMARY KEY constraint enforcement.

          I have tried many things to resolve this (note to self: newts have no
          place in database work) ...so if anyone has ideas, you would surely
          help me preserve my sanity....

          Thanks!
          Bonnie










          Yahoo! Groups Links






        Your message has been successfully submitted and would be delivered to recipients shortly.