LocalStorage DB and foreign key constraints

Forums » Dev.Opera » General Web Development Discussions

You need to be logged in to post in the forums. If you do not have an account, please sign up first.

Go to last post

27. May 2010, 11:05:17

jolo1701

Posts: 1

LocalStorage DB and foreign key constraints

I'm developing a web-app that uses the built in localStorage SQL DB in Opera 10.53, as specified in HTML5. When i try to use constraints in the tables, it won't work.

I define 2 test tables:

CREATE TABLE `TEST1` (
`ID` INTEGER PRIMARY KEY AUTOINCREMENT
,`TEST1_TEXT` TEXT NOT NULL
)
CREATE TABLE `TEST2` (
`ID` INTEGER PRIMARY KEY AUTOINCREMENT
,`TEST1_ID` INTEGER
,`TEST2_TEXT` TEXT NOT NULL
, FOREIGN KEY (`TEST1_ID`) REFERENCES `TEST1`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE
)

I insert some data into TEST1:
INSERT INTO TEST1 (TEST1_TEXT) VALUES ('test1')
INSERT INTO TEST1 (TEST1_TEXT) VALUES ('test2')

Then insert some data into TEST2:
INSERT INTO TEST2 (TEST1_ID,TEST2_TEXT) VALUES (1,'text1')
INSERT INTO TEST2 (TEST1_ID,TEST2_TEXT) VALUES (2,'text2')

The tables then contains:
ID : TEST1_TEXT
===============
1 : test1
2 : test2

ID : TEST1_ID : TEST2_TEXT
==========================
1 : 1 : test1
2 : 2 : test2

So far so good. But when i try to insert this:
INSERT INTO TEST2 (TEST1_ID,TEST2_TEXT) VALUES (99,'test3')

I get no errors, it happily inserts this row.

If i then try to delete something from table TEST1
DELETE FROM TABLE1 WHERE ID = 1

The line from TEST1 is deleted but the line in TEST2 pointing to TEST1 is not deleted.
The two tables look like this:

ID : TEST1_TEXT
===============
2 : test2

ID : TEST1_ID : TEST2_TEXT
==========================
1 : 1 : test1
2 : 2 : test2
3 : 99 : test3

According to http://www.sqlite.org/foreignkeys.html
"Foreign key constraints are disabled by default"
If that is so in Opera 10.53, how do i enable it?
If i try the command suggested at the mentioned web page, i get an error.

PRAGMA foreign_keys = ON
Error #5 : not authorized

I tried to search dev.opera.com for info about this but found nothing.
Can someone please help!

Forums » Dev.Opera » General Web Development Discussions