-------------------------------------------------------------------------------
-- ALTER TABLE statements for specifying primary keys, foreign keys,
-- unique constraints, and not null constraints on the Company database
-------------------------------------------------------------------------------

-- Add a primary keys, must be done before we can make foreign keys
-------------------------------------------------------------------
ALTER TABLE Allocations ADD (PRIMARY KEY (ecpr, pno));
ALTER TABLE Departments ADD (PRIMARY KEY (dnumber));
ALTER TABLE Employees ADD (PRIMARY KEY (cpr));
ALTER TABLE Locations ADD (PRIMARY KEY (dnbr, dlocation));
ALTER TABLE Projects ADD (PRIMARY KEY (pnumber));

-- Add foreign keys
-------------------------------------------------------------------
ALTER TABLE Allocations ADD (FOREIGN KEY (ecpr)
			     REFERENCES employees(cpr),
                             FOREIGN KEY (pno)
			     REFERENCES projects(pnumber));

ALTER TABLE Departments ADD (FOREIGN KEY (mgrcpr) 
			REFERENCES Employees (cpr));
				
ALTER TABLE Employees ADD (FOREIGN KEY (dno) 
			REFERENCES departments (dnumber));

ALTER TABLE Locations ADD (FOREIGN KEY (dnbr)
			   REFERENCES departments (dnumber));

ALTER TABLE Projects ADD (FOREIGN KEY (dnum)
			   REFERENCES departments (dnumber));

-- Add unique constraints (candidate keys)
-------------------------------------------------------------------
ALTER TABLE Departments ADD (UNIQUE (dname));
ALTER TABLE Projects ADD (UNIQUE (pname));
 
-- Add NOT NULL constraints
-------------------------------------------------------------------
ALTER TABLE Allocations MODIFY (hours NOT NULL);

ALTER TABLE Departments MODIFY (dname  NOT NULL,
			        mgrcpr NOT NULL);

-- Possible all attributes of Employees could be NOT NULL
ALTER TABLE Employees MODIFY ( fname    NOT NULL,
			       lname    NOT NULL,
			       address  NOT NULL,
	                       salary   NOT NULL,
			       dno      NOT NULL);

ALTER TABLE Projects MODIFY (pname NOT NULL,
			     dnum  NOT NULL);
