Re: [sqlite] Where Clause

[Roland Wilczek]

Hi,

You expected the database to perform the assignment on each row WHERE ID = 2.
It did exactly this.

-- 
kind regards

- Roland Wilczek

Am Freitag, 2. August 2013, 14:04:40 schrieb techi eth:
> At the end of execution I am expecting SET will change the value but it
> actually doesn't SET the value due to wrong condition.
> 
> I will check return from sqlite3 & due to success return I am in wrong
> assumption.
> 
> On Fri, Aug 2, 2013 at 1:57 PM, Igor Korot <ikoro...@gmail.com> wrote:
> > Hi
> > Why do you think it should return error?
> > 
> > On Aug 2, 2013 1:15 AM, "techi eth" <techi...@gmail.com> wrote:
> > > Come across one issue with conditional query execution.
> > > 
> > > Query: UPDATE COMPANY SET Name= 'test' WHERE ID = 2;
> > > According to my understanding if no ID = 2 is present in table then
> > > error
> > > should return but it return with SQLITE_OK however Name value is not
> > > changed.
> > > 
> > > Cheers -
> > > Techi
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-- 
kind regards

- Roland Wilczek

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

Re: [sqlite] Unstable ON DELETE actions

[Roland Wilczek]

> Igor wrote:
> So don't create two foreign keys that come into conflict.

I would never do, but the users of my tool could make that mistake.
And then my tool must do, what SQLite does.

Problem: SQLite's behaviour is hard to predict and tends to surprise you.
Thats the reason for my questions: Can I hope for better documentation or 
fewer surprises?

-- 
mit freundlichen Grüßen

- Roland Wilczek
Certified ScrumMaster (CSM)

Nachtigallenstraße 11
53179 Bonn

Tel.: 0228 / 336 70 40 9
Mobil: 0171 / 72 36 849
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unstable ON DELETE actions

[Roland Wilczek]

> Clemens wrote:
> > 
> > CREATE TABLE track (artist,
> > FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE
> > FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);
> 
> SQLite allows pretty much anything inside a CREATE TABLE statement, and
> ignores anything it doesn't recognize.
> 
> This particular statement creates a table with a single foreign key
> constraint.  Table constraints must be separated with commas.

Just separated the constraints with comma instead of new line. 
No test changed it's behaviour.
-- 
kind regards

- Roland Wilczek
Certified ScrumMaster (CSM)

Nachtigallenstraße 11
53179 Bonn

Tel.: 0228 / 336 70 40 9
Mobil: 0171 / 72 36 849
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unstable ON DELETE actions

[Roland Wilczek]

ack"->"cd"
 * are declared.
 *
 * Perhaps things would change, if one would change the order of CREATE TABLE 
...
 */

-- RESTRICT vs. CASCADE: RESTRICT wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist VALUES(1);
CREATE TABLE cd (id PRIMARY KEY,artist,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);
INSERT INTO cd VALUES(1,1);
CREATE TABLE track (artist,cd,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT
FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE CASCADE);
INSERT INTO track VALUES(1,1);
DELETE FROM artist  /* Error: foreign key constraint failed */;
SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */;
SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */;
SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 
*/;

-- CASCADE vs. RESTRICT: RESTRICT wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist VALUES(1);
CREATE TABLE cd (id PRIMARY KEY,artist,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);
INSERT INTO cd VALUES(1,1);
CREATE TABLE track (artist,cd,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE
FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE RESTRICT);
INSERT INTO track VALUES(1,1);
DELETE FROM artist  /* Error: foreign key constraint failed */;
SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */;
SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */;
SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 
*/;

-- CASCADE vs. SET NULL: RESTRICT wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist VALUES(1);
CREATE TABLE cd (id PRIMARY KEY,artist,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);
INSERT INTO cd VALUES(1,1);
CREATE TABLE track (artist,cd,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE
FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE SET NULL);
INSERT INTO track VALUES(1,1);
DELETE FROM artist  /* Error: foreign key constraint failed */;
SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */;
SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */;
SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 
*/;

-- SET NULL vs. CASCADE: RESTRICT wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist VALUES(1);
CREATE TABLE cd (id PRIMARY KEY,artist,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);
INSERT INTO cd VALUES(1,1);
CREATE TABLE track (artist,cd,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL
FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE CASCADE);
INSERT INTO track VALUES(1,1);
DELETE FROM artist  /* Error: foreign key constraint failed */;
SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */;
SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */;
SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 
*/;

-- RESTRICT vs. SET NULL: RESTRICT wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist VALUES(1);
CREATE TABLE cd (id PRIMARY KEY,artist,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);
INSERT INTO cd VALUES(1,1);
CREATE TABLE track (artist,cd,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT
FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE SET NULL);
INSERT INTO track VALUES(1,1);
DELETE FROM artist  /* Error: foreign key constraint failed */;
SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */;
SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */;
SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 
*/;

-- SET NULL vs. RESTRICT: RESTRICT wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist VALUES(1);
CREATE TABLE cd (id PRIMARY KEY,artist,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);
INSERT INTO cd VALUES(1,1);
CREATE TABLE track (artist,cd,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL
FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE RESTRICT);
INSERT INTO track VALUES(1,1);
DELETE FROM artist  /* Error: foreign key constraint failed */;
SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */;
SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */;
SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 
*/;

-- 
kind regards

- Roland Wilczek
Certified ScrumMaster (CSM)

Nachtigallenstraße 11
53179 Bonn

Tel.: 0228 / 336 70 40 9
Mobil: 0171 / 72 36 849
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users