SA skips integrity referential?
Jose Soares
I wonder how SA could delete a row of my table (postgresql) linked with
another table.
Take a look...
pg=> select * from attivita where cod_specie='33';
codice | descrizione | cod_specie
--------+-----------------------------------------------------+------------
21311 | Sezionamento selvaggina allevata | 33
pg=> select * from specie where codice='33';
codice | descrizione
-------+-------------
33 | Selvaggina
(1 row)
sfera=> delete from specie where codice='33';
ERROR: update or delete on "specie" violates foreign key constraint
"attivita_cod_specie_fkey" on "attivita"
DETAIL: Key (codice)=(33) is still referenced from table "attivita".
--------------------------
let's try now using SA:
tg-admin shell
In [1] aa=Specie.get_by(codice='33')
In [3]: aa.delete
Out[3]: <bound method Specie.do of <Specie 33>>
In [4]: aa.delete()
In [5]: aa.flush()
2007-02-16 15:30:31,955 sqlalchemy.engine.base.Engine.0x..f4 INFO BEGIN
2007-02-16 15:30:31,958 sqlalchemy.engine.base.Engine.0x..f4 INFO SELECT
attivita.cod_specie AS attivita_cod_specie, attivita.attivo AS
attivita_attivo, attivita.cod_attivita_istat AS
attivita_cod_attivita_istat, attivita.descrizione AS
attivita_descrizione, attivita.cod_prodotto AS attivita_cod_prodotto,
attivita.cod_tipologia_struttura AS attivita_cod_tipologia_s_2e27,
attivita.cod_organizzazione AS attivita_cod_organizzazione,
attivita.cod_orientamento_produttivo AS attivita_cod_orientament_583a,
attivita.codice AS attivita_codice
FROM attivita
WHERE %(lazy_b4ba)s = attivita.cod_specie ORDER BY attivita.codice
2007-02-16 15:30:31,958 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'lazy_b4ba': '33'}
2007-02-16 15:30:31,979 sqlalchemy.engine.base.Engine.0x..f4 INFO UPDATE
attivita SET cod_specie=%(cod_specie)s WHERE attivita.codice =
%(attivita_codice)s
2007-02-16 15:30:31,980 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'cod_specie': None, 'attivita_codice': '01302'}
2007-02-16 15:30:31,986 sqlalchemy.engine.base.Engine.0x..f4 INFO DELETE
FROM specie WHERE specie.codice = %(codice)s
2007-02-16 15:30:31,986 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'codice': '33'}
2007-02-16 15:30:31,990 sqlalchemy.engine.base.Engine.0x..f4 INFO COMMIT
pg=> select * from specie where codice='33';
codice | descrizione
-------+-------------
pg=> select * from specie where codice='33';
codice | descrizione
-------+-------------
(0 row)
Gary Bernhardt
jose
> Referential integrity isn't being violated here - SA is nulling the
> foreign key before deleting the row it points to. Try adding
> nullable=False to the declaration of attivita.cod_specie. That should
> make it fail in the way you expect, because SA will no longer be able
> to null the foreign key.
This seems to me a trick to avoid integrity referential.
I expected the nullable=False was the default behavior for any foreign
key otherwise the referential integrity is violated here?
I'm very, very surprised for this behavior.
Anyway, I'm using autoload to define my tables thus I don't know how to
add nullable=False to my tables.
jo
>
> On 2/16/07, *Jose Soares* <j...@sferacarta.com
Jonathan Ellis
>
> Gary Bernhardt wrote:
>
> > Referential integrity isn't being violated here - SA is nulling the
> > foreign key before deleting the row it points to. Try adding
> > nullable=False to the declaration of attivita.cod_specie. That should
> > make it fail in the way you expect, because SA will no longer be able
> > to null the foreign key.
>
> This seems to me a trick to avoid integrity referential.
> I expected the nullable=False was the default behavior for any foreign
> key otherwise the referential integrity is violated here?
> I'm very, very surprised for this behavior.
Guess it would surprise you to learn about the SQL 92 "ON DELETE SET
NULL" functionality too. :)
jose
Seems to me the SQL92 'ON DELETE SET NULL' is an explicit functionality,
instead in our case, SA does this functionality in implicit way. :-(
I hope there's a sort of configuration to disable this functionality.
jo
Jonathan Ellis
> >Guess it would surprise you to learn about the SQL 92 "ON DELETE SET
> >NULL" functionality too. :)
> >
> >
> Seems to me the SQL92 'ON DELETE SET NULL' is an explicit functionality,
> instead in our case, SA does this functionality in implicit way. :-(
> I hope there's a sort of configuration to disable this functionality.
Sure, set a NOT NULL constraint on the column you don't want to be
null. Which you should really be doing anyway. :)
jose
No Jonathan, I don't want this column is set as NOT NULL, I have to
allow null values for this column and I don't want enable the "ON DELETE
SET NULL" functionality.
I would like SA have the same behavior as PostgreSQL has, I like the
message:
pg> delete from specie where codice='89';
ERROR: update or delete on "specie" violates foreign key constraint
"attivita_cod_specie_fkey" on "attivita"
DETAIL: Key (codice)=(89) is still referenced from table "attivita".
Is there another way to do that?
jo
Jonathan Ellis
cascade on the relationship (cascade='none').
But you can't have your cake and eat it too, you'll have to manually
handle adding new subordinate objects to the session when saving, etc.
jose
I think I found the way to avoid ON DELETE SET NULL... if I delete the
backref for 'specie' it works. :-)
assign_mapper(context, Attivita, tbl['attivita'], properties = dict(
tipologia_struttura = relation(TipologiaStruttura,
backref='attivita'),
organizzazione = relation(Organizzazione,
backref='attivita'),
prodotto = relation(Prodotto, backref='attivita'),
orientamento_produttivo = relation(OrientamentoProduttivo,
backref='attivita'),
specie = relation(Specie, backref='attivita'),
attivita_istat = relation(AttivitaIstat,
backref='attivita'),
))
Michael Bayer
On Feb 16, 2007, at 3:46 PM, jose wrote:
> No Jonathan, I don't want this column is set as NOT NULL, I have to
> allow null values for this column and I don't want enable the "ON
> DELETE
> SET NULL" functionality.
> I would like SA have the same behavior as PostgreSQL has, I like the
> message:
>
> pg> delete from specie where codice='89';
> ERROR: update or delete on "specie" violates foreign key constraint
> "attivita_cod_specie_fkey" on "attivita"
> DETAIL: Key (codice)=(89) is still referenced from table "attivita".
>
> Is there another way to do that?
basically, if you have A->B, and you delete "A" without any kind of
"delete" cascade to "B", you are basically telling SA, "delete "A"
from the database and keep "B"". SA appropriately is smart enough
to know that it should break the relationship from A to B before
deleting A.
if that operation violates an integrity constraint in your
application then youd have a NOT NULL constraint sitting on "B"'s
foreign key to A. That you say you dont want the "NOT NULL" on the
column is a little strange...I dont exactly understand an
"occasional" not-null constraint on a foreign key column.
you might find setting "delete-orphan" cascade on the relationship
might raise an error for "B" sitting in the session by itself,
though. but then you cant save any "B"s by themselves.
jose
>On Feb 16, 2007, at 3:46 PM, jose wrote:
>
>
>>No Jonathan, I don't want this column is set as NOT NULL, I have to
>>allow null values for this column and I don't want enable the "ON
>>DELETE
>>SET NULL" functionality.
>>I would like SA have the same behavior as PostgreSQL has, I like the
>>message:
>>
>>pg> delete from specie where codice='89';
>>ERROR: update or delete on "specie" violates foreign key constraint
>>"attivita_cod_specie_fkey" on "attivita"
>>DETAIL: Key (codice)=(89) is still referenced from table "attivita".
>>
>>Is there another way to do that?
>>
>>
>
>
>basically, if you have A->B, and you delete "A" without any kind of
>"delete" cascade to "B", you are basically telling SA, "delete "A"
>from the database and keep "B"". SA appropriately is smart enough
>to know that it should break the relationship from A to B before
>deleting A.
>
>
>if that operation violates an integrity constraint in your
>application then youd have a NOT NULL constraint sitting on "B"'s
>foreign key to A. That you say you dont want the "NOT NULL" on the
>column is a little strange...I dont exactly understand an
>"occasional" not-null constraint on a foreign key column.
>
>
In a perfect world, we will not need NULLs, but for the moment it is
better to be realistic,
we don't have an answer for any question and sometimes we have to
respond "I DON'T KNOW" and this transalted to SQL as NULL.
This is why we have LEFT/RIGTH JOINs in SQL.
In my case I need a foreign key that sometimes haven't a value.
Anyway, I think I need to wonderstand better how backrefs works, seems
to me that SA activate the ON DELETE SET NULL
when there's a backref in the relation.
My definition was:
assign_mapper(context, Attivita, tbl['attivita'], properties = dict(specie = relation(Specie, backref='attivita'))
now it is:
assign_mapper(context, Attivita, tbl['attivita'], properties =
dict(specie = relation(Specie))
and it works as I want. :-)
jo
Michael Bayer
On Feb 17, 2007, at 2:55 AM, jose wrote:
> My definition was:
>
> assign_mapper(context, Attivita, tbl['attivita'], properties = dict
> (specie = relation(Specie, backref='attivita'))
>
> now it is:
>
> assign_mapper(context, Attivita, tbl['attivita'], properties =
> dict(specie = relation(Specie))
> and it works as I want. :-)
>
yea a backref is essentially this:
mapper(Attivita, a_table, properties={
'specie':relation(Specie)
})
mapper(Specie, b_table, properties={
'attivita':relation(Attivita)
})
so without the backref you have one less "relation" set up.
but i have bad news for you, SA should be managing that relationship
in the way you dont like for both of those relations. im not sure
why it isnt working, because even if you havent loaded the dependent
item into the session, the delete operation should be loading it in.