0

I'm trying to create a shares table for the buy function and have tried multiple CREATE TABLE commands with no success.

Most recently, I tried:

CREATE TABLE IF NOT EXISTS 'shares' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, FOREIGN KEY(fk_user_id) REFERENCES users(user_id), 'stock_symbol' TEXT NOT NULL, 'stock_name' TEXT NOT NULL, 'stock_shares' INTEGER NOT NULL, 'stock_price' INTEGER NOT NULL);

When I enter the query, I get

ERROR: unknown column "fk_user_id" in foreign key definition

I'm not sure what I'm doing wrong here. I'm simply trying to create a foreign key from the users table to store the 'user_id'.

By the way, I'm running the query in phpliteadmin. I also tried this in the sqlite3 cli and got the same error message.

1

Without seeing the description of the table where you're trying to add the foreign key, my best guess is this. FOREIGN KEY(fk_user_id) requires that the parameter here must be a column in the child table. In other words, does/will the child table shares have a column named fk_user_id? Or even user_id? I don't see it in the create table command.

Best practices tip: When building a SQL command to create a table, do all of the column creations first, and then add all the keys and indexes, etc. to the command.

I suggest the following:

CREATE TABLE IF NOT EXISTS 'shares' 
    ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    'user_id' TEXT NOT NULL, 
    'stock_symbol' TEXT NOT NULL, 
    'stock_name' TEXT NOT NULL, 
    'stock_shares' INTEGER NOT NULL, 
    'stock_price' INTEGER NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(user_id)
    );

If this is being used to track current holdings, you might also want to create a unique index on the combination of the two columns user_id and stock_symbol so that all owned shares of an individual stock by an individual user are guaranteed to be in one row and all additions or deletions are carried out on that row.

If this answers your question, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)

2
  • Cliff, you are the man! Thanks! So if I were to create the unique index between user_id and stock_symbol, would this be sufficient? CREATE UNIQUE INDEX ix_uniq_sym ON shares(user_id, stock_symbol);
    – JackJack
    Jul 30 '20 at 0:40
  • sufficient for what? What would you be trying to accomplish? If you want to make sure that there are no duplicate rows using both the userid and symbol, but want to allow multiple rows of the same userid but different symbols ( and allow multiple IDs to have the same stock symbol) then yes, it's enough. But you still want to have the foreign key between userid in this table and in the user table.
    – Cliff B
    Jul 30 '20 at 3:20

You must log in to answer this question.

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