From a4fe01d592aafa73a04284a085a4e52da66b787e Mon Sep 17 00:00:00 2001
From: mo khan <mo.khan@gmail.com>
Date: Wed, 1 Jan 2020 11:35:13 -0700
Subject: Add foreign key constrains to an insance schema

---
 labs/2/run.sql | 15 +++++++++------
 1 file changed, 9 insertions(+), 6 deletions(-)

(limited to 'labs/2/run.sql')

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);
-- 
cgit v1.2.3