Can't delete data that has multiple Foreign Keys

Question

These are my tables:

CREATE TABLE COACH (
  COACH_ID  VARCHAR(8),
  COACH_NAME    VARCHAR(50),
  COACH_ADD VARCHAR(100),
  COACH_PHONENO CHAR(12),
  PRIMARY KEY (COACH_ID));

CREATE TABLE TEAM (
  TEAM_ID       VARCHAR(8),
  COACH_ID  VARCHAR(8),
  TEAM_NAME VARCHAR(50),
  TEAM_COLOUR   VARCHAR(10),
  PRIMARY KEY (TEAM_ID),
  CONSTRAINT TEAM_COACH_ID_FK 
  FOREIGN KEY (COACH_ID) REFERENCES COACH);

CREATE TABLE PARTICIPANT (
  PART_ID       VARCHAR(8),
  TEAM_ID       VARCHAR(8),
  SPORT_NAME    VARCHAR(15),
  PART_NAME VARCHAR(50),
  PART_IC       VARCHAR(14),
  PART_PHONENO  CHAR(12),
  PRIMARY KEY (PART_ID),
  CONSTRAINT SPORT_TEAM_ID_FK
  FOREIGN KEY (SPORT_NAME) REFERENCES SPORT,
  CONSTRAINT TEAM_TEAM_ID_FK
  FOREIGN KEY (TEAM_ID) REFERENCES TEAM );

This is my SQL:

DELETE FROM TEAM 
WHERE TEAM_COLOUR='Magenta';

The code is generating this error:

Error report:
SQL Error: ORA-02292: integrity constraint (HR.TEAM_TEAM_ID_FK) violated - child record found
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause: attempted to delete a parent key value that had a foreign dependency. *Action: delete dependencies first then parent or disable constraint.

I want to DELETE TEAM_COLOUR = 'MAGENTA'

Then first, delete from participant all those participants on a team who have team_color magenta first. Or enable cascade delete. — xQbert, May 24 '17 at 14:06
No.. you'd first have to enable cascasde delete by altering the DDL and then delete, or you'd have to run two delete statements. — xQbert, May 24 '17 at 14:08
See here: https://stackoverflow.com/questions/1571581/how-to-add-on-delete-cascade-in-alter-table-statement you have to first drop the existing constraint, then add it back with cascade delete enabled. — xQbert, May 24 '17 at 14:09
i dont know what the difference between child.column.name and parent.column.name that it said. — arina puteri, May 24 '17 at 14:17

score 0 · Answer 1 · answered May 24 '17 at 14:08

0

First delete all the Participants that are members of any team with Team_Colour='Magenta'.

Then you will be able to delete the Team.

answered May 24 '17 at 14:08

Tab Alleman

31,483
7
36
52

thats mean i need to do more than one query? – arina puteri May 24 '17 at 14:09
Yes. I'm afraid it does. – Tab Alleman May 24 '17 at 14:09

xQbert · Answer 2 · 2017-05-24T14:49:53.240

0

Perhaps something like:

The syntax works: still not sure if this is the RIGHT action for you though...

But be warned; cascade delete can be dangerous. If you delete a team accidentally you lose all the participant information as well... That is why many people prefer to do two delete statements.

ALTER TABLE PARTICIPANT
  DROP CONSTRAINT TEAM_TEAM_ID_FK;

ALTER TABLE PARTICIPANT
  ADD CONSTRAINT TEAM_TEAM_ID_FK
  FOREIGN KEY (TEAM_ID) 
  REFERENCES TEAM(TEAM_ID) 
  ON DELETE CASCADE;

Then

DELETE FROM TEAM WHERE TEAM_COLOUR='Magenta';

should work.

Here's the entire script:

CREATE TABLE COACH (
  COACH_ID  VARCHAR(8),
  COACH_NAME    VARCHAR(50),
  COACH_ADD VARCHAR(100),
  COACH_PHONENO CHAR(12),
  PRIMARY KEY (COACH_ID));

CREATE TABLE TEAM (
  TEAM_ID       VARCHAR(8),
  COACH_ID  VARCHAR(8),
  TEAM_NAME VARCHAR(50),
  TEAM_COLOUR   VARCHAR(10),
  PRIMARY KEY (TEAM_ID),
  CONSTRAINT TEAM_COACH_ID_FK 
  FOREIGN KEY (COACH_ID) REFERENCES COACH);

