CODEWITHSUNDEEP
sqlsql-server-2008compiler-errors
DarkBlue
DarkBlue

Reputation: 19

Microsoft SQL Server

So I'm working on a SQL and I keep getting errors and I have triple checked it and still haven't come up with a reason as to why the queries aren't showing up correctly. I'm using Microsoft SQL Server. These are the errors that I'm currently getting:

Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_PatientID' references invalid table 'Patient'.

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

Msg 208, Level 16, State 1, Line 2
Invalid object name 'TreatmentDetails'.

Code:

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Hospital')
DROP DATABASE [Hospital]
GO

CREATE DATABASE [Hospital] 
GO

USE Hospital
GO

CREATE TABLE [dbo].[Physician]
(
    PhysicianID INTEGER NOT NULL,
    FirstName VARCHAR(30) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    Specialty VARCHAR(30) NOT NULL,
    GraduationDate DATE NOT NULL,
CONSTRAINT [PK_PhysicianID] PRIMARY KEY (PhysicianID) 
);
GO

CREATE TABLE [dbo].[TreatmentDetails]
(
    TreatmentID INTEGER NOT NULL,
    PhysicianID INTEGER NOT NULL,
    PatientID INTEGER NOT NULL,
    StartDateTime DATE NOT NULL,
    EndDateTime DATE NULL,
    Results VARCHAR(30),
    CONSTRAINT [PK_TreatmentID] PRIMARY KEY (TreatmentID),
    CONSTRAINT [FK_PhysicianID] FOREIGN KEY (PhysicianID) REFERENCES Physician(PhysicianID),
    CONSTRAINT [FK_PatientID] FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
);
GO

CREATE TABLE [dbo].[Patient]
(
    PatientID INTEGER NOT NULL,
    FirstName VARCHAR(30) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    DateOfBirth DATE NOT NULL,
    CONSTRAINT [PK_PatientID] PRIMARY KEY (PatientID),
);
GO

CREATE TABLE [dbo].[AdmissionDate]
(
    AdmissionID INTEGER NOT NULL,
    PhysicianID INTEGER NOT NULL,
    PatientID INTEGER NOT NULL,
    AdmissionDate DATE NOT NULL,
    DischargeDate DATE NULL,
    CONSTRAINT [PK_AdmissionID] PRIMARY KEY (AdmissionID),
    CONSTRAINT [FK_PhysicianID] FOREIGN KEY (PhysicianID) REFERENCES Physician(PhysicianID),
    CONSTRAINT [FK_PatientID] FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
);
GO

INSERT INTO TreatmentDetails VALUES (1, 12345, 1234, '2014-4-5', NULL, 'NOT DONE')
INSERT INTO TreatmentDetails VALUES (2, 12346, 1235, '2013-5-6', NULL, 'NOT DONE')
INSERT INTO TreatmentDetails VALUES (3, 12347, 1236, '2012-7-8', '2014-9-10', 'Patient finished')
INSERT INTO TreatmentDetails VALUES (4, 12348, 1237, '2011-9-10', '2013-11-12', 'Patient finished')
INSERT INTO TreatmentDetails VALUES (5, 12349, 1238, '2010-11-12', NULL, 'NOT DONE')

INSERT INTO Physician VALUES (12345, 'Will', 'Smith', 'Surgeon', '2014-5-9');
INSERT INTO Physician VALUES (12346, 'Jim', 'Carey', 'Pediatrictian', '2013-2-4');
INSERT INTO Physician VALUES (12347, 'Adam', 'Sandler', 'Immunologist', '2012-6-12');
INSERT INTO Physician VALUES (12348, 'Seth', 'Rogan', 'Neurologist', '2010-9-19');
INSERT INTO Physician VALUES (12349, 'James', 'Bond', 'Dermatologist', '2011-5-2');

INSERT INTO Patient VALUES (1234, 'Christopher', 'Thompson', '1989-7-9');
INSERT INTO Patient VALUES (1235, 'Mac', 'Miller', '1970-9-5');
INSERT INTO Patient VALUES (1236, 'Abraham', 'Lincoln', '1988-1-22');
INSERT INTO Patient VALUES (1237, 'George', 'Washington', '1965-2-8');
INSERT INTO Patient VALUES (1238, 'Franklin', 'Roosevelt', '1992-5-19');

