DELETE - (IntegrityError) update or delete on table "" violates foreign key constraint ""

[MacVictor]

This is my testing model:

# -*- coding: utf-8 -*-

from sqlalchemy import Column, Table, Sequence, Integer, String, Boolean, Numeric, ForeignKey, MetaData, DateTime, Text

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import relationship, backref, validates

Base = declarative_base()

class Addressess(Base):

    __tablename__ = 'addressess'

    id = Column('id', Integer, Sequence('address_id_seq'), primary_key=True, index=True, unique=True, nullable=False)

    street = Column('street', String(200))

    house_no = Column('house_no', Integer())

    place = Column('place', String(200))

    postal_code = Column('postal_code', String(200))

    post = Column('post', String(200))

    def __init__(self, street, house_no):

        self.street = street

        self.house_no = house_no

    def __repr__(self):

       return u'%s, %s, %s' % (self.place, self.street, self.house_no)

class Price(Base):

    __tablename__ = 'prices'

    id = Column('id', Integer, Sequence('price_id_seq'), primary_key=True, index=True, unique=True, nullable=False)

    price = Column('price', String(200))

    extra = Column('extra', String(200), nullable=True)

    def __init__(self, price, extra=None):

        self.price = price

        self.extra = extra

    def __repr__(self):

       return u'%s (%s)' % (self.price, self.extra)

class Person(Base):

    __tablename__ = 'person'

    id = Column('id', Integer, Sequence('person_id_seq'), primary_key=True, index=True, unique=True, nullable=False)

    name = Column('name', String(200))

    surname = Column('surname', String(200))

    gender = Column('gender', Boolean, unique=False)

    pesel = Column('pesel', Numeric(11, 0))

    phone = Column('phone', Numeric(9, 0))

    email = Column('email', String(200))

    no_card = Column('no_card', String(100))

    status = Column('status', Integer)

    student = Column('student', Boolean, unique=False)

    group_leader_id = Column(Integer, ForeignKey('person.id'), nullable=True)

    group_leader = relationship("Person", remote_side=[id], primaryjoin="Person.id==Person.group_leader_id")

    addressess_id = Column(Integer, ForeignKey('addressess.id'), nullable=True)

    addressess = relationship("Addressess", backref=backref("person", uselist=False, cascade="all, delete-orphan"))

    price_id = Column(Integer, ForeignKey('prices.id'), nullable=True)

    price = relationship("Price", backref=backref("person", uselist=False, cascade="all, delete-orphan"))

    @validates('email')

    def validate_email(self, key, address):

        assert '@' in address

        return address

    def __init__(self, name, surname):

        self.name = name

        self.surname = surname

    def __repr__(self):

        return u'%s %s, %s' % (self.name, self.surname, self.pesel)

class Group(Base):

    __tablename__ = 'group'

    id = Column('id', Integer, Sequence('person_id_seq'), primary_key=True, index=True, unique=True, nullable=False)

    name = Column('email', String(200))

    leader_id = Column(Integer, ForeignKey('person.id'))

    leader = relationship("Person")

    addressess_id = Column(Integer, ForeignKey('addressess.id'))

    addressess = relationship("Addressess")

    def __init__(self, name):

        self.name = name

    def __repr__(self):

        return u'%s' % self.name

association_table = Table('association', Base.metadata,

    Column('person_id', Integer, ForeignKey('person.id')),

    Column('projects_id', Integer, ForeignKey('projects.id'))

)

class Projects(Base):

    __tablename__ = 'projects'

    id = Column('id', Integer, Sequence('person_id_seq'), primary_key=True, index=True, unique=True, nullable=False)

    name = Column('name', String(200))

    subject = Column('subject', String(200))

    supervisor_id = Column(Integer, ForeignKey('person.id'))

    supervisor = relationship("Person")

    students = relationship("Person", secondary=association_table, backref="projects")

    def __init__(self, name, subject):

        self.name = name

        self.subject = subject

    def __repr__(self):

        return u'%s' % self.subject

When I try used example query delete, always show error:

session.query(Person).filter(Person.email.like('%@email.com')).delete(synchronize_session='fetch')

subq = session.query(Person.id).filter(Person.name.like('%P%')).subquery()

session.query(Person).filter(Person.group_leader_id.in_(subq)).delete(synchronize_session='fetch')

subq = session.query(Addressess.id).filter(Addressess.street.like('%Jana%')).subquery()

session.query(Projects).filter(Projects.students.any(Person.addressess_id.in_(subq))).delete(synchronize_session='fetch')

subq = session.query(Addressess.id).filter(Addressess.postal_code.like('%01%')).subquery()

session.query(Person).filter(Person.addressess_id.in_(subq)).delete(synchronize_session='fetch')

session.query(Person).delete(synchronize_session='fetch')

this error:

(IntegrityError) update or delete on table "person" violates foreign key constraint "association_person_id_fkey" on table "association"

DETAIL:  Key (id)=(2990) is still referenced from table "association".

 'DELETE FROM person WHERE person.email LIKE %(email_1)s' {'email_1': '%@email.com%'}

(IntegrityError) update or delete on table "person" violates foreign key constraint "association_person_id_fkey" on table "association"

