Home > Software design >  SQL Error: There is already an object named 'TPatients' in the database
SQL Error: There is already an object named 'TPatients' in the database

Time:02-27

I cannot seem to figure out why I am getting the following error:

Msg 2714, Level 16, State 5, Line 56
There is already an object named 'TPatients' in the database.
Msg 1750, Level 16, State 1, Line 56
Could not create constraint or index. See previous errors.

Here is my Drop Table and Create Table SQL statements below. Whenever I execute, it says TPatients already exists, however I am not seeing it in the object explorer. I tried moving around the drop table statements, but could not get it to work.

-- --------------------------------------------------------------------------------
--  Step #1 : Drop tables
-- --------------------------------------------------------------------------------
IF OBJECT_ID ('TAppointments')      IS NOT NULL DROP TABLE TAppointments
IF OBJECT_ID ('TPatients')          IS NOT NULL DROP TABLE TPatients
IF OBJECT_ID ('TDoctors')           IS NOT NULL DROP TABLE TDoctors
IF OBJECT_ID ('TNurses')            IS NOT NULL DROP TABLE TNurses

-- --------------------------------------------------------------------------------
--  Step #1 : Create table 
-- --------------------------------------------------------------------------------
CREATE TABLE TDoctors
(
    intDoctorID         INTEGER         NOT NULL
   ,strFirstName        VARCHAR(25)     NOT NULL
   ,strLastName         VARCHAR(25)     NOT NULL
   ,strEmail            VARCHAR(25)     NOT NULL
   ,strPhone            VARCHAR(25)     NOT NULL
   ,strAddress          VARCHAR(25)     NOT NULL
   ,dtmHireDate         DATETIME        NOT NULL
   ,dtmTerminationDate  DATETIME        NULL
   ,dtmPassedBoardDate  DATETIME        NOT NULL
   CONSTRAINT TDoctors_PK PRIMARY KEY ( intDoctorID )
)

CREATE TABLE TNurses
(
    intNurseID          INTEGER         NOT NULL
   ,strFirstName        VARCHAR(25)     NOT NULL
   ,strLastName         VARCHAR(25)     NOT NULL
   ,strEmail            VARCHAR(25)     NOT NULL
   ,strPhone            VARCHAR(25)     NOT NULL
   ,strAddress          VARCHAR(25)     NOT NULL
   ,dtmHireDate         DATETIME        NOT NULL
   ,dtmTerminationDate  DATETIME        NULL
   ,dtmPassedBoardDate  DATETIME        NOT NULL
   CONSTRAINT TNurses_PK PRIMARY KEY ( intNurseID )
)

CREATE TABLE TPatients
(
    intPatientID        INTEGER         NOT NULL
   ,strFirstName        VARCHAR(25)     NOT NULL
   ,strLastName         VARCHAR(25)     NOT NULL
   ,dtmDateOfBirth      DATETIME        NOT NULL
   ,strEmail            VARCHAR(25)     NOT NULL
   ,strPhone            VARCHAR(25)     NOT NULL
   ,strAddress          VARCHAR(25)     NOT NULL
   ,strZipCode          VARCHAR(25)     NOT NULL
   ,strSSN              VARCHAR(25)     NOT NULL
   ,strInsuranceCompany VARCHAR(25)     NOT NULL
   ,strEmergFirstName   VARCHAR(25)     NOT NULL
   ,strEmergLastName    VARCHAR(25)     NOT NULL
   ,strEmergPhone       VARCHAR(25)     NOT NULL
   CONSTRAINT TPatients PRIMARY KEY ( intPatientID )
)

CREATE TABLE TAppointments
(
    intAppointmentID    INTEGER         NOT NULL
   ,dtmAppointmentDate  DATETIME        NOT NULL
   ,strReason           VARCHAR(50)     NOT NULL
   ,strDiagnosis        VARCHAR(25)     NOT NULL
   ,dtmFollowUpDate     DATETIME        NOT NULL
   ,intPatientID        INTEGER         NOT NULL
   ,intNurseID          INTEGER         NOT NULL
   ,intDoctorID         INTEGER         NOT NULL
   CONSTRAINT TAppointments PRIMARY KEY ( intAppointmentID )
)

CodePudding user response:

The issue is because you are giving your constraints the same name as the table.

Give them a unique name, eg prefix with "PK_Tpatients" if it's a primary key.

Note that tables and constraints are all "objects" and as such need unique names.

  • Related