How to implement conditional foreign key relationships?

[Christoph Zwerschke]

I'm trying to use SQLAlchemy with a silly legacy database the design of
which I cannot change. In this database, there are parent tables with
foreign key columns which can point to two different tables, depending
on another column in the parent table.

Think for example of a user table with a column "country_id" which can
either mean a US country id like IL=Illinois or an international country
id, like NL=Netherlands, depending on whether another column "native" is
set to true (native user) or not (foreigner). The US countries and
international countries are stored in different tables.

Of course this foreign key relationship is not (and probably cannot) be
enforced by SQL foreign key constraints in the database (it does not use
SQL based constraints at all). But would it be possible to create such a
"conditional" relationship configuration using the SQLAlchemy
relationship API?

I solved the problem by using a manually written property, but maybe I'm
overlooking something?

-- Christoph

[Michael Bayer]

On Aug 31, 2012, at 9:32 AM, Christoph Zwerschke wrote:

> I'm trying to use SQLAlchemy with a silly legacy database the design of which I cannot change. In this database, there are parent tables with foreign key columns which can point to two different tables, depending on another column in the parent table.

right, this is a really common (and IMHO incorrect) design that is referred to as a "generic foreign key" or a "polymorphic foreign key". It was popularized by frameworks such as Rails and Django. We have a recipe that implements this exact schema with SQLAlchemy which is introduced here: http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/ . This is a very old blog post and the pure "active record" version is this: http://techspot.zzzeek.org/files/2007/poly_assoc_1.py .

the basic idea is to create multiple relationship()s corresponding to each foreign key, then use a @property to figure out which one is non-None and return that upon access.

> I solved the problem by using a manually written property, but maybe I'm overlooking something?

seems like you came up with it on your own....

[Christoph Zwerschke]

Am 31.08.2012 16:07, schrieb Michael Bayer:
> right, this is a really common (and IMHO incorrect) design that is
> referred to as a "generic foreign key" or a "polymorphic foreign
> key". It was popularized by frameworks such as Rails and Django. We
> have a recipe that implements this exact schema with SQLAlchemy which
> is introduced here:
http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/

Thanks for the quick reply, will have a look at this.

Your blog is a real treasure trove :)

-- Christoph

[Christoph Zwerschke]

Thanks again for the link to the "Polymorphic Associations with
SQLAlchemy" article. You're right, that's the same problem I had been
facing. Your generic solution is not applicable for me, because I can't
change the database design, but the elementary solution is good enough.

For the curious, here is my application to the example I had posted:


from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Boolean, String

engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

class CountryNat(Base):
__tablename__ = 'country_nat'
country_id = Column(String(2), primary_key=True)
name = Column(String(40))

def __init__(self, country_id, name):
self.country_id = country_id
self.name = name

class CountryInt(Base):
__tablename__ = 'country_int'
country_id = Column(String(2), primary_key=True)
name = Column(String(40))

def __init__(self, country_id, name):
self.country_id = country_id
self.name = name

class Contact(Base):
__tablename__ = 'contact'
name = Column(String(80), primary_key=True)
native = Column(Boolean())
country_id = Column(String(2))

country_nat = relationship(CountryNat,
primaryjoin=country_id==CountryNat.country_id,
foreign_keys=[country_id])
country_int = relationship(CountryInt,
primaryjoin=country_id==CountryInt.country_id,
foreign_keys=[country_id])
country = property(lambda self: getattr(self,
'country_%s' % ('nat' if self.native else 'int')))

def __init__(self, name, native, country_id):
self.name = name
self.native = native
self.country_id = country_id

Base.metadata.create_all(engine)

if not session.query(Contact).first():
session.add_all([
CountryNat('IL', 'Illinois'),
CountryNat('NY', 'New York'),
CountryInt('KZ', 'Kazakhstan'),
CountryInt('NL', 'Netherlands'),
Contact('Flozell Adams', True, 'IL'),
Contact('Vince Lombardi', True, 'NY'),
Contact('Borat Sagdiyev', False, 'KZ'),
Contact('Ryan Donk', False, 'NL')])
session.commit()

for contact in session.query(Contact):
print "%s, %s" % (contact.name, contact.country.name)