0

So I've tried to do this a number of ways. Basically I'm given the following instructions:

Using the blog database (use either a single-line or a multi-line SQL statement):

Write a SQL ALTER TABLE statement that adds a post_id column to the blog.comments table. This new column should be an INTEGER data type with a max. size of 3 digits, UNSIGNED, it should be NOT NULL and it should work as a FOREIGN KEY that uses as a reference the id column of the blog.posts table.

I have no issue adding the column, it's getting the foreign key to work that's stumping me. I used the following code:

ALTER TABLE blog.comments
ADD COLUMN post_id INT(3) UNSIGNED NOT NULL;

ALTER TABLE blog.comments
ADD FOREIGN KEY (post_id) REFERENCES posts(id);

And I keep getting the following error when I submit it:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (b log.#sql-628_2a, CONSTRAINT #sql-628_2a_ibfk_1 FOREIGN KEY (post_id) REFERENCES posts (id))

I have tried several different versions but I can't get it to work.

Sarah
  • 55
  • 1
  • 5
  • Possible duplicate of [Add Foreign Key to existing table](https://stackoverflow.com/questions/10028214/add-foreign-key-to-existing-table) – Jonathan Gagne Dec 02 '18 at 04:59
  • I also tried all of those and none of them worked for me. – Sarah Dec 02 '18 at 05:08
  • 1
    @Sarah do `SHOW CREATE TABLE posts` and post the results of this query to the question. – Madhur Bhaiya Dec 02 '18 at 05:33
  • @Sarah there is a chance that the `comments` table has data already, which may cause issues here. Since you have not defined the `post_id` column to be nullable. Its default value will most likely be 0. And there may not be a zero value in `id` column of the `posts` table. – Madhur Bhaiya Dec 02 '18 at 05:38
  • @Sarah check this answer: https://stackoverflow.com/a/53099922/2469308 for possible ways to resolve this. – Madhur Bhaiya Dec 02 '18 at 05:38
  • Please read & act on [mcve]. Googling 'site:dev.mysql.com more information on foreign key error' we find [this page & its links](https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html) & this quote: "For InnoDB tables, you can obtain a detailed explanation of the most recent InnoDB foreign key error in the MySQL Server, by checking the output of SHOW ENGINE INNODB STATUS." – philipxy Dec 02 '18 at 10:52
  • Hi. (Obviously:) This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Dec 02 '18 at 10:53

0 Answers0