Re: [sqlite] Can't get foreign keys to work

[Richard Hipp]

On Thu, Mar 22, 2012 at 8:46 AM, Marko Mikkonen <mmik...@gmail.com> wrote:

> Hi,
>
> I have two tables like this:
>
> CREATE TABLE IF NOT EXISTS folders (name TEXT NOT NULL ON CONFLICT
> ROLLBACK, parent INTEGER REFERENCES folders (ROWID) ON DELETE CASCADE);
>
> CREATE TABLE IF NOT EXISTS documents (document TEXT NOT NULL ON CONFLICT
> ROLLBACK, folder_id INTEGER REFERENCES folders (ROWID) ON DELETE CASCADE,
> name TEXT NOT NULL ON CONFLICT ROLLBACK);
>
> If I try to add a rows to a fresh database like this:
>
> INSERT INTO folders (parent, name) VALUES (3,'New Folder');
> INSERT INTO documents (document, folder_id, name) values ('some
> text',3,'the name');
>
> I can do it eventhough it violates the foreign key constraints (ROWID of
> the row in folders table is 1). What's wrong with my table definitions? My
> sqlite3.exe's version is 3.7.11 and according to the documentation the
> foreign key constraints should be enforced.
>

For backwards compatibility to legacy versions of SQLite, foreign key
constraints are disabled by default. You have to manually turn foreign key
enforcement on using:

PRAGMA foreign_keys=ON;

>
> -Marko
> ______________________________**_________________
> sqlite-users mailing list
> sqlite...@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>

--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Marko Mikkonen]

Thank you. I see now that it was in the documentation, but I just didn't
see it.

On 22.3.2012 14:48, Richard Hipp wrote:
> PRAGMA foreign_keys=ON;