- The :mod:`sqlalchemy.ext.automap` extension will now set

``cascade="all, delete-orphan"`` automatically on a one-to-many
relationship/backref where the foreign key is detected as containing
one or more non-nullable columns.  This argument is present in the
keywords passed to :func:`.automap.generate_relationship` in this
case and can still be overridden.  Additionally, if the
:class:`.ForeignKeyConstraint` specifies ``ondelete="CASCADE"``
for a non-nullable or ``ondelete="SET NULL"`` for a nullable set
of columns, the argument ``passive_deletes=True`` is also added to the
relationship.  Note that not all backends support reflection of
ondelete, but backends that do include Postgresql and MySQL.
fixes #3210

3 files changed, 123 insertions, 7 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 7d7548e11..88cae563f 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -22,6 +22,22 @@
     on compatibility concerns, see :doc:`/changelog/migration_10`.
 
     .. change::
+        :tags: feature, ext
+        :tickets: 3210
+
+        The :mod:`sqlalchemy.ext.automap` extension will now set
+        ``cascade="all, delete-orphan"`` automatically on a one-to-many
+        relationship/backref where the foreign key is detected as containing
+        one or more non-nullable columns.  This argument is present in the
+        keywords passed to :func:`.automap.generate_relationship` in this
+        case and can still be overridden.  Additionally, if the
+        :class:`.ForeignKeyConstraint` specifies ``ondelete="CASCADE"``
+        for a non-nullable or ``ondelete="SET NULL"`` for a nullable set
+        of columns, the argument ``passive_deletes=True`` is also added to the
+        relationship.  Note that not all backends support reflection of
+        ondelete, but backends that do include Postgresql and MySQL.
+
+    .. change::
         :tags: feature, sql
         :tickets: 3206
 
diff --git a/lib/sqlalchemy/ext/automap.py b/lib/sqlalchemy/ext/automap.py
index 121285ab3..c11795d37 100644
--- a/lib/sqlalchemy/ext/automap.py
+++ b/lib/sqlalchemy/ext/automap.py
@@ -243,7 +243,26 @@ follows:
    one-to-many backref will be created on the referred class referring
    to this class.
 
-4. The names of the relationships are determined using the
+4. If any of the columns that are part of the :class:`.ForeignKeyConstraint`
+   are not nullable (e.g. ``nullable=False``), a
+   :paramref:`~.relationship.cascade` keyword argument
+   of ``all, delete-orphan`` will be added to the keyword arguments to
+   be passed to the relationship or backref.  If the
+   :class:`.ForeignKeyConstraint` reports that
+   :paramref:`.ForeignKeyConstraint.ondelete`
+   is set to ``CASCADE`` for a not null or ``SET NULL`` for a nullable
+   set of columns, the option :paramref:`~.relationship.passive_deletes`
+   flag is set to ``True`` in the set of relationship keyword arguments.
+   Note that not all backends support reflection of ON DELETE.
+
+   .. versionadded:: 1.0.0 - automap will detect non-nullable foreign key
+      constraints when producing a one-to-many relationship and establish
+      a default cascade of ``all, delete-orphan`` if so; additionally,
+      if the constraint specifies :paramref:`.ForeignKeyConstraint.ondelete`
+      of ``CASCADE`` for non-nullable or ``SET NULL`` for nullable columns,
+      the ``passive_deletes=True`` option is also added.
+
+5. The names of the relationships are determined using the
    :paramref:`.AutomapBase.prepare.name_for_scalar_relationship` and
    :paramref:`.AutomapBase.prepare.name_for_collection_relationship`
    callable functions.  It is important to note that the default relationship
@@ -252,18 +271,18 @@ follows:
    alternate class naming scheme, that's the name from which the relationship
    name will be derived.
 
-5. The classes are inspected for an existing mapped property matching these
+6. The classes are inspected for an existing mapped property matching these
    names.  If one is detected on one side, but none on the other side,
    :class:`.AutomapBase` attempts to create a relationship on the missing side,
    then uses the :paramref:`.relationship.back_populates` parameter in order to
    point the new relationship to the other side.
 
-6. In the usual case where no relationship is on either side,
+7. In the usual case where no relationship is on either side,
    :meth:`.AutomapBase.prepare` produces a :func:`.relationship` on the
    "many-to-one" side and matches it to the other using the
    :paramref:`.relationship.backref` parameter.
 
-7. Production of the :func:`.relationship` and optionally the :func:`.backref`
+8. Production of the :func:`.relationship` and optionally the :func:`.backref`
    is handed off to the :paramref:`.AutomapBase.prepare.generate_relationship`
    function, which can be supplied by the end-user in order to augment
    the arguments passed to :func:`.relationship` or :func:`.backref` or to
