Mysql – Constrain/limit certain records in sql table definitions

constraintMySQL

Let's say I have posts on which users can leave a comment. And I want to limit the amount of comments to a certain post to 10, is it possible to add such constraint/restriction to the table?
My guess is not, but just want to make sure there is nothing I am missing.

----------------------------------
| user_id | post_id | comment_id |
----------------------------------
| 1       | 1       | 1          |
| 1       | 1       | 2          |
| 1       | 1       | 3          |
| 1       | 1       | 4          |
| 1       | 1       | 5          |
| 1       | 1       | 6          |
| 1       | 1       | 7          |
| 1       | 1       | 8          |
| 1       | 1       | 9          |
| 1       | 1       | 10         |
----------------------------------

So adding another comment for user 1 on post 1 should not be possible in the above case. But the table is just an example, if I can achieve this with a different database schema, don't hesitate to answer. I simply want to limit the amount of comments for a user on a certain post.

I work with MySql through Doctrine 2. I can of course build in such limitations in my abstraction layer, but I am curious if such constraints are possible in MySql (or maybe MongoDB) directly.

Best Answer

To have maximum 10 rows for a user_id and post_id and given that MySQL has not even implemented CHECK constraints, only PRIMARY KEY, UNIQUE and FOREIGN KEY constraints, it might be easier to enforce this in the application layer.

Of course it's better to have it in the database, as applications may have bugs and the constraint would have to be implemented in every application that uses the database. So, if you want to enforce this constraint in MySQL, the only way possible I think is to add another comment_no column (not to be confused with the comment_id column you may already have and may already be used, e.g. may be unique in the table or unique per post. This comment_no will be unique per post and user and will allow values only from 1 to 10.) The applications that insert data in the table would still have to provide this numbering (1 to 10). But the database would never allow more than 10 rows per user and post, using an additional table and the following FOREIGN KEY and UNIQUE constraints:

-- auxiliary table
CREATE TABLE number_of_comments_allowed
  ( comment_no TINYINT NOT NULL,
    CONSTRAINT number_of_comments_allowed_PK
        PRIMARY KEY (comment_no)
  ) ;

-- values needed in the auxialry table
INSERT INTO number_of_comments_allowed
  ( comment_no)
VALUES
  (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ;

CREATE TABLE comment
  ( user_id INT NOT NULL,
    post_id INT NOT NULL,
    comment_id INT NOT NULL,
    comment_no TINYINT NOT NULL,
    -- PRIMARY KEY (whatever),
    -- other UNIQUE and FOREIGN KEY constraints
    UNIQUE (post_id, user_id, comment_no),
    FOREIGN KEY (comment_no)
        REFERENCES number_of_comments_allowed (comment_no)
  ) ;

The only other thing that needs taken care of is to restrict access to the auxiliary table once it has been created and populated, so no user/session (except an admin) can insert, delete or update it.

The number (10) of rows allowed per user and post can be easily increased (or decreased) later by adding rows (or removing rows - but this may need to delete some rows from the comment table first) from the auxiliary table.

Related Question