Two foreign key column constraint that reference the same table

Phoenix Forum Questions / Help
1

I am trying to set up a table having two columns referencing the same table to simulate a link between two items.
Migration is as follows:

def change do
    create table(:linked_item, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :item_1_id, references(:item, on_delete: :nothing, type: :binary_id)
      add :item_2_id, references(:item, on_delete: :nothing, type: :binary_id)

      timestamps()
    end

    create index(:linked_item, [:item_1_id])
    create index(:linked_item, [:item_2_id])
end

def change do
    create table(:item, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :name, :string

      timestamps()
    end
end

My question is the following:

  • Is there a way, like a constraint, to have it that it checks if item_1_id and item_2_id is already linked up?
    So the following combination will not happen during a POST request:
    { item_1_id: 1, item_2_id: 2 }
    { item_1_id: 2, item_2_id: 1 }

I am checking on having a composite key for this but I don’t know if this is the way to go.
Appreciate any feedback on this!

2

Not sure what you are after. But how I would solve it is to make sure the lowest id (from items) would always go in item_1_id. and then have a simple unique constraint on create

unique_index(:linked_item, [:item_1_id, :item_2_id])

That is the most simple way that I can think of.

3

This looks like a good case for a composite / join table. Thats what I usually do and leveraging the primary key.

And then you can get rid of the :id column but, you might want to have a check or unique constraint to avoid duplicates.

def change do
    create table(:linked_item, primary_key: false) do
      add :item_1_id, references(:item, on_delete: :nothing, type: :binary_id, primary_key: true)
      add :item_2_id, references(:item, on_delete: :nothing, type: :binary_id, primary_key: true)
      timestamps()
    end
   create unique_index(:linked_item, [:item_1_id, :item_2_id])
end
4

This would have been a simple and perfect solution but unfortunately I am working with UUIDs.

5

This is almost the same as the solution by andreaseriksson and I agree that this is the way to go but I don’t know how to go about ensuring that duplicates don’t occur with UUIDs as my keys.
I am looking at maybe making use of ULIDs but I am not sure if this is an efficient type of encoding and if elixir(ecto) natively supports this. Will try to investigate further.

6

Is there some other conceptual way to sort them? By inserted_at? I mean, I find it unlikely that you just randomly but them in the table

7

The UUID is unique in your items table, and you would not be able to create a link without both rows being in the table, the only case I’m not sure about is the cross link, you’ll have to test it out.

That’s where the unique or check constraint comes in, I’m not big on unique constraint in general when working with join tables but there might be situations like these where they are necessary.