@@ -877,6 +896,19 @@ def _relationships_for_fks(automap_base, map_config, table_to_map_config,
                 constraint
             )
 
+            o2m_kws = {}
+            nullable = False not in set([fk.parent.nullable for fk in fks])
+            if not nullable:
+                o2m_kws['cascade'] = "all, delete-orphan"
+
+                if constraint.ondelete and \
+                        constraint.ondelete.lower() == "cascade":
+                    o2m_kws['passive_deletes'] = True
+            else:
+                if constraint.ondelete and \
+                        constraint.ondelete.lower() == "set null":
+                    o2m_kws['passive_deletes'] = True
+
             create_backref = backref_name not in referred_cfg.properties
 
             if relationship_name not in map_config.properties:
@@ -885,7 +917,8 @@ def _relationships_for_fks(automap_base, map_config, table_to_map_config,
                         automap_base,
                         interfaces.ONETOMANY, backref,
                         backref_name, referred_cls, local_cls,
-                        collection_class=collection_class)
+                        collection_class=collection_class,
+                        **o2m_kws)
                 else:
                     backref_obj = None
                 rel = generate_relationship(automap_base,
@@ -916,7 +949,8 @@ def _relationships_for_fks(automap_base, map_config, table_to_map_config,
                                                 fk.parent
                                                 for fk in constraint.elements],
                                             back_populates=relationship_name,
-                                            collection_class=collection_class)
+                                            collection_class=collection_class,
+                                            **o2m_kws)
                 if rel is not None:
                     referred_cfg.properties[backref_name] = rel
                     map_config.properties[
diff --git a/test/ext/test_automap.py b/test/ext/test_automap.py
index 6cfd0fbca..0a57b9caa 100644
--- a/test/ext/test_automap.py
+++ b/test/ext/test_automap.py
@@ -1,7 +1,7 @@
 from sqlalchemy.testing import fixtures
 from ..orm._fixtures import FixtureTest
 from sqlalchemy.ext.automap import automap_base
-from sqlalchemy.orm import relationship, interfaces
+from sqlalchemy.orm import relationship, interfaces, configure_mappers
 from sqlalchemy.ext.automap import generate_relationship
 from sqlalchemy.testing.mock import Mock
 from sqlalchemy import String, Integer, ForeignKey
@@ -157,6 +157,72 @@ class AutomapTest(fixtures.MappedTest):
         ])
 
 
+class CascadeTest(fixtures.MappedTest):
+    @classmethod
+    def define_tables(cls, metadata):
+        Table(
+            "a", metadata,
+            Column('id', Integer, primary_key=True)
+        )
+        Table(
+            "b", metadata,
+            Column('id', Integer, primary_key=True),
+            Column('aid', ForeignKey('a.id'), nullable=True)
+        )
+        Table(
+            "c", metadata,
+            Column('id', Integer, primary_key=True),
+            Column('aid', ForeignKey('a.id'), nullable=False)
+        )
+        Table(
+            "d", metadata,
+            Column('id', Integer, primary_key=True),
+            Column(
+                'aid', ForeignKey('a.id', ondelete="cascade"), nullable=False)
+        )
+        Table(
+            "e", metadata,
+            Column('id', Integer, primary_key=True),
+            Column(
+                'aid', ForeignKey('a.id', ondelete="set null"),
+                nullable=True)
+        )
+
+    def test_o2m_relationship_cascade(self):
+        Base = automap_base(metadata=self.metadata)
+        Base.prepare()
+
+        configure_mappers()
+
+        b_rel = Base.classes.a.b_collection
+        assert not b_rel.property.cascade.delete
+        assert not b_rel.property.cascade.delete_orphan
+        assert not b_rel.property.passive_deletes
+
+        assert b_rel.property.cascade.save_update
+
+        c_rel = Base.classes.a.c_collection
+        assert c_rel.property.cascade.delete
+        assert c_rel.property.cascade.delete_orphan
+        assert not c_rel.property.passive_deletes
+
+        assert c_rel.property.cascade.save_update
+
+        d_rel = Base.classes.a.d_collection
+        assert d_rel.property.cascade.delete
+        assert d_rel.property.cascade.delete_orphan
+        assert d_rel.property.passive_deletes
+
+        assert d_rel.property.cascade.save_update
+
+        e_rel = Base.classes.a.e_collection
+        assert not e_rel.property.cascade.delete
+        assert not e_rel.property.cascade.delete_orphan
+        assert e_rel.property.passive_deletes
+
+        assert e_rel.property.cascade.save_update
+
+
 class AutomapInhTest(fixtures.MappedTest):
     @classmethod
     def define_tables(cls, metadata):