SQLite FOREIGN KEY Constraint

Question

In my android project's SqLite database I have to create a foreign key which is the primery key of the table. I wrote the sql statement as below using SQLiteManager.

 CREATE TABLE OBTTourVehicleUpdate
(
TourHeaderCode INT PRIMARY KEY     NOT NULL,
TourVehicleProcessCode INT    NOT NULL,
VehicleCode CHAR(10),
TourStart TEXT ,
TourEnd TEXT ,
LastMilage DOUBLE,
NewMilage DOUBLE,
CONSTRAINT  FOREIGN KEY (TourHeaderCode) REFERENCES OBTTourHeader(TourHeaderCode)

);

It gives me an error message saying that

Internal Error. near FOREIGN: syntax error.

The table structure of two tables are as below. enter image description here how can I fix this.

show me `TourHeaderCode` table structure – Chintan Khetiya Dec 30 '13 at 07:26 — Chintan Khetiya, Dec 30 '13 at 07:26
OK, I'll post. Thanks. – Samantha Withanage Dec 30 '13 at 07:30 — Samantha Withanage, Dec 30 '13 at 07:30

score 3 · Accepted Answer · answered Dec 30 '13 at 07:27

3

Remove CONSTRAINT from your code. Just do the

FOREIGN KEY (TourHeaderCode) REFERENCES OBTTourHeader(TourHeaderCode).

Remember to call the table on the onCreate() and onUpdate() and also update the DB_version. And to be on the safe side, do not declare your FK as the PK too.

answered Dec 30 '13 at 07:27

Rakeeb Rajbhandari

5,003
6
42
74

1

Ok. It works. Thanks for the advice. But this is not a my design. I'll remember "do not declare your FK as the PK too". Thanks a lot! – Samantha Withanage Dec 30 '13 at 07:37

score 1 · Answer 2 · answered Dec 30 '13 at 07:28

You get that error because your syntax is indeed incorrect.

Read the official SQLite documentation to learn more about how it should be used.

CREATE TABLE OBTTourVehicleUpdate
(
TourHeaderCode INT PRIMARY KEY     NOT NULL,
TourVehicleProcessCode INT    NOT NULL,
VehicleCode CHAR(10),
TourStart TEXT ,
TourEnd TEXT ,
LastMilage DOUBLE,
NewMilage DOUBLE,
FOREIGN KEY(TourHeaderCode) REFERENCES OBTTourHeader(TourHeaderCode)
);

Something like that should work.

score 1 · Answer 3 · answered Dec 30 '13 at 07:30

1

CREATE TABLE OBTTourVehicleUpdate
(
TourHeaderCode INT PRIMARY KEY     NOT NULL,
TourVehicleProcessCode INT    NOT NULL,
VehicleCode CHAR(10),
TourStart TEXT ,
TourEnd TEXT ,
LastMilage DOUBLE,
NewMilage DOUBLE,
FOREIGN KEY(TourHeaderCode) REFERENCES OBTTourHeader(TourHeaderCode)
);

You don't need to use CONSTRAINT in your query. Follow this article about Foreign Key Constraints

answered Dec 30 '13 at 07:30

Bishan

14,610
50
157
245

Yep, It works. I used "CONSTRAINT" by think of SQL statement. Thank you! – Samantha Withanage Dec 30 '13 at 07:34
@SamanthaWithanage Don't care. it's a common programming mistake :) – Bishan Dec 30 '13 at 08:10

SQLite FOREIGN KEY Constraint

3 Answers3