From f06c6ba67303e5c75d8ad044494193d8f97b2e2e Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Sun, 18 Aug 2019 13:03:49 -0400
Subject: Reflect PK of referred table if referred columns not present

Fixed bug where a FOREIGN KEY that was set up to refer to the parent table
by table name only without the column names would not correctly be
reflected as far as setting up the "referred columns", since SQLite's
PRAGMA does not report on these columns if they weren't given explicitly.
For some reason this was harcoded to assume the name of the local column,
which might work for some cases but is not correct. The new approach
reflects the primary key of the referred table and uses the constraint
columns list as the referred columns list, if the remote column(s) aren't
present in the reflected pragma directly.

Fixes: #4810
Change-Id: I7789f83d68845ae197a782080af8ec64a7bf48cc
---
 lib/sqlalchemy/dialects/sqlite/base.py | 24 ++++++++++++++++++++----
 1 file changed, 20 insertions(+), 4 deletions(-)

(limited to 'lib/sqlalchemy/dialects/sqlite')

diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 78ce18ac6..7be0e06dc 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1754,8 +1754,22 @@ class SQLiteDialect(default.DefaultDialect):
         for row in pragma_fks:
             (numerical_id, rtbl, lcol, rcol) = (row[0], row[2], row[3], row[4])
 
-            if rcol is None:
-                rcol = lcol
+            if not rcol:
+                # no referred column, which means it was not named in the
+                # original DDL.  The referred columns of the foreign key
+                # constraint are therefore the primary key of the referred
+                # table.
+                referred_pk = self.get_pk_constraint(
+                    connection, rtbl, schema=schema, **kw
+                )
+                # note that if table doesnt exist, we still get back a record,
+                # just it has no columns in it
+                referred_columns = referred_pk["constrained_columns"]
+            else:
+                # note we use this list only if this is the first column
+                # in the constraint.  for subsequent columns we ignore the
+                # list and append "rcol" if present.
+                referred_columns = []
 
             if self._broken_fk_pragma_quotes:
                 rtbl = re.sub(r"^[\"\[`\']|[\"\]`\']$", "", rtbl)
@@ -1768,13 +1782,15 @@ class SQLiteDialect(default.DefaultDialect):
                     "constrained_columns": [],
                     "referred_schema": schema,
                     "referred_table": rtbl,
-                    "referred_columns": [],
+                    "referred_columns": referred_columns,
                     "options": {},
                 }
                 fks[numerical_id] = fk
 
             fk["constrained_columns"].append(lcol)
-            fk["referred_columns"].append(rcol)
+
+            if rcol:
+                fk["referred_columns"].append(rcol)
 
         def fk_sig(constrained_columns, referred_table, referred_columns):
             return (
-- 
cgit v1.2.1