SQLAlchemy: Insert row eventhough foreign key does not exist in related table

Question

I have two tables securities and positions. The primary key in positions is linked via foreign key to securities.

Now, if I insert a new row into positions, it doens't matter if the security_id does exist in securities. I am wondering if this is how it should work? I had expected to get an error when trying to insert a row into positions that has a security_id, which doens't exist in securities.

from sqlalchemy import (
    Column,
    Float,
    ForeignKey,
    Integer,
    String,
    create_engine,
    select,
)
from sqlalchemy.orm import Session, declarative_base, relationship

Base = declarative_base()


class Security(Base):
    __tablename__ = "securities"

    id = Column(Integer, primary_key=True)
    isin = Column(String)
    name = Column(String)

    positions = relationship("Position", back_populates="security")


class Position(Base):
    __tablename__ = "positions"

    security_id = Column(Integer, ForeignKey("securities.id"), primary_key=True)
    weight = Column(Float)

    security = relationship("Security", back_populates="positions")

    def __repr__(self):
        return f"Position(security_id={self.security_id!r}, weight={self.weight!r})"


engine = create_engine("sqlite+pysqlite:///:memory:", echo=False, future=True,)
Base.metadata.create_all(engine)

session = Session(engine)

# insert some data
security1 = Security(isin="isin_1", name="securtiy_1")
security2 = Security(isin="isin_2", name="securtiy_2")

position1 = Position(security_id=1, weight=0.4)
position2 = Position(security_id=2, weight=0.6)

session.add_all([security1, security2, position1, position2])
session.commit()

# add a security, which doesn't exist yet
position3 = Position(security_id=3, weight=1)
session.add(position3)
session.commit()

result = session.execute(select(Position))
for row in result:
    print(row)

Output:

(Position(security_id=1, weight=0.4),)
(Position(security_id=2, weight=0.6),)
(Position(security_id=3, weight=1.0),) <-- This line shouldn't be allowed to be added in the first place