INSERT INTO AdmissionDate VALUES (001, 12345, 1234,'2014-2-9', NULL);
INSERT INTO AdmissionDate VALUES (002, 12346, 1235, '2014-12-8', '2014-15-9');
INSERT INTO AdmissionDate VALUES (003, 12347, 1236,'2014-3-7', '2014-4-9');
INSERT INTO AdmissionDate VALUES (004, 12348, 1237, '2014-8-6', NULL);
INSERT INTO AdmissionDate VALUES (005, 12349, 1238, '2014-5-5', NULL);



GO

USE Hospital

SELECT * FROM Physician

SELECT * FROM Patient

SELECT * FROM AdmissionDate

SELECT * FROM TreatmentDetails

SELECT Patient.PatientID, Patient.FirstName, Patient.LastName, Patient.DateOfBirth, AdmissionDate.AdmissionDate, AdmissionDate.DischargeDate, Physician.Specialty
FROM Patient, AdmissionDate, Physician
WHERE AdmissionDate IS NOT NULL
ORDER BY Patient.LastName, Patient.FirstName, AdmissionDate.AdmissionDate

SELECT Physician.PhysicianID, Physician.FirstName, Physician.LastName, Patient.PatientID, Patient.FirstName, Patient.LastName, TreatmentDetails.TreatmentID, TreatmentDetails.StartDateTime, TreatmentDetails.EndDateTime, TreatmentDetails.Results
FROM Physician, Patient, TreatmentDetails
WHERE TreatmentDetails.EndDateTime IS NULL
ORDER BY TreatmentDetails.StartDateTime, Physician.LastName, Patient.LastName

SELECT Physician.PhysicianID, Physician.FirstName, Physician.LastName, Patient.PatientID, Patient.FirstName, Patient.LastName, TreatmentDetails.TreatmentID, TreatmentDetails.StartDateTime, TreatmentDetails.EndDateTime, TreatmentDetails.Results
FROM Physician, Patient, TreatmentDetails
WHERE TreatmentDetails.EndDateTime IS NOT NULL
ORDER BY TreatmentDetails.StartDateTime, Physician.LastName, Patient.LastName

Upvotes: 0

Views: 85

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

  1. You have incorrect order of creating tables.
  2. You are creating foreign keys with same name in different tables.
  3. You are inserting data in tables in incorrect order.
  4. You have provided incorrect date format. Default format is YYYY-MM-DD and you provide INSERT INTO AdmissionDate VALUES (002, 12346, 1235, '2014-12-8', '2014-15-9'); 2014-15-9 this is to change.

Here is working script:

USE master

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Hospital')
DROP DATABASE [Hospital]
GO

CREATE DATABASE [Hospital] 
GO

USE Hospital
GO

CREATE TABLE [dbo].[Physician]
(
    PhysicianID INTEGER NOT NULL,
    FirstName VARCHAR(30) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    Specialty VARCHAR(30) NOT NULL,
    GraduationDate DATE NOT NULL,
CONSTRAINT [PK_PhysicianID] PRIMARY KEY (PhysicianID) 
);
GO

CREATE TABLE [dbo].[Patient]
(
    PatientID INTEGER NOT NULL,
    FirstName VARCHAR(30) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    DateOfBirth DATE NOT NULL,
    CONSTRAINT [PK_PatientID] PRIMARY KEY (PatientID),
);
GO

CREATE TABLE [dbo].[TreatmentDetails]
(
    TreatmentID INTEGER NOT NULL,
    PhysicianID INTEGER NOT NULL,
    PatientID INTEGER NOT NULL,
    StartDateTime DATE NOT NULL,
    EndDateTime DATE NULL,
    Results VARCHAR(30),
    CONSTRAINT [PK_TreatmentDetails_TreatmentID] PRIMARY KEY (TreatmentID),
    CONSTRAINT [FK_TreatmentDetails_PhysicianID] FOREIGN KEY (PhysicianID) REFERENCES Physician(PhysicianID),
    CONSTRAINT [FK_TreatmentDetails_PatientID] FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
);
GO

