I have an application in Elixir with news, which the user should be able to comment. Here my migration to create the comment functionality:
def change do
create table(:news_comments, primary_key: false) do
add :id, :uuid, primary_key: true
add :news_id, references(:news, type: :uuid, on_delete: :nilify_all)
add :comment_id, references(:comments, type: :uuid, on_delete: :nilify_all)
add :parent_comment_id, references(:comments, type: :uuid, on_delete: :nilify_all)
end
end
I want to allow sub comments for comments and would do this with a constraint. A user can comment a news by sending the news_id and comment_id. I want to allow to send sub comments, which I want to allow only for the same news. So I need an unique constraint for news_id and comment_id, no problem till now. But when a user sends a sub comment I want to assure, that the sub comment has the news_id and parent_comment_id, which are already saved in the database. Lets say I got a news_comment with news_id = 1 and comment_id = 1, the database should only allow inserts with news_id = 1 and parent_comment_id = 1. I don't want to handle this case in the code, because I think the database can handle it. Would be no problem with a stored procedure, but there should be a way to achieve this with a constraint, but no idea how.
The next case the constraint should handle is to allow only sub comments for comments without a parent_comment_id, because i don't want to allow sub comments for sub comments, like commenting on youtube. Multiple constraints are ok, just need a solution, else I need to handle it inside my code with some database requests which I would like to avoid.
Thanks
def up do
execute "ALTER TABLE news_comments
ADD CONSTRAINT uc_news_comment UNIQUE (news_id, comment_id);"
execute "ALTER TABLE news_comments
ADD FOREIGN KEY (news_id, parent_comment_id)
REFERENCES news_comments(news_id, comment_id);"
end
Made the job.