The immediate problems here are in the
INSERT statement. You're trying to add two rows to the table, and the first row has no values. So what user does it belong to? The foreign key constraint on this table requires all of its rows to have a "parent" user in the
users table, and I'm pretty sure none of those users have
'' as their ID.
There are some other issues with the second row you're trying to insert. First of all, you're trying to insert three strings, but two of the columns are numeric types; you should be giving them numeric data. Otherwise, depending on what
sql_mode is set in your database, you could get an error or end up with the wrong values entirely!
Odds are that
'33' will be handled just fine; MySQL is smart enough to convert that string into the integer value
33. But you're trying to insert the letter
'l' into shares; what numeric value is that supposed to represent? MySQL isn't smart enough to save you on that count; it's going to either raise an error, or insert the value
0 with a warning, depending on the
Even if you get rid of the blank row and just insert something reasonable like
(33, 'goog', 1), it will only work provided there is a user in the
users table with an
id value of
33. That's the purpose of the foreign key constraint.
The screenshots you provided don't show how you defined your keys on the table, so I can't say whether you've made an error there; try fixing the
INSERT statement first. If that gives you a different error, you might want to ask another question.
There are some other things that you could improve about your table design. First, never name a table
table. We know it's a table -- what else would it be? Give it a name that describes what it holds so that when we refer to this table in a query, we can recognize something about its contents. This table exists to track users' portfolios, so it would be sensible to call it something like
Second, try to think of a better name for the
id column in your table. In the
users table, it's not so bad to have a column named
id when we know it represents the ID of a user. It's assumed that the ID corresponds to the table. That doesn't work when you want to represent that same value in another table; you should be clear that this is the ID of another table by naming it something explicit like
user_id. (Some people prefer this even for the column in the
users table, just to avoid any potential for confusion!)
Now, I don't know what you used as a primary key for your table, but one option would be to use a combination of
id (the user's ID) and
symbol. Another option would be to have a separate
id column to use as what's called a surrogate key, which serves no purpose outside the database. A surrogate key exists purely as a way to uniquely identify one row of a table. In the table
users, the column
id is a surrogate key.
One advantage of using a surrogate key is that if a user decides to change their name, you only have to change it once; if you used their name as the key, you'd also have to change it in any tables where their name appeared as a foreign key linking the tables together. Done properly, surrogate keys never have to change. They can also be generated automatically with the
AUTO_INCREMENT column attribute so you don't have to worry about setting them, either. You don't need to use a surrogate key for this problem set, but it's not a bad habit to get into if you plan to use SQL for any real-world projects.