0

I'm having a hard time to explain this so let me illustrate it with an exemple.

I have a table for comments and it looks like so:

CREATE TABLE comments (
    id INT UNSIGNED AUTO_INCREMENT,
    parent_id INT UNSIGNED,
    article_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    ip VARCHAR(255) NOT NULL,
    submitted_at TIMESTAMP DEFAULT NOW(),
    comment TEXT,
    PRIMARY KEY (id),
    CONSTRAINT fk_com_parent
        FOREIGN KEY (parent_id)
        REFERENCES comments (id)
    CONSTRAINT fk_com_article
        FOREIGN KEY (article_id)
        REFERENCES articles (id)
    CONSTRAINT fk_com_user
        FOREIGN KEY (user_id)
        REFERENCES users (id)
);

Now it makes sense that if parent_id is set, child and parent should share the same article_id.

Answers to a comment must be on the same article. So is there a way to contraint this?

  • probably could be done with triggers. Is it necessary to duplicate. recursive CTEs (MySQL-8.0/MariaDB-10.2) mean these are derivable in a query. – danblack Mar 7 '19 at 5:19
  • if parent_id is set, child and parent should share the same article_id. Specify the field you means child. Answers to a comment must be on the same article. So is there a way to contraint this? If so the logic must be that the record have arcticle_id IS NULL when parent_id IS NOT NULL. And the article ID must be obtained from its parent for that record. – Akina Mar 7 '19 at 6:09
  • @akina That could do. How could I enforce that? – Buzut Mar 7 '19 at 6:39
  • @danblack, I'm going to give a look at triggers then, thanks for pointing that out! – Buzut Mar 7 '19 at 6:40
1

Answers to a comment must be on the same article. So is there a way to contraint this?

If so the logic must be that the record have arcticle_id IS NULL when parent_id IS NOT NULL. And the article ID must be obtained from its parent for that record.

SELECT ... , 
       CASE WHEN t1.parent_id IS NULL 
            THEN t1.article_id 
            ELSE t2.article_id 
       END article_id, 
       ...
FROM table t1
LEFT JOIN table t2
    ON t2.user_id = t1.parent_id
...

So if parent_id is set, the article_id is taken from parent's record, and the value from current record is simply ignored.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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