Reflect "NO ACTION" as None; support "RESTRICT"

The "NO ACTION" keyword for foreign key "ON UPDATE" is now considered to be
the default cascade for a foreign key on all supporting backends (SQlite,
MySQL, PostgreSQL) and when detected is not included in the reflection
dictionary; this is already the behavior for PostgreSQL and MySQL for all
previous SQLAlchemy versions in any case.   The "RESTRICT" keyword is
positively stored when detected; PostgreSQL does report on this keyword,
and MySQL as of version 8.0 does as well.  On earlier MySQL versions, it is
not reported by the database.

Fixes: #4741
Change-Id: I6becf1f2450605c1991158bb8a04d954dcc7396c

6 files changed, 54 insertions, 13 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 9aa80a5f4..da76201f2 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2541,7 +2541,7 @@ class MySQLDialect(default.DefaultDialect):
 
             con_kw = {}
             for opt in ("onupdate", "ondelete"):
-                if spec.get(opt, False):
+                if spec.get(opt, False) not in ("NO ACTION", None):
                     con_kw[opt] = spec[opt]
 
             fkey_d = {
diff --git a/lib/sqlalchemy/dialects/mysql/reflection.py b/lib/sqlalchemy/dialects/mysql/reflection.py
index b089483d7..3e5cc7b11 100644
--- a/lib/sqlalchemy/dialects/mysql/reflection.py
+++ b/lib/sqlalchemy/dialects/mysql/reflection.py
@@ -423,7 +423,7 @@ class MySQLTableDefinitionParser(object):
         #
         # unique constraints come back as KEYs
         kw = quotes.copy()
-        kw["on"] = "RESTRICT|CASCADE|SET NULL|NOACTION"
+        kw["on"] = "RESTRICT|CASCADE|SET NULL|NO ACTION"
         self._re_fk_constraint = _re_compile(
             r"  "
             r"CONSTRAINT +"
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 2bc48c53e..5aea02cfc 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -3150,19 +3150,24 @@ class PGDialect(default.DefaultDialect):
                 preparer._unquote_identifier(x)
                 for x in re.split(r"\s*,\s", referred_columns)
             ]
+            options = {
+                k: v
+                for k, v in [
+                    ("onupdate", onupdate),
+                    ("ondelete", ondelete),
+                    ("initially", initially),
+                    ("deferrable", deferrable),
+                    ("match", match),
+                ]
+                if v is not None and v != "NO ACTION"
+            }
             fkey_d = {
                 "name": conname,
                 "constrained_columns": constrained_columns,
                 "referred_schema": referred_schema,
                 "referred_table": referred_table,
                 "referred_columns": referred_columns,
-                "options": {
-                    "onupdate": onupdate,
-                    "ondelete": ondelete,
-                    "deferrable": deferrable,
-                    "initially": initially,
-                    "match": match,
-                },
+                "options": options,
             }
             fkeys.append(fkey_d)
         return fkeys
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index d6a713988..b6ca8fe3c 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1810,9 +1810,13 @@ class SQLiteDialect(default.DefaultDialect):
 
                 for token in re.split(r" *\bON\b *", onupdatedelete.upper()):
                     if token.startswith("DELETE"):
-                        options["ondelete"] = token[6:].strip()
+                        ondelete = token[6:].strip()
+                        if ondelete and ondelete != "NO ACTION":
+                            options["ondelete"] = ondelete
                     elif token.startswith("UPDATE"):
-                        options["onupdate"] = token[6:].strip()
+                        onupdate = token[6:].strip()
+                        if onupdate and onupdate != "NO ACTION":
+                            options["onupdate"] = onupdate
                 yield (
                     constraint_name,
                     constrained_columns,
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 3a2161740..04537905b 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -427,10 +427,22 @@ class SuiteRequirements(Requirements):
         return exclusions.closed()
 
     @property
+    def fk_constraint_option_reflection_ondelete_restrict(self):
+        return exclusions.closed()
+
+    @property
+    def fk_constraint_option_reflection_ondelete_noaction(self):
+        return exclusions.closed()
+
+    @property
     def foreign_key_constraint_option_reflection_onupdate(self):
         return exclusions.closed()
 
     @property
+    def fk_constraint_option_reflection_onupdate_restrict(self):
+        return exclusions.closed()
+
+    @property
     def temp_table_reflection(self):
         return exclusions.open()
 
diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index e529a0753..53e1599b3 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -671,10 +671,29 @@ class ComponentReflectionTest(fixtures.TablesTest):
     def test_get_foreign_key_options_onupdate(self):
         self._test_get_foreign_key_options(onupdate="SET NULL")
 
+    @testing.requires.foreign_key_constraint_option_reflection_onupdate
+    def test_get_foreign_key_options_onupdate_noaction(self):
+        self._test_get_foreign_key_options(onupdate="NO ACTION", expected={})
+
+    @testing.requires.fk_constraint_option_reflection_ondelete_noaction
+    def test_get_foreign_key_options_ondelete_noaction(self):
+        self._test_get_foreign_key_options(ondelete="NO ACTION", expected={})
+
+    @testing.requires.fk_constraint_option_reflection_onupdate_restrict
+    def test_get_foreign_key_options_onupdate_restrict(self):
+        self._test_get_foreign_key_options(onupdate="RESTRICT")
+
+    @testing.requires.fk_constraint_option_reflection_ondelete_restrict
+    def test_get_foreign_key_options_ondelete_restrict(self):
+        self._test_get_foreign_key_options(ondelete="RESTRICT")
+
     @testing.provide_metadata
-    def _test_get_foreign_key_options(self, **options):
+    def _test_get_foreign_key_options(self, expected=None, **options):
         meta = self.metadata
 
+        if expected is None:
+            expected = options
+
         Table(
             "x",
             meta,
@@ -714,7 +733,8 @@ class ComponentReflectionTest(fixtures.TablesTest):
         eq_(dict((k, opts[k]) for k in opts if opts[k]), {})
 
         opts = insp.get_foreign_keys("user")[0]["options"]
-        eq_(dict((k, opts[k]) for k in opts if opts[k]), options)
+        eq_(opts, expected)
+        # eq_(dict((k, opts[k]) for k in opts if opts[k]), expected)
 
     def _assert_insp_indexes(self, indexes, expected_indexes):
         index_names = [d["name"] for d in indexes]