Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For a project in school I'm designing a website that sells items via auctions. Think eBay, but on the complexity scale of school project. We went through the process of making an ER Diagram and planning things out but we still ran into a snag with the table creation statements in SQL. It's a problem I've never had before.

I know what the problem is now after a bit of digging. There is a table with two foreign keys to two different tables. One of those two tables has a foreign key into the other. That's all fine and great. Every foreign key has an ON DELETE CASCADE except for one, which has ON DELETE SET NULL because it makes business sense. Derby is baffing at the creation of the table with the two keys, because deleting a record in one table would cause two different actions in that table.

A clearer example, in SQL.

CREATE TABLE A (
  PrimaryA BIGINT,
  PRIMARY KEY (PrimaryA)
);

CREATE TABLE B ( 
  PrimaryB BIGINT,
  ForeignA BIGINT NOT NULL,
  PRIMARY KEY (PrimaryB),
  FOREIGN KEY (ForeignA) 
    REFERENCES A(PrimaryA)
    ON DELETE CASCADE
);


CREATE TABLE C (
  PrimaryC BIGINT,
  ForeignA BIGINT
    DEFAULT NULL,
  ForeignB BIGINT
    NOT NULL,
  PRIMARY KEY (PrimaryC),
  FOREIGN KEY (ForeignA) 
    REFERENCES A(PrimaryA)
    ON DELETE SET NULL,
  FOREIGN KEY (ForeignB)
    REFERENCES B(PrimaryB)
    ON DELETE CASCADE
);

So if those three statements went through (#3 wont) and someone deleted a row in A, there is the possibility that a row in C would want to be deleted and set null at the same time. While I respect the issue of the concurrency, shouldn't the deletion win? It isn't technically against the business logic for that to happen, but if it did I'd expect the row to be deleted.

So I obviously need to restructure the schema to avoid this pitfall but I can't really think of a way to do it and was hoping there was some magic keyword that would tell Derby to just delete the row and move on with life. If I do need to restructure, the context is that table C is the auction. Each auction has exactly one feedback entry, which is B. It also has exactly one user who is selling the item (A). Feedback (B) entries keep track of the user (A) who places them. In this respect it is the buyer who leaves the feedback.

Now it makes perfect sense to delete the auction (C) if the user (A) who is selling the item has their account deleted. However it doesn't make sense for the auction to be deleted if a rouge user (A) leaves foul feeback (B) on an auction (C) and gets their account deleted. It also doesn't make sense to populate the foreign key to B from C until after the auction is closed and the buyer (A) can leave feedback, which is why it is set to NULL initially. Like I said before, it is technically possible for a user (A) to create an auction (C) and then buy the item from themselves and leave feedback (B) and then have their account deleted. In that case the ON DELETE CASCADE should win from a business logic point of view.

I read through this question and the resolution was to normalize, which I've done already. In that question this was linked, and there they offered a solution to the multiple cascade path that I don't fully understand, but I get enough to believe it isn't a solution to this because I don't actually want to delete along both paths. Another thing mentioned was triggers. I have a rough understanding of triggers at a high level but have never designed or coded one before.

share|improve this question
1  
If B is the Feedback and C is the Auction table, I wonder why you set the FK this way. It seems it should be the other way around (B referencing C). –  ypercube Mar 24 '13 at 10:03
    
@ypercube Back in the original diagram, there was no distinction between the auction and the feedback as they were the same entity. The professor advised us to make them discrete components, leading to what it is today. You suggestion does make sense though, as then the deletion of either the buyer or the auction would remove the feedback. However if a User was deleted, wouldn't there be multiple cascade paths to the feedback entity? (User<-Feedback and User<-Auction<-Feedback) –  Huckle Mar 24 '13 at 16:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.