0

I am trying to alter tables to add the necessary primary and foreign keys, but I am really struggling to run the code without getting any errors.

The script I have written for adding PKs and FKs is the following:

ALTER TABLE PROJDEPT DROP CONSTRAINT PROJDEPT_pk;
ALTER TABLE EMPLOYEE DROP CONSTRAINT EMPLOYEE_pk DROP CONSTRAINT fk_EMPLOYEE;
ALTER TABLE PROJECT DROP CONSTRAINT PROJECT_pk DROP CONSTRAINT fk_PROJECT;
ALTER TABLE PROJECTTASK DROP CONSTRAINT PROJECTTASK_pk DROP CONSTRAINT fk_PROJECTTASK DROP CONSTRAINT fk_PROJECTTASK2;

ALTER TABLE PROJDEPT ADD CONSTRAINT PROJDEPT_pk PRIMARY KEY (Proj_Dept_ID);
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_pk PRIMARY KEY (Employee_ID);
ALTER TABLE PROJECT ADD CONSTRAINT PROJECT_pk PRIMARY KEY (Project_ID);
ALTER TABLE PROJECTTASK ADD CONSTRAINT PROJECTTASK_pk PRIMARY KEY (Project_ID, Employee_ID);

ALTER TABLE EMPLOYEE 
  ADD CONSTRAINT fk_EMPLOYEE 
  FOREIGN KEY (Proj_Dept_ID)
  REFERENCES PROJDEPT(Proj_Dept_ID);

ALTER TABLE PROJECT
  ADD CONSTRAINT fk_PROJECT 
  FOREIGN KEY (Proj_Dept_ID)
  REFERENCES PROJDEPT(Proj_Dept_ID);

ALTER TABLE PROJJECTTASK 
  ADD CONSTRAINT fk_PROJECTTASK FOREIGN KEY (Project_ID) REFERENCES PROJECT(Project_ID),
  ADD CONSTRAINT fk_PROJECTTASK2 FOREIGN KEY (Employee_ID) REFERENCES EMPLOYEE(Employee_ID);

The ER Diagram I am trying to put into Oracle is down below. Any help would be greatly appreciated!

enter image description here

Shadow
  • 33,525
  • 10
  • 51
  • 64
luhook04
  • 21
  • 2
  • **a)** You didn't post CREATE TABLE statements. Therefore, what are we supposed to do with bunch of ALTER TABLE ones? **b)** You mentioned "errors". Which "errors"? They aren't obvious at first glance. Because of a), they aren't obvious at second either. **Therefore**, I suggest you edit the question and provide quite a lot more details about what you did and which errors you got. – Littlefoot Apr 01 '21 at 19:24
  • You tagged this question both [tag:mysql] and [tag:oracle]. You should be specific about which database you use, because it helps to get the attention of the experts who can give the best answer. I know Oracle Corp. owns the MySQL product, but in terms of tags here on Stack Overflow, the oracle tag refers to Oracle database, not MySQL. – Bill Karwin Apr 01 '21 at 19:24
  • You might like this checklist for common foreign key mistakes: https://stackoverflow.com/a/4673775/20860 That's about mysql, not oracle database, but the principles should be similar. – Bill Karwin Apr 01 '21 at 19:26

1 Answers1

0

Drop the constraints in the reverse order that you create them.

Also, you've misspelt PROJECTTASK as PROJJECTTASK.

ALTER TABLE PROJECTTASK DROP CONSTRAINT PROJECTTASK_pk
                        DROP CONSTRAINT fk_PROJECTTASK
                        DROP CONSTRAINT fk_PROJECTTASK2;

ALTER TABLE EMPLOYEE DROP CONSTRAINT EMPLOYEE_pk DROP CONSTRAINT fk_EMPLOYEE;
ALTER TABLE PROJECT DROP CONSTRAINT PROJECT_pk DROP CONSTRAINT fk_PROJECT;
ALTER TABLE PROJDEPT DROP CONSTRAINT PROJDEPT_pk;

ALTER TABLE PROJDEPT ADD CONSTRAINT PROJDEPT_pk PRIMARY KEY (Proj_Dept_ID);
ALTER TABLE PROJECT ADD CONSTRAINT PROJECT_pk PRIMARY KEY (Project_ID);
ALTER TABLE PROJECT
  ADD CONSTRAINT fk_PROJECT 
  FOREIGN KEY (Proj_Dept_ID)
  REFERENCES PROJDEPT(Proj_Dept_ID);

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_pk PRIMARY KEY (Employee_ID);
ALTER TABLE EMPLOYEE 
  ADD CONSTRAINT fk_EMPLOYEE 
  FOREIGN KEY (Proj_Dept_ID)
  REFERENCES PROJDEPT(Proj_Dept_ID);

ALTER TABLE PROJECTTASK ADD CONSTRAINT PROJECTTASK_pk PRIMARY KEY (Project_ID, Employee_ID);

ALTER TABLE PROJECTTASK 
  ADD CONSTRAINT fk_PROJECTTASK FOREIGN KEY (Project_ID) REFERENCES PROJECT(Project_ID);

ALTER TABLE PROJECTTASK 
  ADD CONSTRAINT fk_PROJECTTASK2 FOREIGN KEY (Employee_ID) REFERENCES EMPLOYEE(Employee_ID);

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117