Add foreign key constraints

1 files changed, 58 insertions, 56 deletions

diff --git a/assignments/final/run.sql b/assignments/final/run.sql
index e6817c6..b8e2db2 100644
--- a/assignments/final/run.sql
+++ b/assignments/final/run.sql
@@ -2,117 +2,119 @@
 \set ON_ERROR_STOP on
 
 DROP TABLE IF EXISTS beds;
-DROP TABLE IF EXISTS care_centres;
 DROP TABLE IF EXISTS consumptions;
-DROP TABLE IF EXISTS employees;
 DROP TABLE IF EXISTS items;
 DROP TABLE IF EXISTS laboratories;
-DROP TABLE IF EXISTS nurses;
-DROP TABLE IF EXISTS patients;
-DROP TABLE IF EXISTS people;
-DROP TABLE IF EXISTS physicians;
 DROP TABLE IF EXISTS staff;
 DROP TABLE IF EXISTS technicians;
 DROP TABLE IF EXISTS timesheets;
 DROP TABLE IF EXISTS treatments;
 DROP TABLE IF EXISTS visits;
 
-CREATE TABLE timesheets(
-  id bigint primary key,
-  employee_id bigint,
-  care_centre_id bigint,
-  hours integer,
-  week integer
-);
+DROP TABLE IF EXISTS patients;
+DROP TABLE IF EXISTS physicians;
+DROP TABLE IF EXISTS care_centres;
+DROP TABLE IF EXISTS nurses;
+DROP TABLE IF EXISTS employees;
+DROP TABLE IF EXISTS people;
 
-CREATE TABLE consumptions(
+CREATE TABLE people(
   id bigint primary key,
-  physican_id bigint,
-  patient_id bigint,
-  item_id bigint,
-  consumed_at timestamp,
-  quantity integer,
-  total_cost decimal
+  name varchar(255),
+  address varchar(255),
+  birth_date timestamp,
+  phone_number varchar(255)
 );
 
 CREATE TABLE employees(
   id bigint primary key,
-  person_id bigint,
+  person_id bigint references people(id),
   "type" varchar(255),
   hired_at timestamp
 );
 
-CREATE TABLE items(
+CREATE TABLE nurses(
   id bigint primary key,
-  number integer,
-  description text,
-  unit_cost decimal
+  employee_id bigint references employees(id),
+  certificate text
 );
 
-CREATE TABLE beds(
+CREATE TABLE care_centres(
   id bigint primary key,
-  care_centre_id bigint,
-  bed_number integer,
-  room_number integer
+  name varchar(255),
+  location text,
+  nurse_in_charge_id bigint references nurses(id)
 );
 
-CREATE TABLE laboratories(
+CREATE TABLE timesheets(
   id bigint primary key,
-  location text
+  employee_id bigint references employees(id),
+  care_centre_id bigint references care_centres(id),
+  hours integer,
+  week integer
 );
 
-CREATE TABLE nurses(
+CREATE TABLE physicians(
   id bigint primary key,
-  employee_id bigint,
-  certificate text
+  employee_id bigint references employees(id),
+  specialty text,
+  pager_number varchar(255)
 );
 
 CREATE TABLE patients(
   id bigint primary key,
-  person_id bigint,
-  physician_id bigint,
-  referring_physician_id bigint,
+  person_id bigint references people(id),
+  physician_id bigint references physicians(id),
+  referring_physician_id bigint references physicians(id),
   contacted_at timestamp
 );
 
-CREATE TABLE people(
+
+CREATE TABLE consumptions(
   id bigint primary key,
-  name varchar(255),
-  address varchar(255),
-  birth_date timestamp,
-  phone_number varchar(255)
+  physican_id bigint references physicians(id),
+  patient_id bigint references patients(id),
+  item_id bigint,
+  consumed_at timestamp,
+  quantity integer,
+  total_cost decimal
 );
 
-CREATE TABLE care_centres(
+CREATE TABLE items(
   id bigint primary key,
-  name varchar(255),
-  location text,
-  nurse_in_charge_id bigint
+  number integer,
+  description text,
+  unit_cost decimal
 );
 
-CREATE TABLE physicians(
+CREATE TABLE beds(
   id bigint primary key,
-  employee_id bigint,
-  specialty text,
-  pager_number varchar(255)
+  care_centre_id bigint references care_centres(id),
+  bed_number integer,
+  room_number integer
+);
+
+CREATE TABLE laboratories(
+  id bigint primary key,
+  location text
 );
 
 CREATE TABLE staff(
   id bigint primary key,
-  employee_id bigint,
+  employee_id bigint references employees(id),
   job_class varchar(255)
 );
 
 CREATE TABLE technicians(
   id bigint primary key,
-  employee_id bigint,
+  employee_id bigint references employees(id),
   skill varchar(255)
 );
 
 CREATE TABLE treatments(
   id bigint primary key,
-  physician_id bigint,
-  patient_id bigint,
+  physician_id bigint references physicians(id),
+  patient_id bigint references patients(id),
   number integer,
   name varchar(255),
   occurred_at timestamp,
@@ -121,8 +123,8 @@ CREATE TABLE treatments(
 
 CREATE TABLE visits(
   id bigint primary key,
-  patient_id bigint,
-  physician_id bigint,
+  patient_id bigint references patients(id),
+  physician_id bigint references physicians(id),
   comments text,
   scheduled_at timestamp
 );