Set foreign key in phpLiteAdmin

Question

Does anyone know how to set a foreign key using the phpLiteAdmin interface? The docs for this application are lacking, and the recommended syntax for adding a column using SQLite does not appear to work. I'd like the id field from the users table to become the 'user_id' foreign key in my transactions table. Note, using the following create table statement left me with a bizarre error: ERROR: no such table: temp.users even though the table was created and interacted with my flask website. Hence, I want to set the foreign key using the GUI interface now, if possible.

• CREATE TABLE 'transactions' ( 'tx_id' INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, 'ticker' TEXT UNIQUE NOT NULL, 'security' TEXT UNIQUE NOT NULL, 'price' NUMERIC NOT NULL, 'shares' INTEGER NOT NULL, 'tx_type' INTEGER NOT NULL, 'date' DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, 'user_id' INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) );

Answer 1

If by this

recommended syntax for adding a column using SQLite does not appear to work.

you mean foreign key is not enforced, check out section 2 in SQLite doc, which says

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:

sqlite> PRAGMA foreign_keys = ON;

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

(If that's not what you mean, well, that's what the delete key is for!)

Regarding the bizarre error: Did you ever drop a column in the users table? I ask because sqlite can't really drop columns, so phpliteadmin would have to make a (temporary) copy of the table, drop it, recreate it, then copy the rows back in. What makes this extra bizarre is that in a small test I did, phpLiteAdmin will happily add a foreign key even if the table in REFERENCES doesn't exist.

Answer 2

I think I got it. Another person using Ruby and SQLite had a problem with foreign keys here: SQLite and foreign keys in Ruby One of the solutions suggested, as DinoCoderSaurus mentioned, a PRAGMA statement be enabled for each database connection. The poster recommended putting the following at the top of the file that executes db commands, but only the second (bold) line should be necessary, in my case.

db = SQLite3::Database.new("database.db")

db.execute("PRAGMA foreign_keys = ON")