Re: [sqlite] insert: how to force application to provide value for int primary key?
Yes, indeed works. Great, thank you! Marek On Tue, Sep 10, 2019 at 9:13 AM Richard Hipp wrote: > On 9/10/19, Marek Wieckowski wrote: > > > > Is there a way in sqlite to ensure (on the database side) that all > inserts > > for such a table have to have explicit values for such a column? > > Make it a WITHOUT ROWID table: > > CREATE TABLE example_table ( >id INTEGER PRIMARY KEY, >description TEXT NOT NULL >) WITHOUT ROWID; > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] insert: how to force application to provide value for int primary key?
Hi,
I have an application where a table has an unique not null int field which
identifies the records. I would like to use this column as a primary key.
However, the value has a meaning from the application point of view. There
should never be a record inserted into this table with some default
(random/autoincrement) value filled in automatically by the database.
Is there a way in sqlite to ensure (on the database side) that all inserts
for such a table have to have explicit values for such a column?
Example:
CREATE TABLE example_table
(
id INTEGER PRIMARY KEY,
description TEXT NOT NULL
);
I want
INSERT INTO example_table (description) VALUES ('abc');
to fail with an error, and
INSERT INTO example_table (id, description) VALUES (5, 'abc');
to succeed.
Is it at all possible?
For example, defining a trigger before insert and checking that NEW.id IS
NULL does not work - in before trigger the row has already the field value
(automatically) filled in.
PS: If it's not possible I will end up with a table like
CREATE TABLE example_table
(
id INTEGER PRIMARY KEY,
value INTEGER UNIQUE NOT NULL,
description TEXT NOT NULL
);
with and value being meaningful from the application point of view and id
being internal/artificial. But: I would rather avoid such an additional
artificial column...
Best,
Marek
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect
Hi James, Thanks for your answer. Yes, there are lots of different queries that you might want to write _instead_ - in your example you have changed the logic of the update... But you could also rewrite the original query to keep the original logic in such a way that it would work in sqlite (see e.g. the stackoverflow page http://stackoverflow.com/questions/39350537/sqlite-update-select-query-referencing-table-being-updated-in-order-by-clause/ - the simplest is to ... add an extra level of sub-select, select the value you want to order by (so you use an "external" field in SELECT) and then order by the value from sub-subselect... Super ugly and harder to understand.). The thing is that in principle there is nothing wrong with using test.xxx fields in the subselect: there really should be no difference whether you use them in "where" or "order by"... The fact that sqlite does not allow them to be used in ORDER BY (while allowing in SELECT and WHERE) imho is simply a bug. You don't want to force users to write ugly workarounds. Oh, and btw: the same syntax (with using an external field in ORDER BY of a subselect) simply works e.g. in update statements postgres. Best, Marek On Tue, Feb 14, 2017 at 4:36 PM, James K. Lowden <jklow...@schemamania.org> wrote: > On Tue, 14 Feb 2017 15:06:16 +0100 > Marek Wieckowski <wiec...@gmail.com> wrote: > > > UPDATE test > > SET value = value + 100 *( > > SELECT i.value > > FROM test i > > WHEREi.whereField = test.whereField > > ORDER BY i.orderField = test.orderField > > LIMIT 1 > > ); > > > > Error: no such column: test.orderField > > There are other errors, too. The update is nondeterministic and > nonstandard. Does this not serve the purpose better? > > UPDATE test > SET value = value + 100 * ( > SELECT min(i.value) -- or max, or something > FROM test i > WHEREi.whereField = test.whereField > ); > > --jkl > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect
Hi, Basically: in a subselect in an update statement all fields from the row which is being updated CAN be used in SELECT and WHERE clauses (see the first update below using test.whereField in the WHERE clause), but using them in ORDER BY leads to an error. Example: create table test ( whereField integer, orderField integer, value integer); insert into test (whereField, orderField, value) values (0, 0, 10), (1, 2, 30), (0, 1, 15), (1, 0, 7); UPDATE test SET value = value + 100 *( SELECT i.value FROM test i WHEREi.whereField = test.whereField LIMIT 1 ); SELECT * FROM test; -- whereField orderField value -- -- -- -- -- 0 0 1010 -- 1 2 3030 -- 0 1 101015 -- 1 0 303007 UPDATE test SET value = value + 100 *( SELECT i.value FROM test i WHEREi.whereField = test.whereField ORDER BY i.orderField = test.orderField LIMIT 1 ); Error: no such column: test.orderField Reproducible with the newest vanilla sqlite: sqlite-autoconf-317.tar.gz SQLite version 3.17.0 2017-02-13 16:02:40 but the same bug exists in older versions: we noticed it back in September, see e.g. http://stackoverflow.com/questions/39350537/sqlite-update-select-query-referencing-table-being-updated-in-order-by-clause/ Regards, Marek ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
