ForeignKeyConstraint with duplicate source column references are not supported: Caused by MSSQL Dialect #12907
Description
Describe the bug
def get_foreign_keys(
self, connection, tablename, dbname, owner, schema, **kw
):
# Foreign key constraints
s = (
text(
"""\
WITH fk_info AS (
SELECT
ischema_ref_con.constraint_schema,
ischema_ref_con.constraint_name,
ischema_key_col.ordinal_position,
ischema_key_col.table_schema,
ischema_key_col.table_name,
ischema_ref_con.unique_constraint_schema,
ischema_ref_con.unique_constraint_name,
ischema_ref_con.match_option,
ischema_ref_con.update_rule,
ischema_ref_con.delete_rule,
ischema_key_col.column_name AS constrained_column
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ischema_ref_con
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE ischema_key_col ON
ischema_key_col.table_schema = ischema_ref_con.constraint_schema
AND ischema_key_col.constraint_name =
ischema_ref_con.constraint_name
WHERE ischema_key_col.table_name = :tablename
AND ischema_key_col.table_schema = :owner
),
constraint_info AS (
SELECT
ischema_key_col.constraint_schema,
ischema_key_col.constraint_name,
ischema_key_col.ordinal_position,
ischema_key_col.table_schema,
ischema_key_col.table_name,
ischema_key_col.column_name
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE ischema_key_col
),
index_info AS (
SELECT
sys.schemas.name AS index_schema,
sys.indexes.name AS index_name,
sys.index_columns.key_ordinal AS ordinal_position,
sys.schemas.name AS table_schema,
sys.objects.name AS table_name,
sys.columns.name AS column_name
FROM
sys.indexes
INNER JOIN
sys.objects ON
sys.objects.object_id = sys.indexes.object_id
INNER JOIN
sys.schemas ON
sys.schemas.schema_id = sys.objects.schema_id
INNER JOIN
sys.index_columns ON
sys.index_columns.object_id = sys.objects.object_id
AND sys.index_columns.index_id = sys.indexes.index_id
INNER JOIN
sys.columns ON
sys.columns.object_id = sys.indexes.object_id
AND sys.columns.column_id = sys.index_columns.column_id
)
SELECT
fk_info.constraint_schema,
fk_info.constraint_name,
fk_info.ordinal_position,
fk_info.constrained_column,
constraint_info.table_schema AS referred_table_schema,
constraint_info.table_name AS referred_table_name,
constraint_info.column_name AS referred_column,
fk_info.match_option,
fk_info.update_rule,
fk_info.delete_rule
FROM
fk_info INNER JOIN constraint_info ON
constraint_info.constraint_schema =
fk_info.unique_constraint_schema
AND constraint_info.constraint_name =
fk_info.unique_constraint_name
AND constraint_info.ordinal_position = fk_info.ordinal_position
UNION
SELECT
fk_info.constraint_schema,
fk_info.constraint_name,
fk_info.ordinal_position,
fk_info.constrained_column,
index_info.table_schema AS referred_table_schema,
index_info.table_name AS referred_table_name,
index_info.column_name AS referred_column,
fk_info.match_option,
fk_info.update_rule,
fk_info.delete_rule
FROM
fk_info INNER JOIN index_info ON
index_info.index_schema = fk_info.unique_constraint_schema
AND index_info.index_name = fk_info.unique_constraint_name
AND index_info.ordinal_position = fk_info.ordinal_position
ORDER BY fk_info.constraint_schema, fk_info.constraint_name,
fk_info.ordinal_position
"""
When I run above query (which is from the MSSQL dialect implementation), it returns 2 rows with the same source and referenced column name. This is caused by primary key column (referenced column) and the foreign key column (source column) having the same column name, with the same index name too (index_info.index_name = fk_info.unique_constraint_name).
Im pretty sure that we need to fix the query to handle this case. What's blocking me is that I only have readonly access to the db.
Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected
No response
SQLAlchemy Version in Use
2.0.42
DBAPI (i.e. the database driver)
pyodbc
Database Vendor and Major Version
Microsoft SQL Server Express 15.0.2145.1
Python Version
3.13.1
Operating system
Windows 11
To Reproduce
Create Table A (primary key AId with index named IX_A_AId) and Table B (foreign key column AId, with index named IX_A_AId and referencing the unique index of table A).
use sample;
IF OBJECT_ID('dbo.B', 'U') IS NOT NULL DROP TABLE dbo.B;
IF OBJECT_ID('dbo.A', 'U') IS NOT NULL DROP TABLE dbo.A;
CREATE TABLE dbo.A (
AId INT NOT NULL,
Name NVARCHAR(50),
CONSTRAINT PK_A PRIMARY KEY (AId)
);
-- Add an explicit unique index on AId
CREATE UNIQUE INDEX IX_A_AId ON dbo.A (AId);
-- Then reference that unique index in table B
CREATE TABLE dbo.B (
Id INT IDENTITY PRIMARY KEY,
AId INT,
CONSTRAINT FK_B_A FOREIGN KEY (AId)
REFERENCES dbo.A (AId)
);
CREATE INDEX IX_A_AId ON dbo.B(AId);
Then trigger get_foreign_keys method call for MSSQL Dialect (like getting reflection info). We can also verify manually by running below select query.
WITH fk_info AS (
SELECT
ischema_ref_con.constraint_schema,
ischema_ref_con.constraint_name,
ischema_key_col.ordinal_position,
ischema_key_col.table_schema,
ischema_key_col.table_name,
ischema_ref_con.unique_constraint_schema,
ischema_ref_con.unique_constraint_name,
ischema_ref_con.match_option,
ischema_ref_con.update_rule,
ischema_ref_con.delete_rule,
ischema_key_col.column_name AS constrained_column
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ischema_ref_con
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ischema_key_col ON
ischema_key_col.table_schema = ischema_ref_con.constraint_schema
AND ischema_key_col.constraint_name = ischema_ref_con.constraint_name
WHERE
ischema_key_col.table_name = 'B'
AND ischema_key_col.table_schema = 'dbo'
),
constraint_info AS (
SELECT
ischema_key_col.constraint_schema,
ischema_key_col.constraint_name,
ischema_key_col.ordinal_position,
ischema_key_col.table_schema,
ischema_key_col.table_name,
ischema_key_col.column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ischema_key_col
),
index_info AS (
SELECT
sys.schemas.name AS index_schema,
sys.indexes.name AS index_name,
sys.index_columns.key_ordinal AS ordinal_position,
sys.schemas.name AS table_schema,
sys.objects.name AS table_name,
sys.columns.name AS column_name
FROM
sys.indexes
INNER JOIN sys.objects ON sys.objects.object_id = sys.indexes.object_id
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id
INNER JOIN sys.index_columns ON
sys.index_columns.object_id = sys.objects.object_id
AND sys.index_columns.index_id = sys.indexes.index_id
INNER JOIN sys.columns ON
sys.columns.object_id = sys.indexes.object_id
AND sys.columns.column_id = sys.index_columns.column_id
)
SELECT
fk_info.constraint_schema,
fk_info.constraint_name,
fk_info.ordinal_position,
fk_info.constrained_column,
index_info.table_schema AS referred_table_schema,
index_info.table_name AS referred_table_name,
index_info.column_name AS referred_column,
fk_info.match_option,
fk_info.update_rule,
fk_info.delete_rule,
fk_info.unique_constraint_name
FROM
fk_info
INNER JOIN index_info ON
index_info.index_schema = fk_info.unique_constraint_schema
AND index_info.index_name = fk_info.unique_constraint_name
AND index_info.ordinal_position = fk_info.ordinal_position
ORDER BY
fk_info.constraint_schema, fk_info.constraint_name, fk_info.ordinal_position;
Error
Traceback (most recent call last):
File "c:\Users\johan\source\StartUp\Playground\InitDb\main.py", line 83, in <module>
init_db()
~~~~~~~^^
File "c:\Users\johan\source\StartUp\Playground\InitDb\main.py", line 64, in init_db
db = SQLDatabase(engine)
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\langchain_community\utilities\sql_database.py", line 159, in __init__
self._metadata.reflect(
~~~~~~~~~~~~~~~~~~~~~~^
views=view_support,
^^^^^^^^^^^^^^^^^^^
...<2 lines>...
schema=self._schema,
^^^^^^^^^^^^^^^^^^^^
)
^
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\sqlalchemy\sql\schema.py", line 5899, in
reflect
Table(name, self, **reflect_opts)
~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "<string>", line 2, in __new__
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\sqlalchemy\util\deprecations.py", line 281, in warned
return fn(*args, **kwargs) # type: ignore[no-any-return]
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\sqlalchemy\sql\schema.py", line 429, in __new__
return cls._new(*args, **kw)
~~~~~~~~^^^^^^^^^^^^^
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\sqlalchemy\sql\schema.py", line 483, in _new
with util.safe_reraise():
~~~~~~~~~~~~~~~~~^^
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 224, in __exit__
raise exc_value.with_traceback(exc_tb)
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\sqlalchemy\sql\schema.py", line 479, in _new
table.__init__(name, metadata, *args, _no_init=False, **kw) # type: ignore[misc] # noqa: E501
~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\sqlalchemy\sql\schema.py", line 861, in __init__
self._autoload(
~~~~~~~~~~~~~~^
metadata,
^^^^^^^^^
...<4 lines>...
resolve_fks=resolve_fks,
^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\sqlalchemy\sql\schema.py", line 893, in _autoload
conn_insp.reflect_table(
~~~~~~~~~~~~~~~~~~~~~~~^
self,
^^^^^
...<4 lines>...
_reflect_info=_reflect_info,
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\sqlalchemy\engine\reflection.py", line 1580, in reflect_table
self._reflect_fk(
~~~~~~~~~~~~~~~~^
_reflect_info,
^^^^^^^^^^^^^^
...<7 lines>...
reflection_options,
^^^^^^^^^^^^^^^^^^^
)
^
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\sqlalchemy\engine\reflection.py", line 1799, in _reflect_fk
sa_schema.ForeignKeyConstraint(
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
constrained_columns,
^^^^^^^^^^^^^^^^^^^^
...<4 lines>...
**options,
^^^^^^^^^^
)
^
File "C:\Users\johan\source\StartUp\Playground\InitDb\venv\Lib\site-packages\sqlalchemy\sql\schema.py", line 4717, in
__init__
raise exc.ArgumentError(
...<2 lines>...
)
sqlalchemy.exc.ArgumentError: ForeignKeyConstraint with duplicate source column references are not supported.
Additional context
No response