SQL Server Create Table gives error that object '' already exists when it doesn't

Question

Sorry if this looks like a duplicate but I cannot find an answer to my situation. I am trying to create table cardissuedates in database NBFP.

Here is my create statement:

USE NBFoodPantry

CREATE TABLE cardissuedates 
(
    clientid char(36) NOT NULL,
    issuedate date NOT NULL

    CONSTRAINT cardissuedates 
       PRIMARY KEY CLUSTERED (clientid ASC)
           WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                 ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

I get this error:

Msg 2714, Level 16, State 5, Line 33
There is already an object named 'cardissuedates' in the database.

Msg 1750, Level 16, State 0, Line 33
Could not create constraint or index. See previous errors.

The table does not exist in the NBFB database and I cannot find it when I check master and NBFB.

I have tried

select * 
from sys.all_objects
where name like '%ca%'
order by 1

select * 
from sys.tables
where name like 'c%'
order by 1

select * 
from sys.all_columns
where name like 'c%'
order by 1

I even created a new database and executed this create statement, with the new database name in the USE statement. Same error.

I have stopped all services exited out of SSMS. Nothing seems to work. I do not know where to look any more.

Any help would be GREATLY appreciated.

Thanks

Answer 1

you are giving the primary key the same name as the table, this is not allowed as both are schema scoped objects and multiple objects can't have the same schema_name.object_name. You can do this instead:

CREATE TABLE cardissuedates ( 
    clientid char(36) NOT NULL, 
    issuedate date NOT NULL CONSTRAINT 
    pk_cardissuedates PRIMARY KEY CLUSTERED ( clientid ASC )
    WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON, 
        FILLFACTOR = 90
    ) ON [PRIMARY] 
) ON [PRIMARY]

I just added the prefix PK_ to the constraint