Ticket #109 (new defect)
Opened 3 years ago
ondelete="RESTRICT" is ignored
|Reported by:||guest||Owned by:|
This was discovered while migrating an application from SA to Elixir.
The two following codes were assumed to produce the same result. SA CODE
wilaya_id = Column(Integer, ForeignKey("wilaya.id", ondelete="RESTRICT"))
wilaya = ManyToOne("Wilaya", ondelete="RESTRICT")
In the database, we confirm that the Foreign Key restrictions are in place (PostgreSQL):
CONSTRAINT cities_wilaya_id_fkey FOREIGN KEY (wilaya_id) REFERENCES wilayas (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT
What happens: In SA, deleting the parent of an instance of this class is blocked with a
IntegrityError: this instance is still being referenced in other tables, bla bla bla...
in Elixir, parent is deleted, instance is stranded with a foreign key that constrained no-one (Postgre's restrictions were bypassed), and now points nowhere, and, as a result, database is left in a mess.
reproductible: every single time.
It was suggested here http://groups.google.com/group/sqlelixir/browse_thread/thread/d7bcb1723f85dc13 to add "required=True" to the relation line in the ELIXIR code, but that's a workaround that cleverly masks the issue of foreign keys being ignored with one of a required column being set to NULL. The parent STILL gets deleted.
My question is: what does ondelete="RESTRICT" do in elixir, if it does not enforce ForeignKey constraints, as its name leads us to assume? Under what conditions does it work?
Contrary to what was said here:http://groups.google.com/group/sqlelixir/browse_thread/thread/d7bcb1723f85dc13 I see no way this can be expected behaviour.
AFAIC, this is a showstopper, because it makes Elixir unreliable as an ORM. I can't trust it with my database => reverting to plain SA.