CODEWITHSUNDEEP
androidsqlsqlite
Samantha Withanage
Samantha Withanage

Reputation: 3851

SQLite FOREIGN KEY Constraint

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.

Upvotes: 0

Views: 725

Answers (3)

Bishan
Bishan

Reputation: 15702

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

Upvotes: 1

ardevd
ardevd

Reputation: 3407

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.

Upvotes: 1

Rakeeb Rajbhandari
Rakeeb Rajbhandari

Reputation: 5063

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.

Upvotes: 3

Related Questions