CREATE TABLE IF NOT EXISTS public."Visit"
(
"PatientID" character varying NOT NULL,
"VisitNumber" character varying NOT NULL,
"LabTestID" character varying NOT NULL,
"LabTestName" character varying NOT NULL,
"VisitDate" date NOT NULL,
"NumericalValue" double precision,
"CategoricalValue" character varying,
"Flags" character varying,
CONSTRAINT "Visit_pkey" PRIMARY KEY ("PatientID", "VisitNumber", "LabTestID", "VisitDate")
);
------------------------------------------------------------
------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public."Diagnosis"
(
"PatientID" character varying NOT NULL,
"VisitNumber" character varying NOT NULL,
"VisitDate" date NOT NULL,
"DiseaseID" character varying,
"DiseaseName" character varying NOT NULL,
CONSTRAINT "Diagnosis_pkey" PRIMARY KEY ("PatientID", "VisitNumber", "VisitDate")
);
------------------------------------------------------------
------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public."LabTest"
(
"LabTestID" character varying NOT NULL,
"LabTestName" character varying NOT NULL,
"Unit" character varying,
"NormMin" double precision,
"NormMax" double precision,
CONSTRAINT "LabTest_pkey" PRIMARY KEY ("LabTestID"),
CONSTRAINT "LabTest_ukey" UNIQUE ("LabTestName")
);
------------------------------------------------------------
------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public."Disease"
(
"DiseaseID" character varying NOT NULL,
"DiseaseName" character varying NOT NULL,
CONSTRAINT "Disease_pkey" PRIMARY KEY ("DiseaseID"),
CONSTRAINT "Disease_ukey" UNIQUE ("DiseaseName")
);
------------------------------------------------------------
------------------------------------------------------------
ALTER TABLE public."Visit" ADD CONSTRAINT "Visit_fkey_PatientID" FOREIGN KEY ("PatientID", "VisitNumber", "VisitDate")
REFERENCES public."Diagnosis" ("PatientID", "VisitNumber", "VisitDate");
ALTER TABLE public."Visit" ADD CONSTRAINT "Visit_fkey" FOREIGN KEY ("LabTestID")
REFERENCES public."LabTest" ("LabTestID");
ALTER TABLE public."Diagnosis" ADD CONSTRAINT "Diagnosis_fkey" FOREIGN KEY ("DiseaseID")
REFERENCES public."Disease" ("DiseaseID");
postgresql数据库 多张表之间的多对多关系
最新推荐文章于 2022-08-06 12:29:43 发布