Sqlalchemy union foreign key

salius 0
Viewed 25

I want to make table field (foreign key) that can reference to one of 2 tables: Dog or Cat.

I know it may be most likely implemented in PostgreSQL (via check) keyword (not a part of SQL standard), not a foreign key). So I suppose it may be possible at other databases also via their private syntax. Theoretically it may be done by Sqlalchemy even if some particular database not supporting a such functionality.

in Python code it looks like this:

# # # SCHEMAS.PY # # #
from dataclasses import dataclass


@dataclass
class Cat: ...


@dataclass
class Dog: ...


@dataclass
class review:
    stars_value: int
    entity: Cat | Dog
    comment: str

# # # MODELS.PY # # #
from sqlalchemy import Column, Table, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


cats = Table(
    "cats",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String)
    )

dogs = Table(
    "dogs",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String),
    )

review = Table(
    "review",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("comment", String),
    Column("entity", Integer, ForeignKey("cats.id" | "dogs.id")  # Invalid syntax here!
    )

If no - I will do it via some intermediate table as:

review_categories = Table(
    "review_categories",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("cat", Integer),
    Column("dog Integer),
)
sqlalchemy
1 Answers

You could create a common table for cats and dogs such that the id for any cat and dog is unique, and keep an additional parameter as type (dog or cat).

id (pk) name type
1 A Dog
2 B Cat
3 B Dog

For a general solution where you may want a union of columns as a foreign key.

One interesting way to approach this might be to create a new table containing two columns. The first column will be unique names and the other column will be the frequency of these names (ie, how many times this keyword appears in cats and dogs combined).

Unique_id (pk) Count
A 1
B 2

Such that

Unique_id = Dogs.id ∪ Cats.id

Logic to update the table

  1. Insert in Cats - Insert or Increment Count of Unique_id
  2. Deletion in Cats - Decrement Count of Unique_id or Delete If reaches 0

Then you can use the unique_id column for your foreign key

Krishanu0
Related