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('[e-mail]')).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':
'[e-mail]%'}

(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]

Post by MacVictor
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')
(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".
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.*

Post by MacVictor
*When I try used example query delete, always show error:*
')).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".
email.com%'}
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
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