0

I have a table in my front-end code where I show a series of exercises and next to each of them, there’s a button to be able to eliminate the exercise. The problem is that my exercises table is related to other tables:

CREATE TABLE exercises (
    exercise_id INT,
    PRIMARY KEY(exercise_id)
);

CREATE TABLE ex_tag(
    exercise_id_fk INT,
    tag_id_fk INT,
    FOREIGN KEY(exercise_id_fk) REFERENCES exercises(exercise_id),
    FOREIGN KEY(tag_id_fk) REFERENCES tags(tag_id)
);

CREATE TABLE solution(
    exercise_id_fk INT,
    solution_number INT,
    FOREIGN KEY(exercise_id_fk) REFERENCES exercises(exercise_id)
);

And, sometimes, an entry in the following table is created. If the exercise is answered, it will be created, but if not, it will not be created.

CREATE TABLE answers(
    exercise_id_fk INT,
    student_id INT,
    FOREIGN KEY(exercise_id_fk) REFERENCES exercises(exercise_id)
);

If the table 'exercises' had no relation to any table, this query would work:

DELETE FROM exercises WHERE exercise_id=$id

How can I change the query to make it possible to erase the selected exercise from all the tables that need it, with this single exercise_id?

miken32
  • 42,008
  • 16
  • 111
  • 154
ana
  • 417
  • 2
  • 10
  • 1
    **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman May 06 '19 at 15:33
  • Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is largely an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman May 06 '19 at 15:33
  • 3
    Are you talking about a cascade deletion on that foreign key? – tadman May 06 '19 at 15:34
  • I rarely use the `DELETE FROM`. I usually have a column called `deleted` which is null as default and then set it with the current date when it's deleted. Then you still have the data for history (and if you accidentally delete something it's easy to revert it). Then when you select items, just add `deleted IS NULL` in the WHERE-clause.. – M. Eriksson May 06 '19 at 15:35
  • @tadman yes i'm talking about deleting data from multiple related tables – ana May 06 '19 at 15:35
  • @MagnusEriksson that's very interesting, but I would have to change code from many sites to do that... – ana May 06 '19 at 15:38
  • 2
    To add to tadman's comment, read up on the `ON DELETE` options in a foreign key definition. – Jerry May 06 '19 at 15:52
  • "How can I [..] erase the selected exercise from all the tables". You just `DELETE` all related records. – Paul Spiegel May 06 '19 at 16:19
  • Possible duplicate of [Foreign key constraints: When to use ON UPDATE and ON DELETE](https://stackoverflow.com/questions/6720050/foreign-key-constraints-when-to-use-on-update-and-on-delete) – miken32 May 06 '19 at 17:54

0 Answers0