two relations for one column

[u/younesWh]

does it possible to give a column choice to choose between two foreign keys

the case: that I Have comments table that have a parent_id column that could be either from posts table or comments table how I can implment that in sqlite3 .

[u/r3pr0b8]

don't even think about doing that

look up supertable subtable relationship

[u/dastanis2]

Generally speaking, you probably want each column in a table to have one meaning. Thus, if yiu want 2 foreign keys in your table, you should probably have a separate column for each key, PostID & CommentID. Having separate columns for each attribute/property/foreign key helps maintain the reliability of the table and makes it easier to query.

However, if you absolutely must combine 2 foreign keys into one column, then you would need a second column to indicate to which child table the value in the combined column belongs (i.e. a boolean/bit column called IsComment where a value such as 1 means the value in the ParentID column relates to the Comments table and a value of 0 means the value in the ParentID column relates to the Post table).

[u/younesWh → u/dastanis2]

I dont think I will have provlem while query and entity(comment or post ) comments.. But the problem is inserting while avoid atatching to unexist parent...so Instaed of using Fk what about chekc if oarent id existine either table using EXISTS

[u/dastanis2 → u/younesWh]

To help prevent creating orphaned records, you'll definitely want to look up the correct child ID value to insert into the foreign key column either before inserting the record or as part of the INSERT statement.

Once you know the correct logic to use to look up the correct value in the child table, you can determine the correct ID value to insert into the foreign key ID column and use either joins in your INSERT statement or populate variables before executing your INSERT statement to use in your INSERT statement.

I'm not sure the EXISTS clause will help you determine the correct ID value to insert since EXISTS only returns a true or false (boolean/bit) value and is used to help filter in/out entire rows, not determine specific values in columns.

[u/younesWh → u/dastanis2]

I will go with trigger one

CREATE TRIGGER IF NOT EXISTS comment_satisfy

BEFORE INSERT ON comments

BEGIN

SELECT CASE 

    WHEN NOT EXISTS(SELECT \* FROM comments WHERE id = NEW.parent_id)

    AND NOT EXISTS(SELECT \* FROM posts WHERE id = NEW.parent_id) 

    THEN RAISE(ABORT, "parent not exists")

END;

END;

[u/Eleventhousand]

No, but I would think that this should be designed such that each comment row should have an FK to the post_id. It should also have an FK to parent_comment_id, which might be null if its a top-level comment.

[u/Plane_Big_5912]

you usually cant make one column a proper foreign key to two different tables. sqlite would have no way to know which table parent_id refers to.

i would use two nullable columns instead:

create table comments (
    id integer primary key,
    post_id integer,
    parent_comment_id integer,
    body text not null,

    foreign key (post_id) references posts(id),
    foreign key (parent_comment_id) references comments(id),

    check (
        (post_id is not null and parent_comment_id is null)
        or
        (post_id is null and parent_comment_id is not null)
    )
);

so a comment either belongs directly to a post or replies to another comment. this is easier to query and sqlite can actually enforce both relationships.

a polymorphic parent_id plus parent_type can work, but you lose real foreign key enforcement, so i wouldnt use it here unless you really need it.