can Foreign Key connect to multiple tables?

Question

i have sqlalchemy models like this :

class Person(db.Model):
   ..
   # the photos of the person
   photos = db.relationship('Photos', lazy='dynamic')

class Photos(db.Model):
   ..
   #link to the photo
   link = db.Column(db.TEXT)

of course this raise error that i must include Foreign Key in Photos table connect to Person.

the problem is i want Photos table to host photos of other objects like Cars and stuff like that .

how to make Photos table connect to Options of tables. Note that Photos must connect to only one table if Person no other table will connect to it .

i hope this make sense

score 0 · Accepted Answer · answered Jun 23 '20 at 13:23

The strictly relational database answer to that question is here, already on StackOverflow. Strictly speaking, it is not considered "correct" design to have a single foreign key that could point to multiple tables and it typically isn't directly supported by the database itself.

Having said that, it's still a somewhat common pattern and for your question specifically, you might look into a library called SQLAlchemy-Utils, which includes a feature called Generic Relationships. Consider this example, from the docs:

from sqlalchemy_utils import generic_relationship

class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)

class Customer(Base):
    __tablename__ = 'customer'
    id = sa.Column(sa.Integer, primary_key=True)

class Event(Base):
    __tablename__ = 'event'
    id = sa.Column(sa.Integer, primary_key=True)

    # This is used to discriminate between the linked tables.
    object_type = sa.Column(sa.Unicode(255))

    # This is used to point to the primary key of the linked row.
    object_id = sa.Column(sa.Integer)

    object = generic_relationship(object_type, object_id)


# Some general usage to attach an event to a user.
user = User()
customer = Customer()

session.add_all([user, customer])
session.commit()

ev = Event()
ev.object = user

session.add(ev)
session.commit()

# Find the event we just made.
session.query(Event).filter_by(object=user).first()

# Find any events that are bound to users.
session.query(Event).filter(Event.object.is_type(User)).all()

The event.object attribute points to either a User or Customer object (or other object). The library "finds" the other object by combining the object_type column, to tell it what other table to query, and the object_id column.

There are some downsides to this approach, especially in terms of querying efficiency and indexing, but also in whether the design "feels right." Read up on that answer I linked to above for a discussion of that.

can Foreign Key connect to multiple tables?

1 Answers1