SQLAlchemy: foreign key to multiple tables

Question

Let's consider 3 tables:

• books
• American authors
• British authors

Each book has a foreign key to its author, which can either be in the American table, or the British one.

How can I implement such foreign key condition in SQLAlchemy?

I'd like to have a single column to handle the link.

---

My approach so far was to create an abstract class Author, from which both AmericanAuthor and BritishAuthor inherit, and have the foreign key of Book point to the parent.

class Author(Model):
    __abstract__ = True
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

class AmericanAuthor(Author):
    __tablename__ = 'american_author'
    # some other stuff

class BritishAuthor(Author):
    __tablename__ = 'british_author'
    # some other stuff

class Book(Model):
    __tablename__ = 'book'
    title = db.Column(db.String)
    author_id = db.Column(db.Integer, db.ForeignKey("author.id"))

It fails with the error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'books.author_id' could not find table 'author' with which to generate a foreign key to target column 'id'

Which completely makes sense, considering author is abstract...

Answer 1

I think you can't build a relationship with two different tables using the same column.

Try creating two different columns ("american_author_id" and "british_author_id") then make a @property "author" that returns the author that isn't NULL.

This way you can get the author using: mybook.author

Answer 2

Although the @property decorator will work in the application it might be better to use the @hybrid_property from the sqlalchemy.ext.hybrid package. In that way you will be able to filter on that property just like any normal attribute.

Your Book class would then look like:

class Book(Model):
    __tablename__ = 'book'
    title = db.Column(db.String)
    american_author_id = db.Column(db.Integer, db.ForeignKey("american_author.id"), nullable=True)
    british_author_id = db.Column(db.Integer, db.ForeignKey("british_author.id"), nullable=True)

    @hybrid_property
    def author_id(self):
        return self.american_author_id or self.british_author_id