Search code examples
sql-server-2008t-sqlconstraintscreate-table

Create table fails because PK constraint is already present


This create fails because there is already a [PK_docEnum1ssThes]

How can I delete [PK_docEnum1ssThes]?

[PK_docEnum1ssThes] is in sys.objects
[docEnum1ssThes] is in sys.objects
but [docEnum1ssThes] is not in SSMS
drop table [docEnum1ssThes] fails
ALTER TABLE [dbo].[docEnum1ssThes] DROP CONSTRAINT [PK_docEnum1ssThes] fails

CREATE TABLE [dbo].[docEnum1ssThes]
(
    [searchID] [smallint] NOT NULL,
    [thesID] [smallint] NOT NULL,
    CONSTRAINT [PK_docEnum1ssThes] PRIMARY KEY CLUSTERED 
    (
      [searchID] ASC,
      [thesID] ASC
    )
);

P.S. there was also an orphaned FK but I was able to delete it at the FK table.

What is interesting is that is the only user table with dbo.
I can change the name of the PK and create the table.
But the table name is then docEnum1ssThes not dbo.docEnum1ssThes

There error I get when I try and drop the table is:

Msg 3701, Level 11, State 5, Line 1 Cannot drop the table 'dbo.docEnum1ssThes', because it does not exist or you do not have permission.

So this may be a permission thing but I am in Role serveradmin

In SSMS in View Object Explorer Details the table was listed
From there I was able to delete the table
How bizarre


Solution

  • In SSMS in View Object Explorer Details the table was listed
    From there I was able to delete the table
    How bizarre