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
