2

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.

2
  • 1
    Is comment_id the primary key of the table? Sep 30, 2015 at 13:43
  • I would say this is a join table, so user_id, post_id and comment_id are together a composite key. But the table is just an example, if I can achieve this with a different database schema, don't hesitate to answer :)
    – Wilt
    Sep 30, 2015 at 13:44

1 Answer 1

3

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.

3
  • Really smart. This made me think... Could it be possible to achieve similar result without the extra table by adding an enumeration that allows values 1 to 10 and add unique constraint on enum, user_id and post_id?
    – Wilt
    Oct 1, 2015 at 15:35
  • 1
    I suppose you could. I consider enum types in MySQL to be evil but it may work. See: 8 Reasons Why MySQL's ENUM Data Type Is Evil Oct 1, 2015 at 15:39
  • Evil? Can you elaborate or share a reference?
    – Wilt
    Oct 1, 2015 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.