On Sun, 12 Jul 2009 18:03:14 -0700, "Jim Showalter"
<[email protected]> wrote:
>Schema:
>
>create table words (_id integer primary key autoincrement, wordtext
>text not null unique);
>
>create table definitions (_id integer primary key autoincrement,
>owningWordId integer not null unique, deftext text not null);
>
>create trigger fki_definitions_words_id before insert on definitions
>for each row
>begin
> select raise (rollback, 'insert on table definitions violates
>foreign-key constraint fki_definitions_words_id')
> where (select _id from words where _id = NEW.owningWordId ) is
>null;
>
>end;
>
>Call db.insert, passing it a definition that has the owningWordId set
>to -1, and the insert returns a -1 instead of throwing.
>
>Because it doesn't throw, I don't have the error message "insert on
>table definitions violates foreign-key constraint
>fki_definitions_words_id" to work from. Information is simply lost.
>
>Why isn't it raising an exception?
It does raise.
SQLite behaves as expected.
Must be the wrapper.
sqlite_version():3.6.14.2
create table words (
_id integer primary key autoincrement,
wordtext text not null unique
);
create table definitions (
_id integer primary key autoincrement,
owningWordId integer not null unique,
deftext text not null
);
create trigger fki_definitions_words_id
before insert on definitions
for each row
begin
select raise (rollback,
'insert on table definitions violates foreign-key
constraint fki_definitions_words_id')
where (
select _id from words where _id = NEW.owningWordId
) is null;
end;
.bail off
insert into words (wordtext)
VALUES ('wordone');
insert into definitions (owningWordId,deftext)
VALUES ((select last_insert_rowid()),'defone');
insert into definitions (owningWordId,deftext)
VALUES (-1,'deftwo');
SQL error near line 7: insert on table definitions violates
foreign-key constraint fki_definitions_words_id
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
