🎉 Added support for UNIQUE constraints in PostgreSQL, MySQL, SQLite
For PostgreSQL, unique constraints can be defined at the column level for single-column constraints, and in the third parameter for multi-column constraints. In both cases, it will be possible to define a custom name for the constraint. Additionally, PostgreSQL will receive the NULLS NOT DISTINCT option to restrict having more than one NULL value in a table. Reference
Examples that just shows a different unique usage. Please don’t search a real usage for those tables
// single column
const table = pgTable('table', {
id: serial('id').primaryKey(),
name: text('name').notNull().unique(),
state: char('state', { length: 2 }).unique('custom'),
field: char('field', { length: 2 }).unique('custom_field', { nulls: 'not distinct' }),
});
// multiple columns
const table = pgTable('table', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
state: char('state', { length: 2 }),
}, (t) => ({
first: unique('custom_name').on(t.name, t.state).nullsNotDistinct(),
second: unique('custom_name1').on(t.name, t.state),
}));
For MySQL, everything will be the same except for the NULLS NOT DISTINCT option. It appears that MySQL does not support it
Examples that just shows a different unique usage. Please don’t search a real usage for those tables
// single column
const table = mysqlTable('table', {
id: serial('id').primaryKey(),
name: text('name').notNull().unique(),
state: text('state').unique('custom'),
field: text('field').unique('custom_field'),
});
// multiple columns
const table = mysqlTable('cities1', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
state: text('state'),
}, (t) => ({
first: unique().on(t.name, t.state),
second: unique('custom_name1').on(t.name, t.state),
}));
In SQLite unique constraints are the same as unique indexes. As long as you can specify a name for the unique index in SQLite - we will treat all unique constraints as unique indexes in internal implementation
// single column
const table = sqliteTable('table', {
id: int('id').primaryKey(),
name: text('name').notNull().unique(),
state: text('state').unique('custom'),
field: text('field').unique(),
});
// multiple columns
const table = sqliteTable('table', {
id: int('id').primaryKey(),
name: text('name').notNull(),
state: text('state'),
}, (t) => ({
first: unique().on(t.name, t.state),
second: unique('custom').on(t.name, t.state),
}));
