Constraint self referencing foreign key to limit nesting (SQL, Sequelize)

Question

I am trying to make a table for example which consists of this structure:

<Comments>
id(PK, int)    comment_id(FK, int)    body(text)
1              null                   "This is a comment"
2              1                      "This is a nested comment"
3              2                      "This is a nested nested comment"

The above example represents the advantages of using self referencing tables - which I want to eliminate at a certain level.

The id: 3 of the comment is referencing to comment_id=2 comment while id=2 is referencing comment_id=1.

I want to limit this nested-referencing to a single level with the ability to add a specific constraint which checks and establishes a value equal to the first level of nest.

To make it more clear, the id=3 at this point instead of referencing comment_id=2 should reference the comment_id=1 and look like this:

<Comments>
id(PK, int)    comment_id(FK, int)    body(text)
1              null                   "This is a comment"
2              1                      "This is a nested comment"
3              1                      "This is a nested nested comment"

I want to keep the back-end as less busy as possible in handling such situations and just by providing a reference of comment_id=2 in my create query I would like it to automatically point it to id=2's reference of comment_id=1 instead of comment_id=2

Apart from me using Sequelize in NodeJS and being able to constraint the data in the back-end routes, I would like to understand how would this be possible to achieve with sql (or sequelize) to keep the data integrity flow linear.

score 0 · Answer 1 · answered Oct 21 '18 at 18:44

0

You do this by having another table. If you comments are referencing something, then you can use that table. Otherwise:

create table comments (
    commentId int primary key,
    . . .
);

create table commentLines (
    commentLineId int primary key,
    commentId int references comments(commentId),
    comment varchar(255)
);

answered Oct 21 '18 at 18:44

Gordon Linoff

1,242,037
58
646
786

This does seem logical and I have thought about this solution, but is it the only way of achieving this? As I would like to keep the idea of self-referencing table. – moodseller Oct 21 '18 at 18:51
@moodseller . . . I think this does a better job of expressing the data model that you want to express. – Gordon Linoff Oct 21 '18 at 19:04

Constraint self referencing foreign key to limit nesting (SQL, Sequelize)

1 Answers1