How to debug `Ecto.Migration` constraints?

Questions / Help
1

Hi,

Are there any general steps to debug constraints in migrations?

The problem, which has a very obvious solution probably: Enforcing the format of phone numbers (such as “5551234567”) in the schema works fine using Ecto.Schema.validate_format/4 with the ~r(/^\d{10}$) regex, and decided to add a constraint to the database as well, but it throws an exception.

The migration:

defmodule ANV.Repo.Migrations.CreatePhoneNumbers do
  use Ecto.Migration

  def change do

    create table(:phone_numbers, primary_key: false) do

      add :id, :uuid, primary_key: true
      add :phone_number, :string

      timestamps()
    end

    create constraint(
      :phone_numbers,
      :phone_number_must_be_a_ten_digit_string,
      check: "phone_number ~ '^\d{10}$'"
      
      # This variant doesn't work either
      # check: "phone_number ~ $$^\d{10}\Z$$"
    )
  end
end

I can’t find a hint about what I’m doing wrong in the error below, and adding Ecto.Changeset.check_constraint/3, as suggested below, won’t show any changeset errors either, but the update will still fail.

iex(2)> Repo.preload(admin, :phone_numbers) \
...(2)> |> Ecto.Changeset.change() \
...(2)> |> Ecto.Changeset.put_assoc(:phone_numbers, 
[%{phone_number: "5551234567"}]) \
...(2)> |> Repo.update()

[debug] QUERY OK source="phone_numbers" db=1.0ms queue=1.5ms
SELECT p0."id", p0."phone_number", p0."user_id", p0."inserted_at", p0."updated_at", p0."user_id" FROM "phone_number
s" AS p0 WHERE (p0."user_id" = $1) ORDER BY p0."user_id" [<<151, 69, 143, 150, 60, 216, 64, 125, 152, 42, 217, 7, 2
15, 117, 58, 30>>]
[debug] QUERY OK db=0.2ms
begin []
[debug] QUERY ERROR db=4.7ms
INSERT INTO "phone_numbers" ("phone_number","user_id","inserted_at","updated_at","id") VALUES ($1,$2,$3,$4,$5) ["55
51234567", <<151, 69, 143, 150, 60, 216, 64, 125, 152, 42, 217, 7, 215, 117, 58, 30>>, ~N[2019-09-05 21:39:43], ~N[
2019-09-05 21:39:43], <<217, 77, 187, 38, 201, 82, 78, 117, 156, 144, 64, 248, 249, 55, 227, 70>>]
[debug] QUERY OK db=0.3ms
rollback []
** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * phone_number_must_be_a_ten_digit_string (check_constraint)

If you would like to stop this constraint violation from raising an
exception and instead add it as an error to your changeset, please
call `check_constraint/3` on your changeset with the constraint
`:name` as an option.

The changeset has not defined any constraint.

    (ecto) lib/ecto/repo/schema.ex:687: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
    (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2 
    (ecto) lib/ecto/repo/schema.ex:672: Ecto.Repo.Schema.constraints_to_errors/3
    (ecto) lib/ecto/repo/schema.ex:274: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (ecto) lib/ecto/association.ex:662: Ecto.Association.Has.on_repo_change/5
    (ecto) lib/ecto/association.ex:432: anonymous fn/8 in Ecto.Association.on_repo_change/7
    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/association.ex:428: Ecto.Association.on_repo_change/7
    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/association.ex:392: Ecto.Association.on_repo_change/4
    (ecto) lib/ecto/repo/schema.ex:837: Ecto.Repo.Schema.process_children/5
    (ecto) lib/ecto/repo/schema.ex:914: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
    (ecto_sql) lib/ecto/adapters/sql.ex:890: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection) lib/db_connection.ex:1415: DBConnection.run_transaction/4

Also reconfigured PostgreSQL logging (according to 19.8. Error Reporting and Logging) to log everything, but the only related lines are also unhelpful (or I’m not seeing something that is obvious):

2019-09-05 22:26:33.060 UTC [15340] ERROR:  new row for relation "phone_numbers" violates check constraint "phone_number_must_be_a_ten_digit_string"
2019-09-05 22:26:33.060 UTC [15340] DETAIL:  Failing row contains (e4ce295a-9a7b-4a38-8e24-6f7191f711ce, 5551234567, 98b5b906-16f3-4ea0-875b-f8d4539efe06, 2019-09-05 22:26:33, 2019-09-05 22:26:33).
2019-09-05 22:26:33.060 UTC [15340] STATEMENT:  INSERT INTO "phone_numbers" ("phone_number","user_id","inserted_at","updated_at","id") VALUES ($1,$2,$3,$4,$5)

Thank you!
Attila

2

From what I gather your schema defined :phone_number as string where as your regex is matching on digit.

Perhaps the datatype is wrong?

Have you also try ~*?

1 Like
3

Yes, I am also thinking on a type-mismatch, but it just doesn’t seem to add up (unless I am missing a fundamental SQL rule, which is possible).

Both the schema and migration define phone_number as :string, and it seems logical because the ~ operator would match on strings (right?). The ~* is only the case-insensitive sibling of ~ (according to 9.7.3. POSIX Regular Expressions), so that doesn’t mean to matter if I only need digits.

I could use :bigint instead of string, and put a CHECK constraint on it, but it would be nice to figure out what I am doing wrong.

4

Adding a constraint directly on table using the SQL command

ALTER TABLE phone_numbers 
  ADD CONSTRAINT phone_number_must_be_a_ten_digit_string 
  CHECK (phone_number ~ '^\d{10}$');

works, so I’ll just use Ecto.Migration.execute/2 until figuring out what was wrong with my syntax.

Update 2019-09-06 0804

The cause of my issue: forgot to escape the backslash… It should have been check: "phone_number ~ '^\\d{10}$'".

From the initial migration:

    create constraint(
      :phone_numbers,
      :phone_number_must_be_a_ten_digit_string,
      check: "phone_number ~ '^\d{10}$'"

The same regex but input directly in psql:

ALTER TABLE phone_numbers 
  ADD CONSTRAINT phone_number_must_be_a_ten_digit_string2
  CHECK (phone_number ~ '^\d{10}$');

Comparison side-by-side (using \d+ phone_numbers in psql):

Check constraints:
    "phone_number_must_be_a_ten_digit_string" CHECK (phone_number::text ~ '^^?{10}$'::text)
    "phone_number_must_be_a_ten_digit_string2" CHECK (phone_number::text ~ '^\d{10}$'::text)
2 Likes
5

You probably need to \\d:

iex(1)> IO.puts "\d"
^
:ok
iex(2)> IO.puts "\\d"                                                  
\d
4 Likes