Partial Index with a bit more complex where

Questions / Help
, ,
1 
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 an insert with the parent_comment_id only if there is already a dataset with the correct news_id and comment_id available. For example news_id = 1 and comment_id = 1 is in the database, so only inserts with news_id=1 and parent_comment_id = 1 are allowed. How would the index look like?
Got only this till now: create index(:news_comments, [:news_id, :comment_id, :parent_comment_id], [where: "????", name: :news_parent_comment_unique_index, unique: true])

Thanks

2

This is handled by the references bit which creates a foreign key constraint, this isn’t a constraint you handle with indices.

3

I think I need to explain my usecase a bit more precisly:
Lets say I got a dataset with news_id = 1 and comment_id = 1 and an other dataset with news_id = 2 and comment_id = 2. I don’t want to allow inserts with news_id=1 and parent_comment_id = 2. Allowed should be only an insert with news_id=1 + parent_comment_id = 1 and news_id=2 + parent_comment_id = 2.

4

Have a look at the Ecto documentation for constraint. This should allow you to do what you want.

5 
create constraint("users", :price_must_be_positive, check: "price > 0")   
create constraint("size_ranges", :no_overlap, exclude: ~s|gist (int4range("from", "to", '[]') WITH &&)|)

is what the doc says. How to do a check if there is a dataset with news_id = news_id and comment_id = parent_comment_id already in the database?

6

The constraint only needs to say that comment_id = parent_comment_id. references creates a foreign key constraint, and the foreign key constraint will ensure that the ids are in the database.

From a design perspective I’m not entirely sure why this isn’t just one column though if they’re always equal.

7

Ok, once again. I got news, I want to comment news. So a comment is beeing created and attached to the news via the news_comments table. Now someone wants to comment the created comment. I want to assure, that the comment for the already created comment points to the same news and and as parent_comment the created comment. The Reference allows to add a comment for one news and one parent_comment. If I insert a dataset with news_id, comment_id and parent_id I need to ensure, that there is a dataset news_comments with the given news_id and comment_id, which is inserted as parent_comment_id.

3 Tables -> news, news_comments and comments. To comment a comment, the news_id has to match and the parent_id has to point to the comment, which is pointing via news_id to the news. If I insert news_id = 1, parent_comment_id = 1 i need to assure, that there is a dataset in news_comments with news_id = 1 and comment_id = 1. The reference checks only if there is data with news_id = 1 and data with comment_id = 1, not if there is data with news_id = 1 AND comment_id = 1.

Pseudocode:

 subcomment = comment_id X, news_id 1, parent_comment_id 1   
 IF news_comments has DATA
    WHERE news_id = subcomment.news_id AND comment_id = 
            subcomment.parent_comment_id
    INSERT subcomment
 ELSE error

`

8

Looks like the equivalent raw SQL would be roughly:

INSERT INTO news_comments (
  news_id, 
  comment_id, 
  parent_comment_id
) SELECT
  nc.news_id,
  :comment_id,
  nc.comment_id
FROM news_comments AS nc 
WHERE 
    nc.news_id = :news_id
AND nc.comment_id = :parent_comment_id
GROUP BY nc.news_id, nc.comment_id;
9

I don’t want to handle it in the code. I could of course use Repo.get_by to check it or use a query, but I thought i could avoid it with a constraint or index and just send the changeset via Repo.insert or Repo.update to the database.

10 
CREATE TABLE news_comments (
  news_id int,
  comment_id int,
  parent_comment_id int,
  PRIMARY KEY (news_id, comment_id),
  FOREIGN KEY (news_id, parent_comment_id) REFERENCES news_comments(news_id, comment_id)
);

INSERT INTO news_comments (news_id, comment_id) VALUES (1, 1);
INSERT INTO news_comments (news_id, comment_id) VALUES (2, 2);
INSERT INTO news_comments (news_id, comment_id, parent_comment_id) VALUES (1, 3, 1);
INSERT INTO news_comments (news_id, comment_id, parent_comment_id) VALUES (2, 4, 2);
INSERT INTO news_comments (news_id, comment_id, parent_comment_id) VALUES (1, 5, 4);

\i create_x.sql
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
2018-05-16 13:09:24.800 EDT [3039] ERROR:  insert or update on table "news_comments" violates foreign key constraint "news_comments_news_id_fkey"
2018-05-16 13:09:24.800 EDT [3039] DETAIL:  Key (news_id, parent_comment_id)=(1, 4) is not present in table "news_comments".
2018-05-16 13:09:24.800 EDT [3039] STATEMENT:  INSERT INTO news_comments (news_id, comment_id, parent_comment_id) VALUES (1, 5, 4);
psql:create_x.sql:13: ERROR:  insert or update on table "news_comments" violates foreign key constraint "news_comments_news_id_fkey"
DETAIL:  Key (news_id, parent_comment_id)=(1, 4) is not present in table "news_comments".
1 Like
11

What About
INSERT INTO news_comments (news_id, comment_id, parent_comment_id) VALUES (1, 3, 2);
? Thats the case i want to avoid.

12 
CREATE TABLE news_comments (
  news_id int,
  comment_id int,
  parent_comment_id int,
  PRIMARY KEY (news_id, comment_id),
  FOREIGN KEY (news_id, parent_comment_id) REFERENCES news_comments(news_id, comment_id)
);

INSERT INTO news_comments (news_id, comment_id) VALUES (1, 1);
INSERT INTO news_comments (news_id, comment_id) VALUES (2, 2);
INSERT INTO news_comments (news_id, comment_id, parent_comment_id) VALUES (1, 3, 2);

# \i create_x.sql
CREATE TABLE
INSERT 0 1
INSERT 0 1
2018-05-16 13:39:50.300 EDT [3039] ERROR:  insert or update on table "news_comments" violates foreign key constraint "news_comments_news_id_fkey"
2018-05-16 13:39:50.300 EDT [3039] DETAIL:  Key (news_id, parent_comment_id)=(1, 2) is not present in table "news_comments".
2018-05-16 13:39:50.300 EDT [3039] STATEMENT:  INSERT INTO news_comments (news_id, comment_id, parent_comment_id) VALUES (1, 3, 2);
psql:create_x.sql:11: ERROR:  insert or update on table "news_comments" violates foreign key constraint "news_comments_news_id_fkey"
DETAIL:  Key (news_id, parent_comment_id)=(1, 2) is not present in table "news_comments".
1 Like
13

Thanks :slight_smile: will check this one. Never used a FOREIGN KEY CONSTRAINT this way. Really nice. I normaly would add the parent_comment_id to the comment table, but to avoid the need of a comment controller in my phoenix app I tried it this way. This way I can avoid includes in my json-api view and add all comments as relationships. Thanks a lot, you showed me a new way to use FK contraints.