[Python] sqlalchemy does not enforce foreign key constraing [duplicate]

I am trying to play with the sql alchemy ORM based db definition. I have defined my tables as follows

class Customer(Base):
__tablename__ = 'customer'

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

auth = relationship("CustomerAuth", backref='customer')



class CustomerAuth(Base):
__tablename__ = 'authentication'

id = Column(Integer, ForeignKey('customer.id'))
username = Column(String(80), primary_key=True)
passwd = Column(String(80))


Now I am creating the session

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


And then I try to create two row objects for Customer with id 1 and 2

cst1 = sqla.Customer(id=1,name='shyam')
cst2 = sqla.Customer(id=2,name='ram')


And I create three row objects for CustomerAuth which reference to id 1, 2 and 3 of Customer

auth1 = sqla.CustomerAuth(id=1,username='shyamu',passwd='wam')
auth2 = sqla.CustomerAuth(id=2,username='ramu',passwd='dam')
auth3 = sqla.CustomerAuth(id=3,username='lamu',passwd='sam')


As you can see I have created a CustomerAuth row with id = 3 , which is a foreign key referring the Customer.id. But since Customer table has no entry with id=3 , this should fail

session.add(cst1)
session.add(cst2)
session.add(auth1)
session.add(auth2)
session.flush()


This operation should fail but it goes through successfully.

session.add(auth3)
session.flush()


I want to know what am I not doing which bypasses the foreign key enforcement

Thanks in advance

Continue reading...