2

I have a SQLite application that works fine on Linux, Windows an also Android when using a custom compiled SQLite (using the NDK). Creating tables with and without ROWID works properly.

On the opposite, when I try to create the same schema using the standard SQLite in Android a syntax error is thrown near "WITHOUT" in the next SQL sentence:

CREATE TABLE REL_TABLE1_TABLE2
  ( FK_TABLE1_ID integer, FK_TABLE2_ID integer, 
    FOREIGN KEY (FK_TABLE1_ID)   REFERENCES  TABLE1(ROWID),
    FOREIGN KEY (FK_TABLE2_ID) REFERENCES TABLE2(ROWID),
    PRIMARY KEY (FK_TABLE1_ID, FK_TABLE2_ID) 
  ) 
  WITHOUT ROWID;

If I remove the "WITHOUT ROWID" it works properly. Does it mean WITHOUT ROWID is not supported on Android?.

I found no related documentation but someone else looks to have related problems:

Cannot open sqlite database with linqpad, when WITHOUT ROWID is used

edit: TABLE1/2 still keep the ROWID. WITHOUT ROWID applies only to the N/M REL_TABLE1_TABLE2 table

Community
  • 1
  • 1
earizon
  • 2,099
  • 19
  • 29
  • @Der Golem. Hi, "WITHOUT ROWID" is applied to the table REL_TABLE1_TABLE2. Obviosly, it stays there for TABLE1/2. REL_TABLE1_TABLE2 is just an N/M relationship and FK_TABLE1/2_ID already forms a well defined primary key. – earizon Jul 03 '15 at 11:38
  • OK, but... if it works without `WITHOUT ROWID`, why keeping it? I guess this issue is due to `different SQLite versions`. – Phantômaxx Jul 03 '15 at 11:41
  • 1
    @Der Golem: Because it doesn't make any sense to have 2 primary keys. One the (FK_TABLE1_ID, FK_TABLE2_ID) and then the autogenerated ROWID. From a mathematical point of view (FK_TABLE1_ID, FK_TABLE2_ID) will always suffice as Primary Key in a N/M relation table. (RDB theory). Obviously it continues to work with an extra ROWID, but is wasting space, and more importantly it can cause compatibility problems with SQLite versions that supports / un-support it. My original question is just to now if the SQLite version for Android does actually support it(through some weird config. option f.ex) – earizon Jul 03 '15 at 11:47
  • It doesn't seem to be such a big issue. I guess the current Android SQLite version doesn't support `WITHOUT ROWID`. – Phantômaxx Jul 03 '15 at 11:54

1 Answers1

8

WITHOUT ROWID was introduced in SQLite 3.8.2.

This means that it might or might not be supported on Android, depending on which Android version you're using.

Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259