4

i try to use foreign key in my app using web sql.. i test it in chrome.. no error.. but when i test with manual insert to table img (contain FK)
i expect to fail.. but insert is still succes.. this is my code.. please help me.. 

tx.executeSql("PRAGMA foreign_keys = ON;");    
tx.executeSql("CREATE TABLE IF NOT EXISTS img (ID INTEGER PRIMARY KEY ASC,imgID VARCHAR, image VARCHAR,  FOREIGN  KEY (imgID) REFERENCES trans (ID) )", []);

i this case i try to use FK method for save my image name data and connect to trans table..

my insert code

tx.executeSql("insert into img (imgID,image) values ('100','23.jpg')");
Ah Wirayudha
  • 43
  • 5

1 Answers1

0

Is the INSERT performed on the same database connection as the CREATE? Because the PRAGMA foreign_keys = ON is only valid for the connection. It has to be executed for each connection you open.

Also, make sure that you run that PRAGMA statement when you’re not in a transaction, since that wouldn’t have any effect. Your example code starting with tx. suggests that is the cause of your problem.

UPDATE As pointed out in the comments by MisterSmith, extensions of Chromium-based browsers can't run SQL statements outside a transaction anyway, so no foreign key enforcement is possible.

So, you’ll have to ensure your foreign keys maintain integrity either in your application code, or you can write triggers to enforce the foreign keys (if triggers are permitted to Chromium extensions, that is).

Community
  • 1
  • 1
Martijn
  • 13,225
  • 3
  • 48
  • 58
  • I don't think this is a reliable approach, since PRAGMA is apparently disallowed in some browsers. Read [here](http://code.google.com/p/chromium/issues/detail?id=57218). – Mister Smith Feb 19 '13 at 14:02
  • Yep, in Chrome I think it comes down to the `Database` (from `openDatabase(...)`) not having outside-of-transaction `db.exec(...)` (like e.g. React Native has) or similar and it needs to be executed as no-op (no operation) you have no way of doing so since no-op can't be done with pending `BEGIN` which is basically opening the transaction ... :-) So it may be that it's technically allowed, just not doable. Great link mr. Matrix virus @MisterSmith – jave.web Mar 08 '22 at 11:20