Properly setting up a check constraint with Ecto

Question

I have this check_constraint in my model.

  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, @all_fields)
    |> validate_required(@required_fields)
    |> check_constraint(:stars, name: :stars_range, message: "stars must be between 1 and 5")
  end

The create constraint was successfully migrated.

create constraint("reviews", "stars_range", check: "stars>=1 and stars<=5")

But when I run this test the changeset is valid? I would expect it to be invalid because I'm passing the integer 7 to the stars column. which has the constraint of 1 through 5. Does anybody know what is wrong here?

test "requires stars to be within range of 1-5" do
    user = insert(:user)
    project = insert(:project, owner: user)
    user_project_map = %{project_id: project.id, user_id: user.id}
    review_map = Map.merge(@valid_attrs, user_project_map)

    attrs = %{review_map | stars: 7}
    changeset = Review.changeset(%Review{}, attrs)
    refute changeset.valid?
  end

Answer 1

Quote from docs:

(...) Now, when invoking Repo.insert/2 or Repo.update/2, if the price is not positive, it will be converted into an error and {:error, changeset} returned by the repository. Note that the error will occur only after hitting the database so it will not be visible until all other validations pass.

This means that check_constraint happens only if the query hits the database. Hence your changeset.valid? returning true when you check the validation before the database is actually called. The constraint you created is created inside database so Ecto actually has no way of knowing what this constraint actually checks for before calling it. Usually such constraints are used for more complex checks or if you have constraints already defined in your database (perhaps because you migrated the database from another system?). If you want to see your constraint in action you should just write in your test:

attrs = %{review_map | stars: 7}
changeset = Review.changeset(attrs)
{:error, changeset} = Repo.insert(changeset)
refute changeset.valid?

If you need the Changeset to check some conditions before calling the database then you should use functions like validate_inclusion/4 or validate_subset/4. You can even write your own checker using validate_change/4 (let me know if you need more explanation how to do it). If you use those validators then your changeset will work before calling the database.