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.

Yes, because I want to use number as id. Is it not a good practice? — softshipper, Dec 05 '16 at 15:01
What do you mean by "generate a number" then? An integer key with auto increment would create records with values 0, 1, 2, ... — Dogbert, Dec 05 '16 at 15:03
I will generate a number, that will always have 7 numbers, for example: `1000554` or `4117455'. — softshipper, Dec 05 '16 at 15:07
That will be as a customer number. @Dogbert it is a good practice? — softshipper, Dec 05 '16 at 15:17
I think it might be better to set the initial value of the auto increment sequence to `1000000` in the migration. See http://stackoverflow.com/questions/5342440/reset-auto-increment-counter-in-postgres. When you reach `9999999` it'll continue with 8 digit integers (`10000000..99999999`). — Dogbert, Dec 05 '16 at 15:23
Yes. I'll post an answer with details as soon as I get time. — Dogbert, Dec 05 '16 at 15:32

score 3 · Accepted Answer · answered Dec 05 '16 at 16:01

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.

thanks so much for your help. Are you on elixir team? I have one more question, look at the following [code on github](https://github.com/kostonstyle/relation/blob/master/priv/repo/migrations/20161203214117_create_countries_languages.exs) and pay attention on `countries_codes` table, would be not better to use iso as primary key instead of default `id`. Later, the `countries` table will use `countries_codes` for constraint. — softshipper, Dec 06 '16 at 06:33

Generate custom number for primary key and validation

1 Answers1