FK Reflection in MSSQL incorrectly identifies duplicate source columns for identically-named PKs in different schemas

[sqlalchemy-bot]

Migrated issue, originally created by Sean Dunn (@seanfdnn)

For MSSQL:

• When reflecting a database (such as using autoload for a table) an exception can be thrown ArgumentError: ForeignKeyConstraint with duplicate source column references are not supported. Even when there are no duplicate source column references in the database.

This is caused by two tables in two schemas with the same Primary Key names -- SqlAlchemy confuses these two Primary Keys as being the referred column for a Foreign Key, when only one of them is. This is because SqlAlchemy isn't also checking the constraint schema.

To reproduce:

• Create two schemas FOO and BAR
• Create three tables, FOO.FOOTABLE, FOO.BAZTABLE, BAR.BARTABLE
• Create a PRIMARY KEY on FOO.BAZTABLE and BAR.BARTABLE with the same PK names (i.e. `CONSTRAINT [MY_PK] PRIMARY KEY CLUSTERED ([MY_PK_COL])
• Create a FK in FOO.FOOTABLE that references FOO.BAZTABLE.MY_PK_COL
• reflect FOO.FOOTABLE via footable = Table('FOOTABLE', fooschemameta, autoload=True, autoload_with=myengine)

Here's one example of someone else who has had the same problem:
https://stackoverflow.com/questions/47205433/sqlalchemy-table-reflection-remove-duplicate-source-column-references/51024467#51024467

The root issue is in dialects/mssql/base.py in def_foreign_keys -- the query that checks the foreign key constraints does not have a WHERE clause that matches the R.c.constraint_schema to RR.c.unique_constraint_schema. -- so it's not uniquely identifying the PK (PK's can have identical names in different schemas, so we also need to check the schema)

[sqlalchemy-bot]

Sean Dunn (@seanfdnn) wrote:

A PR has been submitted with a fix for this issue

zzzeek/sqlalchemy#457

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

the PR needs a test case.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "1.2.x"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Correct the bug number for 🎫4288, which was erroneously
given as 🎫4228.

Change-Id: I6525560c1bcf3f3d861d6254723f5facdba6adae
Fixes: #4288

→ 2c44fc2

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Correct the bug number for 🎫4288, which was erroneously
given as 🎫4228.

Change-Id: I6525560c1bcf3f3d861d6254723f5facdba6adae
Fixes: #4288
(cherry picked from commit 2c44fc2)

→ 647ff07