DETAIL:  Key (id)=(1581) is still referenced from table "association".

 'DELETE FROM person WHERE person.group_leader_id IN (SELECT person.id \nFROM person \nWHERE person.name LIKE %(name_1)s)' {'name_1': '%P%'}

(IntegrityError) update or delete on table "projects" violates foreign key constraint "association_projects_id_fkey" on table "association"

DETAIL:  Key (id)=(10941) is still referenced from table "association".

 'DELETE FROM projects WHERE EXISTS (SELECT 1 \nFROM association, person \nWHERE projects.id = association.projects_id AND person.id = association.person_id AND person.addressess_id IN (SELECT addressess.id \nFROM addressess \nWHERE addressess.street LIKE %(street_1)s))' {'street_1': '%Jana%'}

(IntegrityError) update or delete on table "person" violates foreign key constraint "person_group_leader_id_fkey" on table "person"

DETAIL:  Key (id)=(879) is still referenced from table "person".

 'DELETE FROM person WHERE person.addressess_id IN (SELECT addressess.id \nFROM addressess \nWHERE addressess.postal_code LIKE %(postal_code_1)s)' {'postal_code_1': '%01%'}

(IntegrityError) update or delete on table "person" violates foreign key constraint "association_person_id_fkey" on table "association"

DETAIL:  Key (id)=(845) is still referenced from table "association".

 'DELETE FROM person' {}

I read docs: http://docs.sqlalchemy.org/en/latest/orm/session.html#cascades but I do not understand how to set cascade deleting.

I using self ForeignKey and Many2Many filter.. how to set these fields?

[Michael Bayer]

On Jul 6, 2013, at 4:39 AM, MacVictor <witol...@gmail.com> wrote:

When I try used example query delete, always show error:

session.query(Person).filter(Person.email.like('%@email.com')).delete(synchronize_session='fetch')

subq = session.query(Person.id).filter(Person.name.like('%P%')).subquery()

session.query(Person).filter(Person.group_leader_id.in_(subq)).delete(synchronize_session='fetch')

subq = session.query(Addressess.id).filter(Addressess.street.like('%Jana%')).subquery()

session.query(Projects).filter(Projects.students.any(Person.addressess_id.in_(subq))).delete(synchronize_session='fetch')

subq = session.query(Addressess.id).filter(Addressess.postal_code.like('%01%')).subquery()

session.query(Person).filter(Person.addressess_id.in_(subq)).delete(synchronize_session='fetch')

session.query(Person).delete(synchronize_session='fetch')

this error:

(IntegrityError) update or delete on table "person" violates foreign key constraint "association_person_id_fkey" on table "association"

DETAIL:  Key (id)=(2990) is still referenced from table "association".

 'DELETE FROM person WHERE person.email LIKE %(email_1)s' {'email_1': '%@email.com%'}

I read docs: http://docs.sqlalchemy.org/en/latest/orm/session.html#cascades but I do not understand how to set cascade deleting.

I using self ForeignKey and Many2Many filter.. how to set these fields?

SQLAlchemy's ORM-level "cascade" that you set on relationship() doesn't apply to using query().delete().  query().delete() just emits a DELETE statement to the database directly without taking into account what is dependent on it and such.   You can get the database itself to take care of dependent rows if you configure your actual schema with appropriate "ON DELETE CASCADE" instructions (read about it here: http://www.postgresql.org/docs/8.2/static/ddl-constraints.html or google it).     You can configure that from ForeignKey() using "ondelete", see http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html#on-update-and-on-delete

[MacVictor]

Thank you Michael, now I understand :)

My model must contains this parametr:

class Person(Base):

    ...

    group_leader_id = Column(Integer, ForeignKey('person.id', onupdate="CASCADE", ondelete="CASCADE"), nullable=True)

    group_leader = relationship("Person", remote_side=[id], primaryjoin="Person.id==Person.group_leader_id")

    addressess_id = Column(Integer, ForeignKey('addressess.id', onupdate="CASCADE", ondelete="CASCADE"), nullable=True)

    addressess = relationship("Addressess", backref=backref("person", uselist=False))

    price_id = Column(Integer, ForeignKey('prices.id', onupdate="CASCADE", ondelete="CASCADE"), nullable=True)

    price = relationship("Price", backref=backref("person", uselist=False))

class Group(Base):

    ...    

    leader_id = Column(Integer, ForeignKey('person.id', onupdate="SET NULL", ondelete="SET NULL"))

    leader = relationship("Person")

    addressess_id = Column(Integer, ForeignKey('addressess.id', onupdate="SET NULL", ondelete="SET NULL"))

    addressess = relationship("Addressess")

association_table = Table('association', Base.metadata,

    Column('person_id', Integer, ForeignKey('person.id', onupdate="CASCADE", ondelete="CASCADE")),

    Column('projects_id', Integer, ForeignKey('projects.id', onupdate="CASCADE", ondelete="CASCADE"))

)

class Projects(Base):

    ...

    supervisor_id = Column(Integer, ForeignKey('person.id', onupdate="CASCADE", ondelete="CASCADE"))

But what I get in exchange for setting cascade? (http://docs.sqlalchemy.org/en/latest/orm/session.html#cascades)

class Order(Base):

    __tablename__ = 'order'

    items = relationship("Item", cascade="all, delete-orphan")

I do not understand the documentation.