1

Can anyone tell me what's wrong with my INSERT INTO? The table is not updating. I get the first purchase and nothing after that. https://gist.github.com/0bc6c50a5f399ed1a672a8a345cbc395

Here is the SQL table 
             Column #   Field   Type    Not NULL    Default Value   Primary Key
    Edit    Delete  0   symbol  TEXT    No          None            No
    Edit    Delete  1   shares  INTEGER No          None            No
    Edit    Delete  2   cash    REAL    No          None            No
    Edit    Delete  3   id      INTEGER No          None            Yes
    Edit    Delete  4   price   REAL    No          None            No
Check All / Uncheck All With Selected:  Go
Improve this question
5
  • 1
    Usually, this is more about the database than the code. If you successfully added one purchase and then cannot add any more for the same user, that usually indicates a primary or unique key issue. Can you post the DDL code (the SQL code) that was used to create the table and, importantly, all of the indexes on that table? I'll bet that you have a unique index on the user id only, where you should have a unique index on the combination of user and symbol. Also, if you're doing a second purchase of the same stock, you would need an additional clause to update the existing row.
    – Cliff B
    Commented Mar 14, 2017 at 2:55
  • Can I upload the finance.db file?
    – cfdphd
    Commented Mar 14, 2017 at 19:51
  • Column # Field Type Not NULL Default Value Primary Key Edit Delete 0 symbol TEXT No None No Edit Delete 1 shares INTEGER No None No Edit Delete 2 cash REAL No None No Edit Delete 3 id INTEGER No None Yes Edit Delete 4 price REAL No None No Check All / Uncheck All With Selected: Go
    – cfdphd
    Commented Mar 14, 2017 at 19:51
  • You can edit your question, or add a link to the file on github.
    – Cliff B
    Commented Mar 14, 2017 at 20:34
  • I just edited it above..
    – cfdphd
    Commented Mar 14, 2017 at 21:08

1 Answer 1

Reset to default
1

Exactly as predicted. There is a primary key on the ID column and it is ONLY on the ID column. This means that there can be only one row in the table with that ID. The result is each ID can only own a maximum of one stock.

You need to remove the existing primary key and create one that uses BOTH the ID column AND the SYMBOL. This will allow multiple rows with the same ID and multiple rows with the same SYMBOL, but will mandate only one row with any unique combination of the two fields. If you don't know how, here's a link to something that will get you going.

https://stackoverflow.com/questions/8859353/alter-table-to-add-a-composite-primary-key

BTW, don't forget about adding an update clause as discussed earlier.

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

Improve this answer
2
  • thanks. this makes sense. Will look up your link.
    – cfdphd
    Commented Mar 15, 2017 at 2:03
  • I am doing this int phpliteAdmin and getting an ERROR
    – cfdphd
    Commented Mar 15, 2017 at 2:17

You must log in to answer this question.

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