Re: Denormalization to allow use UNQIUE index?

[Peter Brawley]

It's not a denormalisation issue.

You say the relation of post to comment is 1:many. Then your comment FK is misformed, referencing the comment table instead of the post table.

You need ...

CREATE TABLE post ( 
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  body TEXT NOT NULL, 
  issue_id INT UNSIGNED NOT NULL, 
  PRIMARY KEY (id) 
); 

CREATE TABLE comment ( 
  id int unsigned NOT NULL AUTO_INCREMENT, 
  post_id int unsigned NOT NULL, 
  body text COLLATE utf8_bin NOT NULL, 
  PRIMARY KEY (id), 
  FOREIGN KEY (post_id) REFERENCES post(id) 
);

insert into post set body='abc',issue_id=1;
insert into comment values(1,1,'def'),(2,1,'ghi');

select p.id,p.body as post,p.issue_id,c.id,c.post_id,c.body as comment 
from post p 
join comment c on p.id=c.post_id;
+----+------+----------+----+---------+---------+
| id | post | issue_id | id | post_id | comment |
+----+------+----------+----+---------+---------+
|  1 | abc  |        1 |  1 |       1 | def     |
|  1 | abc  |        1 |  2 |       1 | ghi     |
+----+------+----------+----+---------+---------+