There is already an object named PK_Survey_Category in the database

Question

There is already an object named PK_Survey_Category in the database

JMI

SSChampion

Points: 11831
More actions
May 18, 2004 at 10:35 am

#152148

History:
1. drop table Survey_Category
2. create table Survey_Category:
CREATE TABLE [dbo].[Survey_Category] (
[Survey_Category_ID] [int] NOT NULL ,
[Survey_ID] [int] NOT NULL ,
[Survey_Category_Type_ID] [int] NOT NULL ,
[Title] [varchar] (128) NOT NULL ,
[Intro] [varchar] (255) ,
[Sequence] [int] NOT NULL ,
[F_Title] [varchar] (128) ,
[F_Intro] [varchar] (255)
) ON [PRIMARY]
3. Run:
alter table Survey_Category add constraint PK_Survey_Category primary key(Survey_Category_ID) with fillfactor=90
ERROR:
There is already an object named 'PK_Survey_Category' in the database
4. alter table Survey_Category drop constraint PK_Survey_Category
5. ERROR:Constraint 'PK_Survey_Category' does not belong to table 'Survey_Category'
-----------------
QUESTION: When I dropped a table didn't it
drop all the constraints automatically?
And if PK_Survey_Category still exists why it doesn't allow me to drop it?

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

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

seemed to drop it fine when I did it on my local server. I expect that somehow the constraint got linked to a different table. Can you check in sysconstraints and see if it's there and where it's linked?

JMI SSChampion Points: 11831 More actions · Answer 2

Hi Steve Jones,

You are right.

PK_Survey_Category existed already in

a different table.

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

helped.

Thank you for your help.

RobO

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

glad I could help. When you see something strange like this with an index, constriant, etc. Often it's bound to another object.

simon_bruce SSC Enthusiast Points: 150 More actions · Answer 4

I find that I get this problem if I rename a table from one name to another as Enterprise Manager does not change the keys/constraints. I find the best way to manage this is take the SQL script for the object and rename all of the objects (keys/constraints etc) before I create it and then delete the old table.

karthik babu Hall of Fame Points: 3335 More actions · Answer 5

this worked for me. many thanks.

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