CODEWITHSUNDEEP
pythonsqlalchemy
justjake415
justjake415

Reputation: 11

SQLAlchemy table reflection - remove duplicate source column references

I am trying to reflect a table using sqlalchemy by executing:

PilotCycle = Table('PilotCycle', pr_meta, schema='dbo', autoload=True, autoload_with=pr_engine)

However, I receive an error: "ArgumentError: ForeignKeyConstraint with duplicate source column references are not supported."

I think I figured out what's causing this by inspecting the table's foreign keys.

from sqlalchemy import inspect
insp = inspect(pr_engine)
fks = insp.get_foreign_keys('PilotCycle')
print(fks)

This returns a list of dicts with each element being a foreign key constraint. I found one foreign key, 'FK_PilotCycle_Equipment', that has duplicate constrained columns (EquipmentID) and duplicate referred columns (ID). Here is the dict in the list that I am referring to:

{
    'name': 'FK_PilotCycle_Equipment', 
    'constrained_columns': ['EquipmentID', 'EquipmentID'],
    'referred_schema': None,
    'referred_table': 'Equipment',
    'referred_columns': ['ID', 'ID']
}

I have read only access to the database. How can I fix this?

Upvotes: 1

Views: 1724

Answers (1)

Sean
Sean

Reputation: 11

I just spent a day wrangling the same issue and have at least traced its source, if not solved it.

This error is caused by two identically-named PRIMARY KEYS in the same database -- even if the Foreign Keys are in schemas and tables that have nothing to do with each other.

The root issue is the way in which the db inspection query is written in dialects/mssql/base.py:

```SELECT [C].[COLUMN_NAME], 
[R].[TABLE_SCHEMA], 
[R].[TABLE_NAME], 
[R].[COLUMN_NAME], 
[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[CONSTRAINT_NAME], 
[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[MATCH_OPTION], 
[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[UPDATE_RULE], 
[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[DELETE_RULE] 
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [C], [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [R], [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] 
WHERE [C].[TABLE_NAME] = CAST('MYTABLE' AS NVARCHAR(max)) AND [C].[TABLE_SCHEMA] = CAST('MYSCHEMA' AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[CONSTRAINT_SCHEMA] = [C].[TABLE_SCHEMA] AND [C].[CONSTRAINT_NAME] = [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[CONSTRAINT_NAME] AND [R].[CONSTRAINT_NAME] = [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[UNIQUE_CONSTRAINT_NAME] AND [C].[ORDINAL_POSITION] = [R].[ORDINAL_POSITION] ORDER BY [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[CONSTRAINT_NAME], [R].[ORDINAL_POSITION]``

If you replace MYTABLE and MYSCHEMA with the offending table in your DB, you should see a row come back that has the same constraint name but comes from a completely different table.

The following query will find all colliding Primary Key names in a DB:

    select CONSTRAINT_NAME, count(*)
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'PRIMARY KEY'
group by CONSTRAINT_NAME
having count(*) > 1

Unfortunately, if you don't have access to change the DB DDL, I see no easy solution for this.

I've submitted both an issue and a PR with a fix to the SQLAlchemy bug:

If you're in a bind, you can probably take my PR and monkey-patch SQLAlchemy, although I haven't tried this myself.

Upvotes: 1

Related Questions