sql server self-referential FK repeated in multple schemas doubles columns up

from sqlalchemy import *

e = create_engine("mssql+pymssql://scott:tiger^5HHH@mssql2017:1433/test", echo=True)

metadata = MetaData()

users = Table(
    'users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('parent_user_id', Integer,
         ForeignKey('users.user_id',
                       name='user_id_fk')),
)

users = Table(
    'users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('parent_user_id', Integer,
         ForeignKey('test_schema.users.user_id',
                       name='user_id_fk')),
    schema="test_schema",
)

metadata.create_all(e)

insp = inspect(e)

print insp.get_foreign_keys("users", schema="test_schema")

output:

[{'referred_table': u'users', 'referred_columns': [u'user_id', u'user_id'], 'referred_schema': u'scott', 'name': u'user_id_fk', 'constrained_columns': [u'parent_user_id', u'parent_user_id']}]

this has to do with the join of key_constraints to itself, needs to include table_schema.

Comments (1)