There is a table for user comments, I create it as shown below. It is necessary that the comment id and id of the person who sent the comment do not repeat, actually make them unique, but comment id are still assigned in order (1, 2, 3, 4 ... n) ignoring uniqueness for the user, what's the problem?

CREATE TABLE comments( id_comment MEDIUMINT NOT NULL AUTO_INCREMENT, id_user INTEGER NOT NULL, id_send_user integer NOT NULL, author CHAR(32) NOT NULL, date CHAR(18) NOT NULL, message TEXT NOT NULL, img CHAR(50) NOT NULL, PRIMARY KEY (id_user,id_comment) ) ENGINE=MyISAM;

It seems to have solved the problem until everything works, replaced PRIMARY KEY ( id_user,id_comment ) with PRIMARY KEY ( id_send_user,id_comment )

  • one
    UNIQUE INDEX whatever (id_comment, id_user)? - neoascetic
  • no, "PRIMARY and whatever indices are equivalent and one of them can be removed." - BraGB

2 answers 2

In the table structure, such an order of auto-assignment by identifier cannot be set. You can either use the trigger or change the request to add a new record:

 SET @id_user = 1; SELECT MAX(id_comment) FROM comments WHERE id_user = @id_user INTO @id_last; INSERT INTO comments VALUE (IFNULL@id_last, 0) + 1, @id_user, .......);
  1. We @id_last - the maximum id_comment value for the given @id_user .
  2. Insert a record.

The trigger code should look something like this:

 CREATE TRIGGER trg_comments_bi BEFORE INSERT ON comments FOR EACH ROW BEGIN DECLARE _id_last INT(11) DEFAULT NULL; IF ISNULL(NEW.id_comment) THEN SELECT MAX(id_comment) FROM comments WHERE id_user = NEW.id_user INTO _id_last; SET NEW.id_comment = IFNULL(_id_last, 0) + 1; END IF; END;

    AUTO_INCREMENT take away id_comment

    • and how will it assign an id? removed A_I, now all id messages are zero and one message replaces another in the database - BraGB
    • UNIQUE INDEX, and you can AUTO_INCREMENT field add id - erofeev