CREATE TABLE [dbo].[AdmissionDate]
(
    AdmissionID INTEGER NOT NULL,
    PhysicianID INTEGER NOT NULL,
    PatientID INTEGER NOT NULL,
    AdmissionDate DATE NOT NULL,
    DischargeDate DATE NULL,
    CONSTRAINT [PK_AdmissionDate_AdmissionID] PRIMARY KEY (AdmissionID),
    CONSTRAINT [FK_AdmissionDate_PhysicianID] FOREIGN KEY (PhysicianID) REFERENCES Physician(PhysicianID),
    CONSTRAINT [FK_AdmissionDate_PatientID] FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
);
GO

INSERT INTO Physician VALUES (12345, 'Will', 'Smith', 'Surgeon', '2014-5-9');
INSERT INTO Physician VALUES (12346, 'Jim', 'Carey', 'Pediatrictian', '2013-2-4');
INSERT INTO Physician VALUES (12347, 'Adam', 'Sandler', 'Immunologist', '2012-6-12');
INSERT INTO Physician VALUES (12348, 'Seth', 'Rogan', 'Neurologist', '2010-9-19');
INSERT INTO Physician VALUES (12349, 'James', 'Bond', 'Dermatologist', '2011-5-2');

INSERT INTO Patient VALUES (1234, 'Christopher', 'Thompson', '1989-7-9');
INSERT INTO Patient VALUES (1235, 'Mac', 'Miller', '1970-9-5');
INSERT INTO Patient VALUES (1236, 'Abraham', 'Lincoln', '1988-1-22');
INSERT INTO Patient VALUES (1237, 'George', 'Washington', '1965-2-8');
INSERT INTO Patient VALUES (1238, 'Franklin', 'Roosevelt', '1992-5-19');

INSERT INTO TreatmentDetails VALUES (1, 12345, 1234, '2014-4-5', NULL, 'NOT DONE')
INSERT INTO TreatmentDetails VALUES (2, 12346, 1235, '2013-5-6', NULL, 'NOT DONE')
INSERT INTO TreatmentDetails VALUES (3, 12347, 1236, '2012-7-8', '2014-9-10', 'Patient finished')
INSERT INTO TreatmentDetails VALUES (4, 12348, 1237, '2011-9-10', '2013-11-12', 'Patient finished')
INSERT INTO TreatmentDetails VALUES (5, 12349, 1238, '2010-11-12', NULL, 'NOT DONE')

INSERT INTO AdmissionDate VALUES (001, 12345, 1234,'2014-2-9', NULL);
INSERT INTO AdmissionDate VALUES (002, 12346, 1235, '2014-12-8', '2014-9-15');
INSERT INTO AdmissionDate VALUES (003, 12347, 1236,'2014-3-7', '2014-4-9');
INSERT INTO AdmissionDate VALUES (004, 12348, 1237, '2014-8-6', NULL);
INSERT INTO AdmissionDate VALUES (005, 12349, 1238, '2014-5-5', NULL);



GO

USE Hospital

SELECT * FROM Physician

SELECT * FROM Patient

SELECT * FROM AdmissionDate

SELECT * FROM TreatmentDetails

SELECT Patient.PatientID, Patient.FirstName, Patient.LastName, Patient.DateOfBirth, AdmissionDate.AdmissionDate, AdmissionDate.DischargeDate, Physician.Specialty
FROM Patient, AdmissionDate, Physician
WHERE AdmissionDate IS NOT NULL
ORDER BY Patient.LastName, Patient.FirstName, AdmissionDate.AdmissionDate

SELECT Physician.PhysicianID, Physician.FirstName, Physician.LastName, Patient.PatientID, Patient.FirstName, Patient.LastName, TreatmentDetails.TreatmentID, TreatmentDetails.StartDateTime, TreatmentDetails.EndDateTime, TreatmentDetails.Results
FROM Physician, Patient, TreatmentDetails
WHERE TreatmentDetails.EndDateTime IS NULL
ORDER BY TreatmentDetails.StartDateTime, Physician.LastName, Patient.LastName

SELECT Physician.PhysicianID, Physician.FirstName, Physician.LastName, Patient.PatientID, Patient.FirstName, Patient.LastName, TreatmentDetails.TreatmentID, TreatmentDetails.StartDateTime, TreatmentDetails.EndDateTime, TreatmentDetails.Results
FROM Physician, Patient, TreatmentDetails
WHERE TreatmentDetails.EndDateTime IS NOT NULL
ORDER BY TreatmentDetails.StartDateTime, Physician.LastName, Patient.LastName

Upvotes: 1

Related Questions