How to create a foreign key in swift sqlite3?-CodePudding

I am trying to create a sqlite table containing a foreign key, using the default sqlite3 library from swift/xCode via import sqlite3. The table is getting created without the key, but if I add the foreign key to the statement, I get a syntax error.

Error: [logging] near "date": syntax error

"""
        CREATE TABLE IF NOT EXISTS measurement(
          measurementid         INTEGER     PRIMARY KEY AUTOINCREMENT   NOT NULL,
          accountid             INTEGER                                 NOT NULL,
            FOREIGN KEY(accountid) REFERENCES accounts(userid),
          date                  TEXT                                    NOT NULL,
          measurementtype       TEXT                                    NOT NULL,
          moodmeasurementid     INTEGER,
          healthmeasurementid   INTEGER,
          bodymeasurementid     INTEGER,
          workout               TEXT                                    NOT NULL
        );
"""

"""
        CREATE TABLE IF NOT EXISTS accounts(
            userid          INTEGER PRIMARY KEY AUTOINCREMENT   NOT NULL,
            email           TEXT                                NOT NULL,
            password        TEXT                                NOT NULL,
            firstname       TEXT                                NOT NULL,
            lastname        TEXT                                NOT NULL,
            birthday        TEXT                                NOT NULL,
            gender          TEXT                                NOT NULL,
            creationdate    TEXT                                NOT NULL
        );
        """

After some research I read, that you might need to activate foreign keys, before using them. So I added the following code, but I still get the same error.

if sqlite3_exec(dbPointer, "PRAGMA foreign_keys = ON", nil, nil, nil) != SQLITE_OK {
            let err = String(cString: sqlite3_errmsg(dbPointer))
                print("error attempting to enable foreign keys: \(err)")
            }

Also adding it to the end of the statement didn't fix it.

CodePudding user response：

You are using the table constraint syntax instead of the column constraint syntax.

You want:

accountid             INTEGER                                 NOT NULL
  REFERENCES accounts(userid),

The FOREIGN KEY(accountid) would be needed if you were adding the constraint to the table. Also note the removal of the comma after NOT NULL.

See https://www.sqlite.org/lang_createtable.html for complete details of the syntax.