Use ``passive_deletes=True`` to disable child object loading on a DELETE
operation, in conjunction with "ON DELETE (CASCADE|SET NULL)" on your database
-to automatically cascade deletes to child objects. Note that "ON DELETE" is
-not supported on SQLite, and requires ``InnoDB`` tables when using MySQL::
+to automatically cascade deletes to child objects::
__tablename__ = 'mytable'
+ To use "ON DELETE CASCADE", the underlying database engine must
+ * When using MySQL, an appropriate storage engine must be
+ selected. See :ref:`mysql_storage_engines` for details.
+ * When using SQLite, foreign key support must be enabled explicitly.
+ See :ref:`sqlite_foreign_keys` for details.
When ``passive_deletes`` is applied, the ``children`` relationship will not be
loaded into memory when an instance of ``MyClass`` is marked for deletion. The
``cascade="all, delete-orphan"`` *will* take effect for instances of
engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)
Most MySQL server installations have a default table type of ``MyISAM``, a
non-transactional table type. During a transaction, non-transactional storage
engines do not participate and continue to store table changes in autocommit
-mode. For fully atomic transactions, all participating tables must use a
+mode. For fully atomic transactions as well as support for foreign key
+constraints, all participating tables must use a
transactional engine such as ``InnoDB``, ``Falcon``, ``SolidDB``, `PBXT`, etc.
Storage engines can be elected when creating tables in SQLAlchemy by supplying
+ `The InnoDB Storage Engine <http://dev.mysql.com/doc/refman/5.0/en/innodb-storage-engine.html>`_ - on the MySQL website.
Case Sensitivity and Table Reflection
see `Situations Where Another RDBMS May Work Better - High Concurrency <http://www.sqlite.org/whentouse.html>`_
near the bottom of the page.
+SQLite supports FOREIGN KEY syntax when emitting CREATE statements for tables,
+however by default these constraints have no effect on the operation
+Constraint checking on SQLite has three prerequisites:
+* At least version 3.6.19 of SQLite must be in use
+* The SQLite libary must be compiled *without* the SQLITE_OMIT_FOREIGN_KEY
+ or SQLITE_OMIT_TRIGGER symbols enabled.
+* The ``PRAGMA foreign_keys = ON`` statement must be emitted on all connections
+SQLAlchemy allows for the ``PRAGMA`` statement to be emitted automatically
+for new connections through the usage of events::
+ from sqlalchemy.engine import Engine
+ from sqlalchemy import event
+ @event.listens_for(Engine, "connect")
+ def set_sqlite_pragma(dbapi_connection, connection_record):
+ cursor = dbapi_connection.cursor()
+ cursor.execute("PRAGMA foreign_keys=ON")
+ `SQLite Foreign Key Support <http://www.sqlite.org/foreignkeys.html>`_ -
+ on the SQLite web site.
+ :ref:`event_toplevel` - SQLAlchemy event API.