SQLite: How can I turn on "ON DELETE CASCADE" from within sqlalchemy?

[Jinghui Niu]

I know you can set this constraint if you are directly dealing with sqlite3, but how can I achieve this database level setting from within sqlalchemy?

The documentation reads:

"Note that these clauses are not supported on SQLite, and require InnoDB tables when used with MySQL. They may also not be supported on other databases."

So is there a way to turn this feature on from within sqlalchemy? Thanks. 

[Mike Bayer]

that documentation is probably out of date.  The ON DELETE CASCADE directive will emit on SQLite like on any other backend.   You would need to enable foreign key support on a per-connection basis for sqlite3 in order for them to take effect:

http://docs.sqlalchemy.org/en/rel_1_0/dialects/sqlite.html#foreign-key-support

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Jinghui Niu]

Hi Mike. Thanks for your reply. That's great news!

Just want to clarify on which level such constraints will be placed? sqlalchemy or the underlying database?

So now if I want to use passive delete, which relies solely on the underlying SQLite database's constraints not the sqlalchemy's, I just need to set the foreign key argument like this:

ForeignKey('items.record_id', ondelete='CASCADE'))

when I declare the classes. This will set the constraint on the underlying database. Is my understanding correct?

[Mike Bayer]

On 8/6/15 5:56 PM, Jinghui Niu wrote:

Hi Mike. Thanks for your reply. That's great news!

Just want to clarify on which level such constraints will be placed? sqlalchemy or the underlying database?

So now if I want to use passive delete, which relies solely on the underlying SQLite database's constraints not the sqlalchemy's, I just need to set the foreign key argument like this:

ForeignKey('items.record_id', ondelete='CASCADE'))

when I declare the classes. This will set the constraint on the underlying database. Is my understanding correct?

yes the ondelete key at that level is for the DDL emitted to the database when you emit metadata.create_all(), assuming the table is not there yet.