Generate custom number for primary key and validation

Question

I have following schema:

  @primary_key false
  schema "companies" do
    field :number,         :integer, primary_key: true
    field :name,           :string
    field :street,         :string
    field :zipcode,        :integer
    field :location,       :string
    field :phone,          :integer
    field :company_class,  :string
    field :country_iso,    :string
    field :email,          :string
    field :password,       :string, virtual: true
    field :password_hash,  :string
    has_many :contacts,    Busiket.Contact, on_delete: :delete_all

    timestamps
  end

  def register(struct, params \\ %{}) do

  end

How can I generate a number for field number, when a changeset will be create through the register function?

How can I validate first the database, if the number is already available or not, to avoid duplication.

Answer 1

Here's one way to create a column that starts at 1000000 and automatically assigns a unique value which is roughly equivalent to the previous value + 1 (roughly because an id might be "skipped" because of failed transactions and possibly some other cases).

This answer is specific to PostgreSQL as it uses the PostgreSQL specific setval and pg_get_serial_sequence functions.

Migration:

defmodule MyApp.Repo.Migrations.CreateCompany do
  use Ecto.Migration

  def up do
    create table(:companies, primary_key: false) do
      add :number, :serial, primary_key: true
      timestamps()
    end
    execute "select setval(pg_get_serial_sequence('companies', 'number'), 999999)"
  end

  def down do
    drop table(:companies)
  end
end

Model:

defmodule MyApp.Company do
  use MyApp.Web, :model

  @primary_key false
  schema "companies" do
    field :number, :integer, primary_key: true, read_after_writes: true

    timestamps()
  end
end

Demo:

iex(1)> Repo.insert! %Company{}
[debug] QUERY OK db=2.7ms queue=0.1ms
INSERT INTO "companies" ("inserted_at","updated_at") VALUES ($1,$2) RETURNING "number" [{{2016, 12, 5}, {15, 57, 44, 0}}, {{2016, 12, 5}, {15, 57, 44, 0}}]
%MyApp.Company{__meta__: #Ecto.Schema.Metadata<:loaded, "companies">,
 inserted_at: #Ecto.DateTime<2016-12-05 15:57:44>, number: 1000000,
 updated_at: #Ecto.DateTime<2016-12-05 15:57:44>}
iex(2)> Repo.insert! %Company{}
[debug] QUERY OK db=4.5ms
INSERT INTO "companies" ("inserted_at","updated_at") VALUES ($1,$2) RETURNING "number" [{{2016, 12, 5}, {15, 57, 44, 0}}, {{2016, 12, 5}, {15, 57, 44, 0}}]
%MyApp.Company{__meta__: #Ecto.Schema.Metadata<:loaded, "companies">,
 inserted_at: #Ecto.DateTime<2016-12-05 15:57:44>, number: 1000001,
 updated_at: #Ecto.DateTime<2016-12-05 15:57:44>}
iex(3)> Repo.insert! %Company{}
[debug] QUERY OK db=3.4ms queue=0.1ms
INSERT INTO "companies" ("inserted_at","updated_at") VALUES ($1,$2) RETURNING "number" [{{2016, 12, 5}, {15, 57, 45, 0}}, {{2016, 12, 5}, {15, 57, 45, 0}}]
%MyApp.Company{__meta__: #Ecto.Schema.Metadata<:loaded, "companies">,
 inserted_at: #Ecto.DateTime<2016-12-05 15:57:45>, number: 1000002,
 updated_at: #Ecto.DateTime<2016-12-05 15:57:45>}

Some notes:

• I set the sequence value to 999999 to ensure the next number in the sequence is 1000000

• I added read_after_writes: true to the column because the value for this field is generated by the database, and without read_after_writes set to true, the field will not be reloaded after insertion and will remain nil.