0

I have set my datas with separate tables inheritance mapping, but Doctrine seems to give very limited controls about it.
Among other issues, one I get is that it only cascades on DELETE.

(I use XML mapping)
From what I see, cascades are put inside the relation tag, which is absent on an ID column using inheritance mapping.

Is there no easy way to ensure my datas stay consistent ? (e.g. if accessed from outside) ?
Without this, even a simple update on one of my lines of data becomes complex, since it would violate constraints.

EDIT @Cerad : here's a sample working example. I use MariaDB 10.3.22

CREATE DATABASE kaskade;
USE kaskade;

CREATE TABLE a (main_id INT PRIMARY KEY);
CREATE TABLE b (fk_id INT PRIMARY KEY);
INSERT INTO a (main_id) VALUES (1), (2), (3);
INSERT INTO b (fk_id) VALUES (1), (2), (3);

ALTER TABLE b ADD CONSTRAINT id_link FOREIGN KEY (fk_id) REFERENCES a(main_id) ON UPDATE CASCADE;

SELECT * from a;
SELECT * from b;
UPDATE a SET main_id = main_id + 10 WHERE main_id > 1;
#UPDATE b SET id = 5 where id = 1;  // Would be rejected, since the cascade must come from the "parent"
SELECT * from a;
SELECT * from b;
Balmipour
  • 2,985
  • 1
  • 24
  • 28
  • Are you trying to update an actual id column? Because that is just asking for trouble. Or is something else going on? – Cerad Oct 02 '20 at 11:54
  • @Cerad I'd like to put as much constraints as possible on my data, so that they remain consistent, no matter where they were modified from. Since the id from the mother-class is referenced with a FK, I'd like it to update on both tables at once. I thought it's possible on the SQL side, isn't it ? – Balmipour Oct 02 '20 at 12:24
  • Not really. Anytime you try to reference an id used as a foreign key you will encounter problems. Make yourself a couple of simple tables from a sql admin console and try for yourself. As a rule, id's should be left alone once a database row is created. And you should avoid exposing them to the outside world. All basic database stuff. – Cerad Oct 02 '20 at 12:29
  • @Cerad Just added a sample. While I agree manipulating IDs might not be a good thing, it all depends on one's needs IMO. For example, a small users database could use ugly VARCHAR as user IDS, and allow them to be renamed. Anyway, my question was : Is it possible to achieve this simple SQL need with Doctrine ? I'm afraid it's not. (in the same idea, it seems I can't put a simple "check" constraint... which puzzles me. – Balmipour Oct 02 '20 at 13:52

0 Answers0