sqlalchemy orm with complex foreign key construct

Question

i want to make my first python program which uses sqlalchemy orm model, but i´m stuck for days now, i hope you can help me out.

I have a table which has 4 keyfields, where col 1 is also foreign key to table "User" col 2 AND col 3 are foreign keys to table "Bill_Pos" col 4 is foreign key to table "Products"

so in col 1 there can only be values which occur in the table user. in col 4 of course only values which occur in table products.

but in col 2 and col 3 there should be only values which occur in the same combination in table bill pos.

example - the yellow value should not be allowed

foreign Key Constructs

I know what i want but i am not able with the help-files, many hours and days with google to solve my problem.

Here´s the code:

from sqlalchemy import Column, Integer, REAL, String, DateTime, Boolean
from sqlalchemy.orm import registry
from sqlalchemy.orm import relationship
from sqlalchemy import ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy import func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import select
from sqlalchemy.exc import OperationalError

import keyboard
from os import system

import cl_password_services

Base = declarative_base()



class Info(Base):
    __tablename__ = "info"
    # Meta
    id = Column(Integer, primary_key=True)     
    info = Column(String(25), nullable=False)  
    description = Column(String, nullable=False)
    # Relations
    user = relationship("User", back_populates="info")
    bill_hdr = relationship("Bill_HDR", back_populates="info")


class User(Base):
    __tablename__ = "user"
    # Meta
    id = Column(Integer, primary_key=True)
    name = Column(String(30), unique=True, index=True, nullable=False)
    password = Column(String(97))
    info_id = Column(ForeignKey("info.id"), nullable=False)
    # Relations
    info = relationship("Info", back_populates="user")
    add_data = relationship("Add_Data", back_populates="user")
    count = relationship("Count", back_populates="user")


class Bill_HDR(Base):
    __tablename__ = "bill_hdr"
    # Meta
    id = Column(Integer, primary_key=True)
    timestamp = Column(DateTime, unique=True, nullable=False, default=func.now())
    info_id = Column(ForeignKey("info.id"), nullable=False)  
    col4 = Column(Integer) 
    # Relations
    info = relationship("Info", back_populates="bill_hdr")
    bill_pos = relationship("Bill_Pos", back_populates="bill_hdr")


class Bill_Pos(Base):
    __tablename__ = "bill_pos"
    # Meta
    id = Column(ForeignKey("bill_hdr.id"), primary_key=True)
    pos = Column(Integer, primary_key=True)
    # Relations
    bill_hdr = relationship("Bill_HDR", back_populates="bill_pos")
    add_data = relationship("Add_Data", back_populates="bill_pos")


class Products(Base):
    __tablename__ = "products"
    # Meta
    id = Column(Integer, primary_key=True)
    prod = Column(Integer, nullable=False)
    pos = Column(Integer, nullable=False)
    # Relations
    add_data = relationship("Add_Data", back_populates="products")


class Add_Data(Base):
    __tablename__ = "add_data"
    # Meta
    user_id = Column(ForeignKey("user.id"), primary_key=True)
    bill_id = Column(ForeignKey("bill_pos.id"), primary_key=True)
    bill_pos = Column(ForeignKey("bill_pos.pos"), primary_key=True)
    prod_id = Column(ForeignKey("products.id"), primary_key=True)
    info1 = Column(Integer)
    info2 = Column(String)
    info3 = Column(Boolean)
    # Compound / Composite Foreign Keys
    __table_args__ = (ForeignKeyConstraint(["bill_id", "bill_pos"], ["bill_pos.id", "bill_pos.pos"]), {})    
    
    #__table_args__ = (ForeignKeyConstraint(["bill_id", "bill_pos.id"], ["bill_pos", "bill_pos.pos"]), {})    

    #__table_args__ = (ForeignKeyConstraint(["bill_id"], ["bill_pos.id"],
    #                                       ["bill_pos"], ["bill_pos.pos"]), {})

    #__table_args__ = (ForeignKeyConstraint(['user_id'], ['user.id'], ["bill_id"], ["bill_pos.id"], ["bill_pos"], ["bill_pos.pos"], ["prod_id"], ["products.id"]), )

    user = relationship("User", back_populates="add_data")

    products = relationship("Products", back_populates="add_data")




class Count(Base):
    __tablename__ = "count"
    # Meta
    user_id = Column(ForeignKey("user.id"), primary_key=True)
    prod = Column(ForeignKey("products.prod"), primary_key=True)
    count = Column(Integer, nullable=False)
    # Relations
    user = relationship("User", back_populates="count")
    products = relationship("Products", back_populates="count")



class DB:
    def __init__(self):

        self.engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
        #                             DB-Kind & DB-API    in RAM    Logging=On 2.0 Style enabled
        
        self.mapper_registry = registry()
        self.Session = sessionmaker(bind=self.engine, future=True)

    def create_tables_db(self):
        # Tabellen anlegen von ORM Klassenobjekten
        with self.engine.begin() as connection:
            Base.metadata.create_all(connection)

    def fill_some_data(self):
        this_works = True
        #try:
        #    if this_works:
        #        info_1 = Info(id=1, info="first", description="description first line")
        #        info_2 = Info(id=2, info="second", description="description 2nd line")
        #        info_3 = Info(id=3, info="third", description="description 3rd line")
        #        info_4 = Info(id=4, info="fourth", description="description 4th line")
        #        with self.Session() as session:
        #            session.add(info_1)
        #            session.add(info_2)
        #            session.add(info_3)
        #            session.add(info_4)
        #except Exception as e:
        #    print("oh no")
        if this_works:
            info_1 = Info(id=1, info="first", description="description first line")
            info_2 = Info(id=2, info="second", description="description 2nd line")
            info_3 = Info(id=3, info="third", description="description 3rd line")
            info_4 = Info(id=4, info="fourth", description="description 4th line")
            with self.Session() as session:
                session.add(info_1)
                session.add(info_2)
                session.add(info_3)
                session.add(info_4)

mydb = DB()
mydb.create_tables_db()
mydb.fill_some_data()
print("END")

I tried many different ways but now i´m so exhausted i feel like i need a hint. I hope here´s someone how understand the Problem and can help me out.

P.S.: the example behind is not part of the discussion, I need a technical fix for this problem. Of course you can question that as well, but anyways i´m very interested in a solution for the question above. Thanks a lot in advance for your time and hopefully for a solution.

Edit: it might not work without association table? This is pushing me to my limits :-) / :-(