[Feature Request] Check Constraint and Unique Constraint

[Ericnr]

check

• [x] ORM support
• [ ] Kit support

unique

• [ ] ORM support
• [ ] Kit support

---

Would be great to see those implemented

[kelbyfaessler]

+1 to unique constraint

[ghost]

I'm using drizzle-orm with sqlite, and typescript finds the export unique from drizzle-orm/sqlite-core, and looking at the type declarations, it looks like it exists.

However, is it not currently usable? I couldn't figure out how to actually use it, kept running into type errors.

[Proshashi]

Any update on the unique constraint?

[deadcoder0904]

yes, would love an update on unique constraint.

i'm using planetscale & foreign keys aren't supported.

the recommendation is to add @unique to userId... i'm not sure if using just index (userIdx) would do the trick:

export const key = mysqlTable(
  'auth_key',
  {
    id: varchar('id', {
      length: 255,
    }).primaryKey(),
    userId: varchar('user_id', {
      length: 15,
    }).notNull(),
    primaryKey: boolean('primary_key').notNull(),
    hashedPassword: varchar('hashed_password', {
      length: 255,
    }),
    expires: bigint('expires', {
      mode: 'number',
    }),
  },
  (table) => {
    return {
      userIdx: index('user_idx').on(table.userId),
    }
  }
)

[MurmeltierS]

til; @algora-pbc isn't good with handling decimals

[zcesur]

hey @MurmeltierS, your $0.50 bounty didn't go through as we have a $10 limit - just updated the bounty form to reflect that. you can create a new bounty if you'd like. cheers

[medromo]

Does anyone know if it's okay to manually add the UNIQUE constraint to the migration file before running it? I think this should work okay while the feature is added to Drizzle, but I haven't seen the suggestion anywhere, so I wonder if it may have some unwanted side effects. Anyone using this approach? Or any thoughts in the matter? thanks ✌🏻

[AprilNEA]

We care about unique constraint, which is a very important part of the database. Any progress?

[aetherplex]

Any updates on unique constraints? Or workarounds? Has to be one of the most-requested features

[algora-pbc]

💎 $40 bounty created by @MurmeltierS 👉 To claim this bounty, submit your pull request on Algora 📝 Before proceeding, please make sure you can receive payouts in your country 💵 Payment arrives in your account 2-5 days after the bounty is rewarded 💯 You keep 100% of the bounty award 🙏 Thank you for contributing to drizzle-team/drizzle-orm!

[jemiluv8]

@MurmeltierS, I'm attempting this.

[AndriiSherman]

https://github.com/drizzle-team/drizzle-orm/pull/825 orm part is done now, I need to finish drizzle-kit part for migrations

[jemiluv8]

Ok. @AndriiSherman. Thanks for the timely update. I will no longer be attempting this.

[AndriiSherman]

Kit part is done. I'm performing the final tests and hoping to release the unique constraints tomorrow. By the way, after extensively researching the documentation, I discovered that unique indexes should be identical to unique constraints.

According to the SQLite documentation, unique constraints are treated as unique indexes. Therefore, in the ORM and Kit, we will handle unique constraints for SQLite as unique indexes, providing a more convenient syntax sugar

I couldn't find any reliable information about the differences in MySQL. The only distinction I found in PostgreSQL is that unique constraints can ensure proper uniqueness in transaction modes or parallel queries. I will attempt to replicate this behavior locally.

So essentially, unique indexes should suffice for your needs. However, this task is already completed, and it was done in order to accommodate both syntaxes for each database

But for all databases, constraints create unique indexes for you and utilize them for uniqueness detection. It's essentially the same as simply creating a unique index, which we already have in Drizzle

[AndriiSherman]

Everything is ready to be checked on [email protected] and [email protected] Closing this issue as completed and creating a new one just for checks constraints support

[algora-pbc]

🎉🎈 @drizzle-team has been awarded $40 by @MurmeltierS! 🎈🎊