ORM and sqlite

[carlo]

Does ORM generate syntax like this?

CREATE TABLE bar (
id INTEGER NOT NULL PRIMARY KEY,
fooId INTEGER
CONSTRAINT fk_foo_id REFERENCES foo(id),
fooId2 INTEGER NOT NULL
CONSTRAINT fk_foo_id2 REFERENCES foo(id) ON DELETE CASCADE
);

I mean does it make use of CONSTRAINT ?

carlo

[Massimo Di Pierro]

the keyword CONTRAINT is used (required) only in Oracle. In the case
MySQL, SQLite, and PostgreSQL it uses REFERENCES but not CONSTRAINT.
I believe sqlite ignores constraints anyway.

Massimo

[carlo]

Sqlite parses both CONSTRAINT and REFERENCES but ignores them in the
sense no foreign key is created.
This means data integrity is quite at risk with such a rdbms: I read
that you can enforce REFERENCES, i.e. getting something like the MySQL
CASCADE ON DELETE, through TRIGGERS and it seems quite easy but as far
as I know you can set this kind of Triggers only if you used the
CONSTRAINT statement in the table CREATE.

Can you confirm this? I suppose that due to this sqlite limitation,
without triggers, ORM would let you delete a record even if there are
other tables referencing to that record, is this correct?

carlo

[Massimo Di Pierro]

I believe you are correct.

Massimo

[carlo]

ok I made some tests.

First thing for those of you interested in getting relational
integrity in your sqlite db (and you should be, imho) you may find
very interesting this:

http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers

From my tests it does not matter if you declared CONSTRAINT or even
REFERENCES to define your "pseudo" foreign keys: what it does matter
is you set your triggers correct. With some simple trigger directives
you can:

-prevent an INSERT in the table who has a foreign key if the
referenced key does not exist
-prevent an UPDATE in the same situation as above
-simulate the ON DELETE CASCADE and the ON DELETE RESTRICT statements
you use in other rdbms

carlo