Re: [sqlite] Unexpected cascading delete

[Duquette, William H (318K)]

Richard,

I was afraid you were going to tell me that; it makes all
too much sense, once I thought about.

Thanks for the definitive word.

Will

On 1/12/11 2:08 PM, "Richard Hipp"  wrote:

On Wed, Jan 12, 2011 at 4:54 PM, Duquette, William H (318K) <
william.h.duque...@jpl.nasa.gov> wrote:

> I've just discovered that a REPLACE can trigger a
> cascading delete.  Is this expected behavior?
>
> I have an undo scheme where I grab entire rows from the
> database before they are changed; then, on undo I
> simply put the rows back using "INSERT OR REPLACE".
> My assumption was that doing a REPLACE was
> equivalent to doing an UPDATE on the non-key
> values given the key values.  Apparently not.
>

Nope.  REPLACE is a shorthand for DELETE followed by INSERT.  Note that if
there are multiple uniqueness constraints on a table, a REPLACE might get
translated into multiple DELETEs (one for each uniqueness constraint)
followed by a single INSERT.  Hence a REPLACE can reduce the number of rows
in a table.



>
> Here's some SQL that illustrates the problem:
>
> PRAGMA foreign_keys=1;
>
> CREATE TABLE parent(
>parent_id TEXT PRIMARY KEY,
>value TEXT);
>
> CREATE TABLE child(
>child_id  INTEGER PRIMARY KEY,
>parent_id TEXT REFERENCES parent(parent_id)
>   ON DELETE CASCADE
>   DEFERRABLE INITIALLY DEFERRED,
>value TEXT);
>
> INSERT INTO parent(parent_id, value) VALUES('FRED',   1);
> INSERT INTO parent(parent_id, value) VALUES('GEORGE', 2);
>
> INSERT INTO child(parent_id, value) VALUES('FRED',   'FOO');
> INSERT INTO child(parent_id, value) VALUES('FRED',   'BAR');
> INSERT INTO child(parent_id, value) VALUES('GEORGE', 'FOO');
>
> -- Causes the records in child that reference FRED
> -- to be deleted.
> REPLACE INTO parent(parent_id, value) VALUES('FRED',3);
>
>
>
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected cascading delete

[Richard Hipp]

On Wed, Jan 12, 2011 at 4:54 PM, Duquette, William H (318K) <
william.h.duque...@jpl.nasa.gov> wrote:

> I've just discovered that a REPLACE can trigger a
> cascading delete.  Is this expected behavior?
>
> I have an undo scheme where I grab entire rows from the
> database before they are changed; then, on undo I
> simply put the rows back using "INSERT OR REPLACE".
> My assumption was that doing a REPLACE was
> equivalent to doing an UPDATE on the non-key
> values given the key values.  Apparently not.
>

Nope.  REPLACE is a shorthand for DELETE followed by INSERT.  Note that if
there are multiple uniqueness constraints on a table, a REPLACE might get
translated into multiple DELETEs (one for each uniqueness constraint)
followed by a single INSERT.  Hence a REPLACE can reduce the number of rows
in a table.



>
> Here's some SQL that illustrates the problem:
>
> PRAGMA foreign_keys=1;
>
> CREATE TABLE parent(
>parent_id TEXT PRIMARY KEY,
>value TEXT);
>
> CREATE TABLE child(
>child_id  INTEGER PRIMARY KEY,
>parent_id TEXT REFERENCES parent(parent_id)
>   ON DELETE CASCADE
>   DEFERRABLE INITIALLY DEFERRED,
>value TEXT);
>
> INSERT INTO parent(parent_id, value) VALUES('FRED',   1);
> INSERT INTO parent(parent_id, value) VALUES('GEORGE', 2);
>
> INSERT INTO child(parent_id, value) VALUES('FRED',   'FOO');
> INSERT INTO child(parent_id, value) VALUES('FRED',   'BAR');
> INSERT INTO child(parent_id, value) VALUES('GEORGE', 'FOO');
>
> -- Causes the records in child that reference FRED
> -- to be deleted.
> REPLACE INTO parent(parent_id, value) VALUES('FRED',3);
>
>
>
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected cascading delete

[Gerry Snyder]

On 1/12/2011 2:54 PM, Duquette, William H (318K) wrote:
> I've just discovered that a REPLACE can trigger a
> cascading delete.  Is this expected behavior?
>
> I have an undo scheme where I grab entire rows from the
> database before they are changed; then, on undo I
> simply put the rows back using "INSERT OR REPLACE".
> My assumption was that doing a REPLACE was
> equivalent to doing an UPDATE on the non-key
> values given the key values.  Apparently not.

 From the "ON CONFLICT" section of the docs:

"When a UNIQUE constraint violation occurs, the REPLACE algorithm 
deletes pre-existing rows that are causing the constraint violation 
prior to inserting or updating the current row and the command continues 
executing normally."

This seems to make it expected behavior.


Gerry (JPL retiree)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unexpected cascading delete

[Duquette, William H (318K)]

I've just discovered that a REPLACE can trigger a
cascading delete.  Is this expected behavior?

I have an undo scheme where I grab entire rows from the
database before they are changed; then, on undo I
simply put the rows back using "INSERT OR REPLACE".
My assumption was that doing a REPLACE was
equivalent to doing an UPDATE on the non-key
values given the key values.  Apparently not.

Here's some SQL that illustrates the problem:

PRAGMA foreign_keys=1;

CREATE TABLE parent(
parent_id TEXT PRIMARY KEY,
value TEXT);

CREATE TABLE child(
child_id  INTEGER PRIMARY KEY,
parent_id TEXT REFERENCES parent(parent_id)
   ON DELETE CASCADE
   DEFERRABLE INITIALLY DEFERRED,
value TEXT);

INSERT INTO parent(parent_id, value) VALUES('FRED',   1);
INSERT INTO parent(parent_id, value) VALUES('GEORGE', 2);

INSERT INTO child(parent_id, value) VALUES('FRED',   'FOO');
INSERT INTO child(parent_id, value) VALUES('FRED',   'BAR');
INSERT INTO child(parent_id, value) VALUES('GEORGE', 'FOO');

-- Causes the records in child that reference FRED
-- to be deleted.
REPLACE INTO parent(parent_id, value) VALUES('FRED',3);



Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users