Member Avatar for Joemeister

I'm trying to do a simple a database SQL script where I simply need tocreate a database as well as 4 tables within. So far I'm getting a very annoying error that states: There is already an object named 'DEVICE_TYPE' in the database. Why, I don't know? Any help regarding this matter will be highly appreciated.

Please see the code below:

DROP Database EliteShop
GO

CREATE Database EliteShop
GO

USE EliteShop
GO

CREATE TABLE DEVICE_TYPE
(
    Device_TypeID INT IDENTITY(1,1),
    Device_TypeID_Name VARCHAR(50),
    CONSTRAINT pk_devicetype PRIMARY KEY(Device_TypeID)
)

CREATE TABLE MANUFACTURER
(
    Manufacturer_Code VARCHAR(50),
    Manufacturer_Description VARCHAR(100),
    CONSTRAINT pk_manufacturercode PRIMARY KEY(Manufacturer_Code)
)

CREATE TABLE [PLATFORM]
(
    PlatformID INT IDENTITY(1,1),
    Platform_Description VARCHAR(50),
    CONSTRAINT pk_platID PRIMARY KEY(PlatformID)
)

CREATE TABLE DEVICE
(
    DeviceID INT IDENTITY(1,1),
    Device_TypeID INT,
    PlatformID INT,
    Manufacturer_Code VARCHAR(50),
    Model VARCHAR(50),
    InternalMemory VARCHAR(10),
    Price MONEY,
    CONSTRAINT pk_deviceID PRIMARY KEY (DeviceID),
    CONSTRAINT fk_deviceType FOREIGN KEY (Device_TypeID) REFERENCES DEVICE_TYPE(Device_TypeID),
    CONSTRAINT fk_manuFCode FOREIGN KEY (Manufacturer_Code) REFERENCES MANUFACTURER(Manufacturer_Code),
    CONSTRAINT fk_myPlatID FOREIGN KEY (PlatformID) REFERENCES [PLATFORM](PlatformID)
)
  • 4 Contributors
  • 4 Replies
  • 304 Views
  • 3 Weeks Discussion Span
  • Latest Post Latest Post by erikko

Dani AI

Generated

Short, practical explanation and fixes

That error means SQL Server already has an object called DEVICE_TYPE in the current database (not necessarily a table — it could be a table, type, synonym, partition function, etc.). The common cause in scripts like yours is running the same CREATE statements more than once, or a partial run that created some objects before failing. Deleting the database and re-running (what did) works because it removed the pre-existing objects, which proves the root cause was a name collision. (learn.microsoft.com)

How to quickly find what exists before you create

  • Look for any schema-scoped object with that name (tables, views, functions, types, synonyms):
SELECT s.name AS schema_name, o.type_desc, o.name
FROM sys.all_objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.name = 'DEVICE_TYPE';
  • Check specific catalogs too (user types, synonyms, tables) and use OBJECT_ID to test existence. These system catalog functions/views are the reliable way to detect an existing object before you try to create one. (wario.hezongjian.com)

Make your DDL idempotent (safe to run multiple times)

  • Drop the whole DB only if it exists and force exclusive access when required:
IF DB_ID(N'EliteShop') IS NOT NULL
BEGIN
  ALTER DATABASE [EliteShop] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE [EliteShop];
END
GO
  • For tables (SQL Server 2014 and earlier):
IF OBJECT_ID(N'dbo.DEVICE_TYPE','U') IS NOT NULL
  DROP TABLE dbo.DEVICE_TYPE;
GO
  • On SQL Server 2016+ you can use DROP TABLE IF EXISTS. Use these patterns in setup scripts so reruns don’t fail. (sqlshack.com)

Short best-practice checklist

  • Always schema-qualify object names (e.g., dbo.DEVICE_TYPE) to avoid default-schema surprises. (learn.microsoft.com)
  • Give constraints descriptive, table-scoped names (include table name) so you don’t end up with name collisions. (stackoverflow.com)
  • Remember GO is a client batch separator — place it where batches must be committed or to avoid parser issues with temp objects. (documentation.help)

Notes tied to the thread: ’ suggestion to rename the table will avoid collisions but isn’t necessary if you make the script idempotent; could run it cleanly because his database state was different. Use the diagnostics above to find the exact existing object before dropping or renaming anything.

Recommended Answers

All 4 Replies

Member Avatar for hericles

DeviceType isn't showing the list of reserved words for SQL Server but it does seem to be a named enumeration in SQL Server 2012/14. You might want to use a different name for that table in case it is conflicting with SQL Server internal structure.

Member Avatar for djjeavons

Hi

I tried your script, first time I had to omit the DROP EliteShop statement as that did not exist (of course) and it ran fine. I then put the DROP EliteShop back in and ran the statement and again, it ran fine. I am running SQL Server 2014.

I know my answer is not much help, I'm not quite sure why it would not be working as I don't see anything wrong with the script.

Member Avatar for Joemeister

Hey guys,

well I managed to find a solution. I just deleted the database after I created it the first time. (Sounds weird and redundent) but when I ran the script again afterwards and it worked... Was maybe just a weird error or something.

But I assume when you run the script once you shouldnt run it again then it will give you the error that the tables already exists... So I understand what I did wrong.

Thanks for your help! :)

Joe

Member Avatar for erikko

Joe, I think your code is great and no errors.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.