1

I am using SQLite and DB Browser for SQLite. I know that if you want to insert a foreign key using the GUI, you just have to scroll to the right and double click to insert the foreign key to the corresponding field. The problem is when you want to use a multiple foreign key. How or where should i put the multiple foreign key constrain using the GUI ? my actual sql is (returns an exception 'foreign key mismatch - "diseases" referencing "beehives"...'):

CREATE TABLE "diseases" (
"id"    INTEGER NOT NULL,
"id_beehive"    INTEGER NOT NULL,
"id_apiary" INTEGER NOT NULL,
"disease"   TEXT NOT NULL,
"treatment" TEXT NOT NULL,
"start_treat_date"  DATE NOT NULL,
"end_treat_date"    DATE,
PRIMARY KEY("id"),
FOREIGN KEY("id_beehive") REFERENCES "beehives"("number") ON UPDATE CASCADE,
FOREIGN KEY("id_apiary") REFERENCES "beehives"("id_apiary") ON UPDATE CASCADE
);

what i want to do using the gui is:

CREATE TABLE "diseases" (
"id"    INTEGER NOT NULL,
"id_beehive"    INTEGER NOT NULL,
"id_apiary" INTEGER NOT NULL,
"disease"   TEXT NOT NULL,
"treatment" TEXT NOT NULL,
"start_treat_date"  DATE NOT NULL,
"end_treat_date"    DATE,
PRIMARY KEY("id"),
FOREIGN KEY("id_beehive","id_apiary") REFERENCES "beehives"("number","id_apiary") ON UPDATE CASCADE
);

beehives sql:

CREATE TABLE "beehives" (
"number"    INTEGER NOT NULL,
"id_apiary" INTEGER NOT NULL DEFAULT -2,
"date"  DATE,
"type"  TEXT,
"favorite"  BOOLEAN DEFAULT 'false',
PRIMARY KEY("number","id_apiary"),
FOREIGN KEY("id_apiary") REFERENCES "apiaries"("id") ON DELETE SET NULL
);
Osolemio44
  • 63
  • 1
  • 10
  • Post the definition of beehives. – forpas Apr 27 '20 at 11:39
  • I don't know of any way to create the composite foreign key with the GUI. But your current code, with the 2 separate foreign keys works fine with no errors. – forpas Apr 27 '20 at 11:56
  • Thanks for the answer. And yes, i am asking for a GUI solution, because what i like from DB Browser, is that you can easily modify the table structure without deleting it and loosing all data inside. I know that in this case, i can delete the table, and use the second sql with the correct foreign key sentence, but do you know a way to do it without deleting the table? i have been googleing and it seems that sqlite dont accept foreign key constrains on alter table ( maybe i am wrong about that, my english is not the best. Sorry for that). – Osolemio44 Apr 27 '20 at 12:05
  • You will have to follow the steps described here: https://www.sqlite.org/lang_altertable.html – forpas Apr 27 '20 at 12:15

1 Answers1

1

After researching, it is not possible for the moment. Everytime you modify the structure of the table, what DB Browser for SQLite do on the background is renaming the table, creating a new table with the updated structure, move the data to this new table and delete the old table. So in the GUI it looks like its only updating. Unfortunatly, to create a composite foreign key or modify a simple FK to a composite FK , you can only do it manualy from the SQL window.

example :

FOREIGN KEY("id_beehive","id_apiary") REFERENCES "beehives"("number","id_apiary") ON 
UPDATE CASCADE
Osolemio44
  • 63
  • 1
  • 10