CODEWITHSUNDEEP
sqloracle-databaseoracle11g
yooouuri
yooouuri

Reputation: 2658

On Delete Cascade fail

I have two tables, first my Account table

DROP TABLE "Account" CASCADE CONSTRAINTS; 
CREATE table "Account" (
  "Id" NUMBER(5) NOT NULL,
  "Name" VARCHAR2(32) NOT NULL,
  "User" VARCHAR2(16) NOT NULL,
  "Email" VARCHAR2(32) NOT NULL,
  "Password" VARCHAR2(16) NOT NULL,
  "Level" VARCHAR2(16) NOT NULL,
  CONSTRAINT "Account_pk" PRIMARY KEY ("Id")
);

ALTER TABLE "Account"
  ADD UNIQUE ("Name", "User", "Email");

DROP SEQUENCE Account_seq;
CREATE SEQUENCE Account_seq START WITH 0
  INCREMENT BY 1
  MINVALUE 0
  MAXVALUE 10000;

Then insert some dummy data

INSERT INTO "Account" ("Id", "Name", "User", "Email", "Password", "Level") VALUES (Account_seq.NEXTVAL, 'Jan', 'Jan1993', '[email protected]', 'password', 'user');
INSERT INTO "Account" ("Id", "Name", "User", "Email", "Password", "Level") VALUES (Account_seq.NEXTVAL, 'Piet', 'Piet1978', '[email protected]', 'password', 'admin');

And i have a Review table

DROP TABLE "Review" CASCADE CONSTRAINTS; 
CREATE table "Review" (
  "Id" NUMBER(5) NOT NULL,
  "ReactionId" NUMBER(5) NOT NULL,
  "UserId" NUMBER(5) NOT NULL,
  "Score" NUMBER(1) NOT NULL,
  "Date" DATE NOT NULL,
  CONSTRAINT "Review_pk" PRIMARY KEY ("Id")
);

ALTER TABLE "Review"
  ADD CONSTRAINT "Review_Reaction_fk"
  FOREIGN KEY ("ReactionId")
  REFERENCES "Reaction" ("Id")
  ON DELETE CASCADE;

ALTER TABLE "Review"
  ADD CONSTRAINT "Review_User_fk"
  FOREIGN KEY ("UserId")
  REFERENCES "Account" ("Id")
  ON DELETE CASCADE;

DROP SEQUENCE Review_seq;
CREATE SEQUENCE Review_seq START WITH 0
  INCREMENT BY 1
  MINVALUE 0
  MAXVALUE 10000;

And again i insert some dummy data

INSERT INTO "Review" ("Id", "ReactionId", "UserId", "Score", "Date") VALUES (Review_seq.NEXTVAL, 1, 2, 1, SYSDATE);
INSERT INTO "Review" ("Id", "ReactionId", "UserId", "Score", "Date") VALUES (Review_seq.NEXTVAL, 2, 1, 0, SYSDATE);

When i remove a user with Id = 1 it deletes all the data in the Review table. But it should only delete the data where UserId = 1...

Upvotes: 0

Views: 762

Answers (2)

TommCatt
TommCatt

Reputation: 5636

The "on delete cascade" option, during the first years of relational databases, was a nifty idea. You didn't have to worry about following relational links back to their origins. Just delete the entity and the system will do all the grunt work for you.

However, there are some serious drawbacks. The unintended consequences for one: "Wait! I didn't mean to delete all that other stuff." Then there is the locking. Before a row can be deleted, it must be locked. But if there are FK references to the row, those must be searched out and locked. If any of those have FK references, another level of "search out and lock" takes place. Again and again until everything is safely locked. The trouble is, it doesn't take very much of this to bring your entire database to a screeching halt. Everything is locked or waiting for a lock to be released.

I'm glad I'm not a DBA. It's times like these I'm really glad I'm not a DBA!

A few more problems like the one you are having may convince you the "on delete cascade" is generally a Bad Idea. You're probably a lot better off just taking the time to write the code to search out the chain of relationships and delete them in an intelligent manner. It will also give you the opportunity to show some "Are your sure?" second chance opportunities to the users. Just in case.

Upvotes: -1

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Only possible reason, I could guess is the mysterious constraint,

"Review_Reaction_fk"

it refers Reaction's "Id" as foreign key.

ALTER TABLE "Review"
  ADD CONSTRAINT "Review_Reaction_fk"
  FOREIGN KEY ("ReactionId")
  REFERENCES "Reaction" ("Id") ----> Here
  ON DELETE CASCADE;

Perhaps, reaction table refers "Account" table, and it gets the record deleted, that "Review" refers.

Upvotes: 1

Related Questions