Skip to content
Advertisement

How to use multiple foreign keys in one table in sqlite?

String Post_Code = "CREATE TABLE IF NOT EXISTS PostCode("
                        + "PostCode_ID integer PRIMARY KEY, "
                        + "Code        string NOT NULL, "
                        + "City_ID     integer,"
                        + "FOREIGN KEY (City_ID)"
                        + "REFERENCES City (City_ID)"
                        +   "ON UPDATE CASCADE "
                        +   "ON DELETE SET NULL, "
                        + "County_ID   integer,"
                        + "FOREIGN KEY (County_ID)"
                        + "REFERENCES County (County_ID)"
                        +   "ON UPDATE CASCADE "
                        +   "ON DELETE SET NULL"
                        + ");";

I believe it’s most likely to be something within the first foreign key reference.

Advertisement

Answer

Either move all the FOREIGN KEY definitions at the end of the statement:

CREATE TABLE IF NOT EXISTS PostCode(
  PostCode_ID integer PRIMARY KEY, 
  Code TEXT NOT NULL, 
  City_ID integer,
  County_ID integer,
  FOREIGN KEY (City_ID) REFERENCES City (City_ID) ON UPDATE CASCADE ON DELETE SET NULL, 
  FOREIGN KEY (County_ID)REFERENCES County (County_ID) ON UPDATE CASCADE ON DELETE SET NULL
);

or, define each foreign key right after the definition of each column without the FOREIGN KEY keywords:

CREATE TABLE IF NOT EXISTS PostCode(
  PostCode_ID integer PRIMARY KEY, 
  Code TEXT NOT NULL, 
  City_ID integer REFERENCES City (City_ID) ON UPDATE CASCADE ON DELETE SET NULL,
  County_ID integer REFERENCES County (County_ID) ON UPDATE CASCADE ON DELETE SET NULL
);

See the demo.

Note that there is no string data type in SQLite.
I changed it to TEXT.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement