Ticket #109 (new defect)

Opened 4 years ago

ondelete="RESTRICT" is ignored

Reported by: guest Owned by:
Priority: major Milestone:
Component: core Version: 0.7.1
Keywords: Cc:

Description

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"))

ELIXIR CODE

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.

Note: See TracTickets for help on using tickets.