I’m working on a project and I’m at a point where I need to be able to delete some records from the database. The deletion would work on cascade.
Example: I have a Poll and each poll can have questions and poll data. Each question can have question options and each poll data may have answers, etc. You get the point. The idea is to delete the poll, ask the user for confirmation, and then proceed to delete everything related to that poll.
I was working and doing this via ActiveRecord, but since each AR apprently creates an object and does 1 or 2 database calls, wouldn’t the complete operation be very taxing? Think of a poll with some 500 answers. I’m almost completely positive that it’s better to make a custom query (perhaps a routine to save in the database?) for the deletion, effectively lowering the database calls a whole lot, as well as minimizing the creation of objects.
Is there any particular reason why I should not do this, and proceed with the ActiveRecord method?
If you define foreign keys for your relations in the database you can add to them options like ‘ON UPATE CASCADE’ and ‘ON DELETE CASCADE’. So having tables like:
CREATE TABLE question (
id serial NOT NULL PRIMARY KEY,
text varchar NOT NULL
);
CREATE TABLE answer (
id serial NOT NULL PRIMARY KEY,
question_id integer NOT NULL REFERENCES question (id) ON UPDATE CASCADE ON DELETE CASCADE,
text varchar NOT NULL
);
when you execute:
DELETE * FROM question;
all related answers will be also removed.
Above code is based on PostgreSQL but also works in SQLite, except the serial type on ‘id’ columns.
See the manual from your database for more details.
Yes, we know about the Cascading options in the database tables. However, we would rather not have those on. We interact a lot directly with the database, and if we use the Database Cascading it opens the way for accidents and general stupidity. We’d rather have the database prevent such things, and have to manually delete whatever we need to.
Edit: Yeah, never mind. We will go for cascading and just make backups before we touch sensitive information when interacting directly with the database.
