New learner with SQL, using SQL Server Mang. Studio. I'm getting an error, Msg 2714, Level 16, State 6, Line 3 There's already an object named

Question

I have created a new DB for an assignment. I am a new learner. Everything seems to be fine except for the one error. I don't know if I can delete the table with the error without messing up the rest of the DB or if I can do a 'DROP' for the table. I found the 'drop' researching the web, but am leery about messing with the DB. Can someone show me how I can fix this please?

I haven't tried anything yet, as I don't want to mess up the DB that is hopefully working.

CREATE TABLE Customer
(CustID         int  NOT NULL IDENTITY PRIMARY KEY,
 MediaStatusID  int  NOT NULL   REFERENCES MediaStatus(MediaStatusID),
 FName              varchar(50)     NOT NULL,
 LName              varchar(50)     NOT NULL,
 Address            varchar(50)     NOT NULL,
 Address2           varchar(50)     NOT NULL,
 Email              varchar(254)            NULL,
 Phone              varchar(10)     NOT NULL,
 City               varchar(50)     NOT NULL,
 State              varchar(2)      NOT NULL,
 ZipCode            varchar(5)      NOT NULL,
 Balance            int             NOT NULL,
 LastUpdate         timestamp       NOT NULL
 );

I am just expecting the whole DB to work to that I can write queries for the rest of the assignment. Here is the error message:

Msg 2714, Level 16, State 6, Line 3 There is already an object named 'Customer' in the database.

Answer 1

there is already table with name Customer in your database try to refresh the tables and check it you can use Customer2 as initial solution.

Answer 2

The error message explains exactly what's wrong. You're trying to CREATE an object called Customer that already exists.

What you can do is write your CREATE DDL (data definition language) in a way that it first checks for the existence for an object before trying to create it like so...

IF OBJECT_ID('dbo.Customer','U') IS NULL --only create if object doesn't exist
BEGIN
    CREATE TABLE dbo.Customer
        (CustID         int  NOT NULL IDENTITY PRIMARY KEY,
         MediaStatusID  int  NOT NULL   REFERENCES MediaStatus(MediaStatusID),
         FName              varchar(50)     NOT NULL,
         LName              varchar(50)     NOT NULL,
         Address            varchar(50)     NOT NULL,
         Address2           varchar(50)     NOT NULL,
         Email              varchar(254)            NULL,
         Phone              varchar(10)     NOT NULL,
         City               varchar(50)     NOT NULL,
         State              varchar(2)      NOT NULL,
         ZipCode            varchar(5)      NOT NULL,
         Balance            int             NOT NULL,
         LastUpdate         timestamp       NOT NULL
     );
END ;
GO

Answer 3

Delete and Drop are two different commands in SQL. While Delete is a DML(Data Manipulation Language) command that removes one or more rows from a table or view in SQL Server Drop is a DDL (Data Definition Language) command and removes one or more table definitions and all related data.

As the error message suggests there is already a table with name Customer that exists in the database. To recreate the table with new definition you can write a query as:

IF OBJECT_ID('dbo. Customer', 'U') IS NOT NULL
  DROP TABLE dbo. Customer;

--And then follows the new table definition..
Create..

Answer 4

The error messgage tells, that there is already a table named "Customer" in your database. You can look into tht tables data writing select * from Custumer. If the table is empty you can delete the table with drop table Customer. After the table is gone, you will be able to create your new table.

If you don't want to do all that (eg. if your table has data in it), you can just use another name than "Customer" for your new table.