CREATE TABLE PARTICIPANT (
  PART_ID       VARCHAR(8),
  TEAM_ID       VARCHAR(8),
  SPORT_NAME    VARCHAR(15),
  PART_NAME VARCHAR(50),
  PART_IC       VARCHAR(14),
  PART_PHONENO  CHAR(12),
  PRIMARY KEY (PART_ID),
  CONSTRAINT TEAM_TEAM_ID_FK
  FOREIGN KEY (TEAM_ID) REFERENCES TEAM );

  insert into coach values (1,'Test1',NULL,NULL);
  Insert into Team values (1,1,'Test1','Blue');
  insert into team values (2,1,'Test1','Magenta');
  Insert into Participant (part_ID, Team_ID) values (1,1);
  Insert into Participant (part_ID, Team_ID) values (2,1);
  Insert into Participant (part_ID, Team_ID) values (3,2);

ALTER TABLE PARTICIPANT DROP Constraint TEAM_TEAM_ID_FK;

ALTER TABLE PARTICIPANT
  ADD CONSTRAINT TEAM_TEAM_ID_FK
  FOREIGN KEY (TEAM_ID) 
  REFERENCES TEAM(TEAM_ID) 
  ON DELETE CASCADE;
  DELETE FROM TEAM WHERE TEAM_COLOUR='Magenta';

edited May 24 '17 at 14:49

answered May 24 '17 at 14:18

xQbert

34,733
2
41
62

ALTER TABLE tbl_magazine_issue TABLE TEAM? – arina puteri May 24 '17 at 14:20
Oops. copy and paste error. still testing I've corrected – xQbert May 24 '17 at 14:21
#CODING ALTER TABLE TEAM DROP FOREIGN KEY TEAM_TEAM_ID_FK; ALTER TABLE PARTICIPANT ADD CONSTRAINT TEAM_TEAM_ID_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID) ON DELETE CASCADE; #ERROR ALTER TABLE PARTICIPANT ADD CONSTRAINT TEAM_TEAM_ID_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID) ON DELETE CASCADE Error report: SQL Error: ORA-02275: such a referential constraint already exists in the table "such a referential constraint already exists in the table" *Cause: Self-evident. *Action: Remove the extra constraint. – arina puteri May 24 '17 at 14:24
Why are you altering table team? you need to alter the tables in which Team is a FK (participant) in your example. – xQbert May 24 '17 at 14:26
you're not dropping the constraint from the participant table... you dropped it from team.... `ALTER TABLE TEAM DROP FOREIGN KEY TEAM_TEAM_ID_FK; ` is wrong `ALTER TABLE PARTICIPANT DROP FOREIGN KEY TEAM_TEAM_ID_FK;` is correct. – xQbert May 24 '17 at 14:29
i have change it, but still got same error. maybe because need to do same thing for coach table? – arina puteri May 24 '17 at 14:32
No. If you're just deleting a team. Participant is the only which has team as a Foreign key. So you just need to alter the participant table to drop the FK to team, re-add the FK back with delete cascade enabled. I've put together an SQL fiddle showing it working in the link in the answer above. You still need to ask your self if this is the correct course of action or if you should delete from the participant first then delete from the team. – xQbert May 24 '17 at 14:36
Error starting at line 1 in command: ALTER TABLE PARTICIPANT ADD CONSTRAINT TEAM_TEAM_ID_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID) ON DELETE CASCADE Error report: SQL Error: ORA-02275: such a referential constraint already exists in the table 02275. 00000 - "such a referential constraint already exists in the table" *Cause: Self-evident. *Action: Remove the extra constraint. #i got this error – arina puteri May 24 '17 at 14:38
Error starting at line 1 in command: ALTER TABLE PARTICIPANT DROP FOREIGN KEY TEAM_TEAM_ID_FK Error report: SQL Error: ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause: *Action: – arina puteri May 24 '17 at 14:39
This implies you've not deleted the constraint before attempting to add the new one. you must first remove it then add it as above. – xQbert May 24 '17 at 14:40
really sorry, i dont understand it. i have doing this project continuosly 7 hours. so i cant think well now. so can you just give command that i need to do. really sorry. :( – arina puteri May 24 '17 at 14:42
I thought this was mySQL for some reason: `ALTER TABLE PARTICIPANT DROP CONSTRAINT TEAM_TEAM_ID_FK;` is what you need for oracle. I guess this line made me think mySQL... "This is my SQL:" – xQbert May 24 '17 at 14:49

Can't delete data that has multiple Foreign Keys

2 Answers2