61

I have this simple model of Author - Books and can't find a way to make firstName and lastName a composite key and use it in relation. Any ideas?

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

Base = declarative_base()
engine = create_engine('mssql://user:pass@library')
engine.echo = True
session = sessionmaker(engine)()

class Author(Base):
    __tablename__ = 'authors'
    firstName = Column(String(20), primary_key=True)
    lastName = Column(String(20), primary_key=True)
    books = relationship('Book', backref='author')

class Book(Base):
    __tablename__ = 'books'
    title = Column(String(20), primary_key=True)
    author_firstName = Column(String(20), ForeignKey('authors.firstName'))
    author_lastName = Column(String(20), ForeignKey('authors.lastName'))
tshepang
  • 12,111
  • 21
  • 91
  • 136
mdob
  • 2,224
  • 3
  • 22
  • 25

1 Answers1

107

The problem is that you have defined each of the dependent columns as foreign keys separately, when that's not really what you intend, you of course want a composite foreign key. Sqlalchemy is responding to this by saying (in a not very clear way), that it cannot guess which foreign key to use (firstName or lastName).

The solution, declaring a composite foreign key, is a tad clunky in declarative, but still fairly obvious:

class Book(Base):
    __tablename__ = 'books'
    title = Column(String(20), primary_key=True)
    author_firstName = Column(String(20))
    author_lastName = Column(String(20))
    __table_args__ = (ForeignKeyConstraint([author_firstName, author_lastName],
                                           [Author.firstName, Author.lastName]),
                      {})

The important thing here is that the ForeignKey definitions are gone from the individual columns, and a ForeignKeyConstraint is added to a __table_args__ class variable. With this, the relationship defined on Author.books works just right.

SingleNegationElimination
  • 151,563
  • 33
  • 264
  • 304
  • 12
    The [docs](http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=check#metadata-foreignkeys) include additional clarification and examples: It’s important to note that the `ForeignKeyConstraint` is the only way to define a composite foreign key. While we could also have placed individual `ForeignKey` objects on both [...] columns, SQLAlchemy would not be aware that these two values should be paired together - it would be two individual foreign key constraints instead of a single composite foreign key referencing two columns. – iled Jan 20 '16 at 16:46