CODEWITHSUNDEEP
phpmysqlsql
Nikki Mather
Nikki Mather

Reputation: 1148

Allow duplicate in table

I'm relatively new to databases, database architecture and MySQL in general, so forgive me if my method isn't optimal. I have created a table called comments, for which i want to store the users id in to the column post_id which works fine as it stands. This table's sole purpose is to store messages posted on any given users profile and some other related information.

However i want to allow duplicate entries so i can read the comments table and look for a certain users id, then take the column comments from the comments table and display them on the users profile where the id is matched.

I'd do this by doing an INNER JOIN on comments and user_info, specifically the post_id from comments and id from user_info.

When posting the information to the database from a users profile, i get the below error

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '59' for key 'post_id'

user_info

'user_info', 'CREATE TABLE `user_info` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `username` varchar(12) COLLATE utf8_unicode_ci NOT NULL,\n  `pass` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,\n  `joined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  PRIMARY KEY (`id`),\n  UNIQUE KEY `username` (`username`)\n) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'

comments

'comments', 'CREATE TABLE `comments` (\n  `post_id` int(11) DEFAULT NULL,\n  `comment` text COLLATE utf8_unicode_ci,\n  `date_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  UNIQUE KEY `post_id` (`post_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'

Let me know if i'm missing anything that would potentially help you answer the question.

Upvotes: 0

Views: 689

Answers (5)

spencer7593
spencer7593

Reputation: 108510

If a row in comment is to be related to a row in user_info, and one user_info can have zero, one or more comment, and comment is related to exactly one user_info...

Then you'd store the value of the id column (PRIMARY KEY) column from the user_info, as a value in the comment table.

With the InnoDB engine, you can also define a FOREIGN KEY constraint.

For example:

ALTER TABLE `comment` ADD 
  `user_info_id` INT COMMENT 'FK ref user_info.id' ;

ALTER TABLE `comment` ADD
  CONSTRAINT `FK_comment_user_info` FOREIGN KEY (`user_info_id`)
  REFERENCES `user_info` (`id`)
  ON UPDATE CASCADE
  ON DELETE CASCADE

Leave current the post_id column as a unique key (or change it to a PRIMARY KEY) on the table.

Note that a JOIN operation will return multiple rows, when there are multiple comments for a given user_info 

  FROM `user_info` u
  LEFT
  JOIN FROM `comment` c
    ON c.user_info_id = u.id
 WHERE u.id = 42

If a comment can be related to more than one user_info, then that would be a many-to-many relationship, and the normative pattern to implement that would be to add a third association table, a row in the association table would have foreign key references to both the user_info table and the comment table.

Upvotes: 0

Ujjal Suttra Dhar
Ujjal Suttra Dhar

Reputation: 846

You have to check your query once again. Because the problem may exits on your select query. You have to remove unique index from post_id column of comments table.

Upvotes: 0

ElendilTheTall
ElendilTheTall

Reputation: 1452

You need three tables: users, comments, and commentlist.

The users table just holds user details. The comments table just holds comments. Commentlist has 3 columns: an id, which you won't really use, user id, and comment id.

You can then have multiple user id entries in the commentlist table, all relating to an individual comment id.

Upvotes: 0

user4035
user4035

Reputation: 23759

You need to drop unique index on post_id:

ALTER TABLE `comments` DROP INDEX post_id;

Then you can create a non-unique index if necessary:

ALTER TABLE `comments` ADD INDEX `post_id` (`post_id`);

Also your comments table lacks primary key. Create an auto increment integer column: comment_id, that would identify the record.

Upvotes: 1

na-98
na-98

Reputation: 889

post_id should be an auto increment field as primary key in columns table that uniquely identifies a tuple (row). You should add another column called user_id that stores the id from user_info table. You should then add an index to user_id column in comments table for faster search.

Upvotes: 0

Related Questions