1

In pset 7 I need to connect the ID from my table to the ID in users. But when I try to insert rows into my table I get the following error:

SQL query:

INSERT INTO `pset7`.`table` (
  `id`,
  `symbol`,
  `shares`
)
VALUES (
  '', '', ''
),(
  '33', 'goog', 'l'
)

MySQL said:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`pset7`.`table`, CONSTRAINT `table_ibfk_1` FOREIGN KEY (`id`) REFERENCES `users` (`id`))

Is it possible that I have the wrong indexes or primary keys in my table?

# | Name   | Type         | Collation       | Attributes | Null | Default | Extra
--+--------+--------------+-----------------+------------+------+---------+-------
1 | id     | int(10)      |                 | UNSIGNED   | No   | None    |
2 | symbol | varchar(255) | utf8_unicode_ci |            | No   | None    |
3 | shares | int(11)      |                 |            | No   | None    |

I would really appreciate any help. I am banging my head against the wall.

1 Answer 1

1

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 sql_mode.

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.

Other issues

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 portfolios.

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.

You must log in to answer this question.

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