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.