CODEWITHSUNDEEP
postgresqlsqlalchemy
ashim
ashim

Reputation: 25560

sqlalchemy, violation foreign key constraint

I am new to sqlalchemy. I want to create a class which has two foreign key for different tables. Why I get next error?

sqlalchemy.exc.IntegrityError: (IntegrityError) insert or update on table "event" violates foreign key constraint "event_user_fkey"
DETAIL:  Key (user)=(U) is not present in table "user".
 'INSERT INTO event (id, "user", item) VALUES (%(id)s, %(user)s, %(item)s)' {'item': 'I', 'user': 'U', 'id': 'E'}

My code is next:

class User(Base):
    __tablename__ = 'user'
    id = Column(String, primary_key=True)

    def __init__(self, id):
        self.id = id

class Item(Base):
    __tablename__ = 'item'
    id = Column(String, primary_key=True)

    def __init__(self, id):
        self.id = id

class Event(Base):
    __tablename__ = 'event'
    id = Column(String, primary_key=True)
    user = Column(String, ForeignKey('user.id'))
    item = Column(String, ForeignKey('item.id'))

    def __init__(self, id, user_id, item_id):
        self.id = id
        self.user = user_id
        self.item = item_id

I use postgresql as a back end.

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
usr = User('U')
it = Item('I')
event = Event('E', usr.id, it.id)
session.add(usr)
session.add(it)
session.add(event)

Upvotes: 4

Views: 7261

Answers (1)

Doobeh
Doobeh

Reputation: 9440

The error seems pretty clear: Key (user)=(U) is not present in table "user".

So it's trying to insert the Event row before the User has been committed to the database, which breaks the ForeignKey constraint, causing this error. Try committing the User and Item to the database before committing the Event, which depends on them and the problem should evaporate.

Upvotes: 5

Related Questions