1

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) );

2 Answers 2

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.

3
  • Hi! Thanx for the reply! No, I wasn't referring to PRAGMA. I was thinking more along the lines of ALTER TABLE table ADD COLUMN column type, FOREIGN KEY (column) REFERENCES users(id); I never added or deleted anything from the users table (no need to). So, what's the cleanest way to deal with this? Should I drop the table I have now and run that PRAGMA statement after the above CREATE TABLE statement and be done with it? Or does one run the PRAGMA statement at another time?
    – Ryan
    Feb 16, 2017 at 23:26
  • 1
    My understanding of sqlite is you can only create a foreign_key in the create table script. You need to run the PRAGMA statement so the app can use the foreign key as in so must be enabled separately for each database connection. Feb 16, 2017 at 23:37
  • Many thanks for the help! I'll pursue the strategy I outlined below, hopefully it will allow me to bring this baby in tonight!
    – Ryan
    Feb 17, 2017 at 0:00
0

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")

3
  • Did you manage to get phpliteadmin to use foreign keys? I've designed a database structure where I want to use foreign keys, but I get: "ERROR: near "db": syntax error db.execute("PRAGMA foreign_keys = ON")". I am not sure where to put this db.execute (in the database or the table)?
    – Rachel
    Mar 13, 2017 at 2:47
  • @Rachel - Hi Rachel! Sorry to hear you are having issues. I stuck it in the login function. I figured that would be as good a place as any to put it. Everything seemed to work just fine. My exact statement was db.execute("PRAGMA foreign_keys = ON")
    – Ryan
    Mar 14, 2017 at 21:39
  • @Rachel - Forgot to mention a bunch of us were going thru pset7 at the same time over on CS50's sub reddit a few weeks ago. We kicked around a number of questions. I believe everyone who was working on it at the time completed it and has moved on to "mashup." So, you may want to check out what was said there over the last month or so, if you have other issues. Best of luck!
    – Ryan
    Mar 14, 2017 at 22:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .