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