warn and skip for FKs that refer to invisible cols for Oracle

Supported use case for foreign key constraints where the local column is
marked as "invisible". The errors normally generated when a
:class:`.ForeignKeyConstraint` is created that check for the target column
are disabled when reflecting, and the constraint is skipped with a warning
in the same way which already occurs for an :class:`.Index` with a similar
issue.

tests are added for indexes, unique constraints, and primary key
constraints, which were already working; indexes and uniques warn,
primary keys don't which we would assume is because we never see those
PK columns in the first place.

Constraints now raise an informative ConstraintColumnNotFoundError
in the general case for strings in the "pending colargs" collection
not being resolvable.

Fixes: #9059
Change-Id: I400cf0bff6abba0e0c75f38b07617be1a8ec3453

3 files changed, 39 insertions, 21 deletions

diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py
index 7e1fca0e5..941590b13 100644
--- a/lib/sqlalchemy/engine/reflection.py
+++ b/lib/sqlalchemy/engine/reflection.py
@@ -1786,16 +1786,25 @@ class Inspector(inspection.Inspectable["Inspector"]):
             else:
                 options = {}
 
-            table.append_constraint(
-                sa_schema.ForeignKeyConstraint(
-                    constrained_columns,
-                    refspec,
-                    conname,
-                    link_to_name=True,
-                    comment=fkey_d.get("comment"),
-                    **options,
+            try:
+                table.append_constraint(
+                    sa_schema.ForeignKeyConstraint(
+                        constrained_columns,
+                        refspec,
+                        conname,
+                        link_to_name=True,
+                        comment=fkey_d.get("comment"),
+                        **options,
+                    )
+                )
+            except exc.ConstraintColumnNotFoundError:
+                util.warn(
+                    f"On reflected table {table.name}, skipping reflection of "
+                    "foreign key constraint "
+                    f"{conname}; one or more subject columns within "
+                    f"name(s) {', '.join(constrained_columns)} are not "
+                    "present in the table"
                 )
-            )
 
     _index_sort_exprs = {
         "asc": operators.asc_op,
diff --git a/lib/sqlalchemy/exc.py b/lib/sqlalchemy/exc.py
index eb633ec8c..82d9d31ce 100644
--- a/lib/sqlalchemy/exc.py
+++ b/lib/sqlalchemy/exc.py
@@ -165,6 +165,15 @@ class AmbiguousForeignKeysError(ArgumentError):
     between two selectables during a join."""
 
 
+class ConstraintColumnNotFoundError(ArgumentError):
+    """raised when a constraint refers to a string column name that
+    is not present in the table being constrained.
+
+    .. versionadded:: 2.0
+
+    """
+
+
 class CircularDependencyError(SQLAlchemyError):
     """Raised by topological sorts when a circular dependency is detected.
 
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 62e962a32..5083940f0 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -4014,10 +4014,17 @@ class ColumnCollectionMixin:
             assert len(result) == len(self._pending_colargs)
             return result
         else:
-            return [
-                parent.c[col] if isinstance(col, str) else col
-                for col in self._pending_colargs
-            ]
+            try:
+                return [
+                    parent.c[col] if isinstance(col, str) else col
+                    for col in self._pending_colargs
+                ]
+            except KeyError as ke:
+                raise exc.ConstraintColumnNotFoundError(
+                    f"Can't create {self.__class__.__name__} "
+                    f"on table '{parent.description}': no column "
+                    f"named '{ke.args[0]}' is present."
+                ) from ke
 
     def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
         assert isinstance(parent, (Table, Column))
@@ -4519,14 +4526,7 @@ class ForeignKeyConstraint(ColumnCollectionConstraint):
         assert isinstance(table, Table)
         Constraint._set_parent(self, table)
 
-        try:
-            ColumnCollectionConstraint._set_parent(self, table)
-        except KeyError as ke:
-            raise exc.ArgumentError(
-                "Can't create ForeignKeyConstraint "
-                "on table '%s': no column "
-                "named '%s' is present." % (table.description, ke.args[0])
-            ) from ke
+        ColumnCollectionConstraint._set_parent(self, table)
 
         for col, fk in zip(self._columns, self.elements):
             if not hasattr(fk, "parent") or fk.parent is not col: