sql - Microsoft SQL服务器

因此，我正在使用SQL，并且不断收到错误，我对它进行了三重检查，但仍然没有提出查询未正确显示的原因。我正在使用Microsoft SQL Server。这些是我目前遇到的错误:

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'.



码:
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

最佳答案

  • 您创建表的顺序不正确。
  • 您正在不同的表中创建具有相同名称的外键。
  • 您以错误的顺序将数据插入表中。
  • 您提供了错误的日期格式。默认格式为YYYY-MM-DD并提供INSERT INTO AdmissionDate VALUES (002, 12346, 1235, '2014-12-8', '2014-15-9'); 2014-15-9即可更改。

    • 这是工作脚本:
    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

    https://stackoverflow.com/questions/28651376/

    相关文章：

    c# - 为什么这种编译方式有所不同？

    compiler-errors - gfortran在编译文件上不输出ident工具

    eclipse - 如何修复xtext上的简单错误？

    compiler-errors - 在Solaris Sparc 11下强制使用Sun Studio

    asp.net - 错误: SignalRRouteExtension.Mapconnection

    haskell - 实例化类时发生编译错误

    c# - 根据文本框错误重新定义变量

    c# - MVC3网站编译器错误

    c# - Unity 在编译有效的 C# 类时遇到问题

    compiler-errors - 由于 glib 错误，无法处理 gobject-introspe