[BUG]: `drizzle:push` repeatedly prompts to truncate table for `unique()` constraint that already exists in the database

### Report hasn't been filed before.

- [x] I have verified that the bug I'm about to report hasn't been filed before.

### What version of `drizzle-orm` are you using?

0.44.5

### What version of `drizzle-kit` are you using?

0.31.8

### Other packages

_No response_

### Describe the Bug

`drizzle-kit push` shows the truncation warning prompt for a `unique()` constraint on **every run**, even after the constraint has been successfully applied to the database. The constraint is confirmed present in `pg_constraint`, but drizzle-kit fails to detect it on subsequent runs and treats it as missing each time.

## Environment

| | |
|---|---|
| `drizzle-kit` | `0.31.8` |
| `drizzle-orm` | `0.44.5` |
| `pg` driver | `8.16.3` |
| Database | PostgreSQL |
| Node.js | `22.22.3` |
| OS | macOS |

## Schema Definition

```ts
// src/schema/customerCategoryAssignment.ts
import { pgTable, timestamp, unique, uuid } from "drizzle-orm/pg-core";

export const customerCategoryAssignmentTable = pgTable(
'customer_category_assignment',
{
id: uuid('id').defaultRandom().primaryKey(),
createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
customerId: uuid('customer_id').notNull(),
categoryId: uuid('category_id').notNull(),
},
(table) => [
unique('uq_customer_category_assignment').on(table.customerId, table.categoryId),
]
);
```

> **Note:** The bug also reproduces with the old (now deprecated) object syntax:
> ```ts
> (table) => ({
> uniqueCustomerCategory: unique('uq_customer_category_assignment').on(table.customerId, table.categoryId),
> })
> ```

## Steps to Reproduce

1. Define a `pgTable` with `unique('name').on(col1, col2)` in the table config callback.
2. Run `drizzle-kit push` — the following prompt appears:
```
· You're about to add uq_customer_category_assignment unique constraint
to the table, which contains 447 items. If this statement fails, you
will receive an error from the database. Do you want to truncate
customer_category_assignment table?

❯ No, add the constraint without truncating the table
Yes, truncate the table
```

3. Select **"No, add the constraint without truncating the table"**.
4. Push completes successfully.
5. Verify the constraint exists in the database:
```sql
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'customer_category_assignment'::regclass
AND contype = 'u';
```

Result confirms the constraint is present:

```
conname | contype | pg_get_constraintdef
-------------------------------|---------|-----------------------------------
uq_customer_category_assignment| u | UNIQUE (customer_id, category_id)
```

6. Run `drizzle-kit push` **again** — the **same prompt appears again**, even though the constraint already exists.
7. This repeats infinitely on every subsequent `drizzle-kit push` run.
## Expected Behavior

After step 3, `drizzle-kit push` should detect that `uq_customer_category_assignment` already exists in `pg_constraint` and produce no diff on all subsequent runs — identical to how it handles primary keys, foreign keys, and regular indexes.

## Actual Behavior

`drizzle-kit push` prompts about truncating the table on **every single run**, even though:

- The constraint exists in `pg_constraint` with `contype = 'u'`
- The constraint exists in `pg_indexes` (PostgreSQL creates a backing index for every unique constraint)
- There are **zero duplicate rows** in the table
- The constraint definition matches the schema exactly: `UNIQUE (customer_id, category_id)`
## Root Cause (Hypothesis)

drizzle-kit's schema introspection appears to differentiate between:

- A **UNIQUE CONSTRAINT** (`ALTER TABLE t ADD CONSTRAINT name UNIQUE (col)`) → what `unique()` generates
- A **UNIQUE INDEX** (`CREATE UNIQUE INDEX name ON t (col)`) → what `uniqueIndex()` generates
When drizzle-kit pulls the existing database schema to compute a diff, it does not appear to recognise a `UNIQUE CONSTRAINT` (stored in `pg_constraint` with `contype = 'u'`) as satisfying the `unique()` call in the TypeScript schema. It keeps treating the constraint as absent.

Both object types are functionally identical in PostgreSQL for the purpose of preventing duplicate rows. The difference is an internal implementation detail that drizzle-kit's introspection does not handle correctly.

## Workaround

Replace `unique()` with `uniqueIndex()` in the table config:

```ts
import { pgTable, timestamp, uniqueIndex, uuid } from "drizzle-orm/pg-core";

export const customerCategoryAssignmentTable = pgTable(
'customer_category_assignment',
{
id: uuid('id').defaultRandom().primaryKey(),
createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
customerId: uuid('customer_id').notNull(),
categoryId: uuid('category_id').notNull(),
},
(table) => [
uniqueIndex('uq_customer_category_assignment').on(table.customerId, table.categoryId),
]
);
```

After making this change, drop the existing constraint from the database (so drizzle-kit can recreate it as an index):

```sql
ALTER TABLE customer_category_assignment
DROP CONSTRAINT uq_customer_category_assignment;
```

Then run `drizzle-kit push` — it creates a `UNIQUE INDEX` without prompting, detects it correctly on all future runs, and never asks again.

## Impact

This bug is a **production data safety risk**. The repeated truncation prompt on every push run creates a permanent opportunity to accidentally select "Yes, truncate the table" and destroy all rows in a production table. In our case, the affected table contained 447 rows of live business data. The prompt appearing once (on first apply) is expected and acceptable — appearing indefinitely after successful application is not.

## Additional Notes

- The issue was reproduced on a table with 447 rows. The table had **zero duplicate values** on the constrained columns, so the prompt is not caused by constraint violation risk — it is purely a false positive from the diff engine.
- The same behaviour occurs regardless of whether the old deprecated object syntax or the new array syntax is used for the table config callback.
- The `unique()` function is documented as the idiomatic way to add named unique constraints to a table. The fact that its only correct workaround is to use `uniqueIndex()` instead suggests this is an unintentional inconsistency in how the two are introspected.

Start with drizzle-kit push using the schema in src/schema/customerCategoryAssignment.ts, then inspect how the existing PostgreSQL unique constraint is read from pg_constraint and compared with unique(). Verify the behavior with the provided SQL query and repeated push runs; done means the existing constraint produces no diff or truncation prompt on subsequent runs.

Written by the indexing model from the issue text.