Why use Ecto :id instead of unique index?


I’m working my way through Programming Phoenix at the moment, and I got to the chapter where the Ecto Schema+Migration is set up. They do something like this:

def change do
  create table(:users) do
    add :name, :string
    add :username, :string, null: false
    add :password_hash, :string
  create unique_index(:users, [:username])

I was wondering: If you’re going to put a unique index on username anyway, why even use the Ecto :id field and not user :username as primary key for the table?


Hi @clamshell,

This discussion really is about the use of “natural” vs artificial ids in databases. There are several reasons why a dedicated :id column is generally preferred, even when usernames are considered unique. A big part of it boils down to “foreign keys”. That is, when you have other tables, and those tables want to reference your users table by its primary key, using an integer :id is both more efficient and more resilient to change. If you use the username column, then if you change a user’s username not only do you have to change it in the users table, you have to change it all over your database to update rows that point to that user by username.


Thanks, Ben, that makes a lot of sense!

1 Like