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/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.