Add NOT NULL constraints

1 files changed, 28 insertions, 30 deletions

diff --git a/assignments/final/run.sql b/assignments/final/run.sql
index b8e2db2..8c32b8d 100644
--- a/assignments/final/run.sql
+++ b/assignments/final/run.sql
@@ -1,4 +1,4 @@
-\echo '--- Question 4 ---'
+\echo '--- Module 2 ---'
 \set ON_ERROR_STOP on
 
 DROP TABLE IF EXISTS beds;
@@ -28,14 +28,13 @@ CREATE TABLE people(
 
 CREATE TABLE employees(
   id bigint primary key,
-  person_id bigint references people(id),
-  "type" varchar(255),
-  hired_at timestamp
+  person_id bigint references people(id) NOT NULL UNIQUE,
+  hired_at timestamp NOT NULL
 );
 
 CREATE TABLE nurses(
   id bigint primary key,
-  employee_id bigint references employees(id),
+  employee_id bigint references employees(id) NOT NULL UNIQUE,
   certificate text
 );
 
@@ -43,55 +42,54 @@ CREATE TABLE care_centres(
   id bigint primary key,
   name varchar(255),
   location text,
-  nurse_in_charge_id bigint references nurses(id)
+  nurse_in_charge_id bigint references nurses(id) NOT NULL
 );
 
 CREATE TABLE timesheets(
   id bigint primary key,
-  employee_id bigint references employees(id),
-  care_centre_id bigint references care_centres(id),
+  employee_id bigint references employees(id) NOT NULL,
+  care_centre_id bigint references care_centres(id) NOT NULL,
   hours integer,
   week integer
 );
 
 CREATE TABLE physicians(
   id bigint primary key,
-  employee_id bigint references employees(id),
+  employee_id bigint references employees(id) NOT NULL UNIQUE,
   specialty text,
   pager_number varchar(255)
 );
 
 CREATE TABLE patients(
   id bigint primary key,
-  person_id bigint references people(id),
+  person_id bigint references people(id) NOT NULL UNIQUE,
   physician_id bigint references physicians(id),
-  referring_physician_id bigint references physicians(id),
+  referring_physician_id bigint references physicians(id) NOT NULL,
   contacted_at timestamp
 );
 
+CREATE TABLE items(
+  id bigint primary key,
+  number integer,
+  description text,
+  unit_cost decimal
+);
 
 CREATE TABLE consumptions(
   id bigint primary key,
-  physican_id bigint references physicians(id),
-  patient_id bigint references patients(id),
-  item_id bigint,
+  physican_id bigint references physicians(id) NOT NULL,
+  patient_id bigint references patients(id) NOT NULL,
+  item_id bigint references items(id) NOT NULL,
   consumed_at timestamp,
   quantity integer,
   total_cost decimal
 );
 
-CREATE TABLE items(
-  id bigint primary key,
-  number integer,
-  description text,
-  unit_cost decimal
-);
-
 CREATE TABLE beds(
   id bigint primary key,
   care_centre_id bigint references care_centres(id),
-  bed_number integer,
-  room_number integer
+  bed_number integer NOT NULL,
+  room_number integer NOT NULL
 );
 
 CREATE TABLE laboratories(
@@ -101,20 +99,20 @@ CREATE TABLE laboratories(
 
 CREATE TABLE staff(
   id bigint primary key,
-  employee_id bigint references employees(id),
+  employee_id bigint references employees(id) NOT NULL UNIQUE,
   job_class varchar(255)
 );
 
 CREATE TABLE technicians(
   id bigint primary key,
-  employee_id bigint references employees(id),
+  employee_id bigint references employees(id) NOT NULL UNIQUE,
   skill varchar(255)
 );
 
 CREATE TABLE treatments(
   id bigint primary key,
-  physician_id bigint references physicians(id),
-  patient_id bigint references patients(id),
+  physician_id bigint references physicians(id) NOT NULL,
+  patient_id bigint references patients(id) NOT NULL,
   number integer,
   name varchar(255),
   occurred_at timestamp,
@@ -123,8 +121,8 @@ CREATE TABLE treatments(
 
 CREATE TABLE visits(
   id bigint primary key,
-  patient_id bigint references patients(id),
-  physician_id bigint references physicians(id),
+  patient_id bigint references patients(id) NOT NULL,
+  physician_id bigint references physicians(id) NOT NULL,
   comments text,
-  scheduled_at timestamp
+  scheduled_at timestamp NOT NULL
 );