From 74ae1f76877f3f070f3a3f30b3c52980491013c6 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 23 Sep 2012 12:24:24 -0400 Subject: [PATCH] - improve docs for MySQL/SQLite foreign key/ON UPDATE|DELETE instructions, [ticket:2514] --- doc/build/orm/collections.rst | 15 +++++++++-- lib/sqlalchemy/dialects/mysql/base.py | 9 ++++++- lib/sqlalchemy/dialects/sqlite/base.py | 36 ++++++++++++++++++++++++++ 3 files changed, 57 insertions(+), 3 deletions(-) diff --git a/doc/build/orm/collections.rst b/doc/build/orm/collections.rst index 822fcd3be6..053369baa7 100644 --- a/doc/build/orm/collections.rst +++ b/doc/build/orm/collections.rst @@ -114,8 +114,7 @@ Using Passive Deletes 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:: class MyClass(Base): __tablename__ = 'mytable' @@ -131,6 +130,18 @@ not supported on SQLite, and requires ``InnoDB`` tables when using MySQL:: ForeignKey('mytable.id', ondelete='CASCADE') ) + +.. note:: + + To use "ON DELETE CASCADE", the underlying database engine must + support foreign keys. + + * 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 diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index f2daec564a..28bbbed824 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -49,13 +49,16 @@ been idle for eight hours or more. To circumvent having this issue, use the engine = create_engine('mysql+mysqldb://...', pool_recycle=3600) +.. _mysql_storage_engines: + Storage Engines --------------- 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 @@ -68,6 +71,10 @@ creation option can be specified in this syntax:: mysql_charset='utf8' ) +.. seealso:: + + `The InnoDB Storage Engine `_ - on the MySQL website. + Case Sensitivity and Table Reflection ------------------------------------- diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index fb0f78619c..eb4cdacfb7 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -85,6 +85,42 @@ For more information on SQLite's lack of concurrency by design, please see `Situations Where Another RDBMS May Work Better - High Concurrency `_ near the bottom of the page. +.. _sqlite_foreign_keys: + +Foreign Key Support +------------------- + +SQLite supports FOREIGN KEY syntax when emitting CREATE statements for tables, +however by default these constraints have no effect on the operation +of the table. + +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 + before use. + +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") + cursor.close() + +.. seealso:: + + `SQLite Foreign Key Support `_ - + on the SQLite web site. + + :ref:`event_toplevel` - SQLAlchemy event API. + """ import datetime, re -- 2.47.2