3 replies

.unique() doesn't seem to work

I am coming from a front end background so am in unfamiliar territory with DB/Postgres stuff so apologies if I've missed something obvious.

I started with the following table in Drizzle (removed irrelevant columns):

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  }
);

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  }
);

I then wanted to ensure that the

username

username

column was unique so changed it to:

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 }).unique()
  }
);

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 }).unique()
  }
);

After doing a

drizzle-kit push

drizzle-kit push

I was expecting it to say that changes needed to be applied but to my surprise it just said "No changes detected".

I then checked for uniqueness by going to Drizzle Studio and found I was still able to have multiple rows with the same username.

The only way I was able to get the unique constraint to work properly was by doing:

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  },
  (table) => {
    return {
      usernameIndex: uniqueIndex("usernameIndex").on(table.username),
    };
  },
);

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  },
  (table) => {
    return {
      usernameIndex: uniqueIndex("usernameIndex").on(table.username),
    };
  },
);

Am I missing something with how

.unique()

.unique()

should be used or what it's for?

Thanks.

Drizzle Team•2y ago•

3 replies

epsilon42

.unique() doesn't seem to work

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  }
);

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  }
);

I then wanted to ensure that the

username

username

column was unique so changed it to:

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 }).unique()
  }
);

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 }).unique()
  }
);

After doing a

drizzle-kit push

drizzle-kit push

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  },
  (table) => {
    return {
      usernameIndex: uniqueIndex("usernameIndex").on(table.username),
    };
  },
);

export const users = createTable(
  "user",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    username: varchar("username", { length: 32 })
  },
  (table) => {
    return {
      usernameIndex: uniqueIndex("usernameIndex").on(table.username),
    };
  },
);

Am I missing something with how

.unique()

.unique()

should be used or what it's for?

Thanks.

.unique() doesn't seem to work

Similar Threads

.unique() doesn't seem to work

Similar Threads

Similar Threads

Similar Threads