Add foreign key constrains to an insance schema

1 files changed, 9 insertions, 6 deletions

diff --git a/labs/2/run.sql b/labs/2/run.sql
index 8027009..1a501bc 100644
--- a/labs/2/run.sql
+++ b/labs/2/run.sql
@@ -1,4 +1,4 @@
-DROP TABLE IF EXISTS employees;
+DROP TABLE IF EXISTS employees CASCADE;
 CREATE TABLE employees (
   fname varchar(255),
   minit varchar(1),
@@ -11,23 +11,24 @@ CREATE TABLE employees (
   dno integer
 );
 
-DROP TABLE IF EXISTS departments;
+
+DROP TABLE IF EXISTS departments CASCADE;
 CREATE TABLE departments (
   dname varchar(255),
   dnumber integer primary key,
-  mgrsin integer,
+  mgrsin varchar(9),
   mgrstartdate date
 );
 
-DROP TABLE IF EXISTS projects;
+DROP TABLE IF EXISTS projects CASCADE;
 CREATE TABLE projects (
   pname varchar(255),
-  pnumber integer,
+  pnumber integer primary key,
   plocation varchar(255),
   dnum integer
 );
 
-DROP TABLE IF EXISTS locations;
+DROP TABLE IF EXISTS locations CASCADE;
 CREATE TABLE locations (
   dnbr integer,
   dlocation varchar(255)
@@ -56,4 +57,6 @@ INSERT INTO locations VALUES (11, 'Athabasca');
 INSERT INTO locations VALUES (12, 'Ottawa');
 INSERT INTO locations VALUES (12, 'Montreal');
 
+ALTER TABLE departments ADD CONSTRAINT employees_fk FOREIGN KEY (mgrsin) REFERENCES employees (sin);
 ALTER TABLE employees ADD CONSTRAINT departments_fk FOREIGN KEY (dno) REFERENCES departments (dnumber);
+ALTER TABLE projects ADD CONSTRAINT departments_fk FOREIGN KEY (dnum) REFERENCES departments (dnumber);