How to set a foreign key constraint using SQLAlchemy?

Question

I have the following Python code where I'm using the SQLAlchemy library. In the code, I added a record to the 'attendance' table where the foreign key doesn't reference any valid record in the 'employee' table, yet no error is thrown when I execute it. Can someone explain why this is happening?

from datetime import datetime, time
from sqlalchemy import Column, ForeignKey, create_engine, DateTime, Integer, String, Time
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()

class Employee(Base):

    __tablename__ = 'employee'

    id_employee = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name_1 = Column(String)
    last_name_2 = Column(String)

    def __init__(self, id_employee, first_name, last_name_1, last_name_2):

        self.id_employee = id_employee
        self.first_name = first_name
        self.last_name_1 = last_name_1
        self.last_name_2 = last_name_2

    def __repr__(self):

        return f'Employee({self.id_employee}, {self.first_name}, {self.last_name_1}, {self.last_name_2})'
    
class Attendance(Base):

    __tablename__ = 'attendance'

    date = Column(DateTime, primary_key=True)
    id_employee = Column(Integer, ForeignKey('employee.id_employee'), primary_key=True)
    start_time = Column(Time)
    end_time = Column(Time)

    def __init__(self, date, id_employee, start_time, end_time):

        self.date = date
        self.id_employee = id_employee
        self.start_time = start_time
        self.end_time = end_time

    def __repr__(self):

        return f'Attendance({self.date}, {self.id_employee}, {self.start_time}, {self.end_time})'
    
engine = create_engine('sqlite:///database.db', echo=True)
Base.metadata.create_all(bind=engine)

Session = sessionmaker(bind=engine)
session = Session()

emp_1 = Employee(1, 'WALTER', 'WHITE', 'WHITE')
session.add(emp_1)

att_1 = Attendance(datetime(2023, 4, 22), 10, time(10, 0), time(18, 0))
session.add(att_1)

session.commit()
session.close()

The only thing I expect is to not be able to add records when the foreign key is not a valid employee_id.

How to set a foreign key constraint using SQLAlchemy?

0 Answers0