Informix Error -371

-371 Cannot create unique index on column with duplicate data.

This CREATE UNIQUE INDEX statement cannot be completed because the column (or columns) contains one or more duplicate rows. You can either create an ordinary index, accepting the duplicate values, or you can modify the table to remove the duplicates. To get a list of the duplicate values in a single column, first create the ordinary index. Then use a SELECT statement such as the following, filling in the table and column names:

SELECT column FROM table main WHERE 1 < ( SELECT COUNT(*) FROM table sub WHERE main.column = sub.column )

This statement can be extended to handle the case of multiple columns using AND.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-371 fires when CREATE UNIQUE INDEX is attempted on a column (or column combination) that already contains duplicate values — a unique index can only be built on data that's already actually unique.

Duplicate data already present in the table — the column simply isn't unique yet, whether by original design or by data quality drift over time.
A table populated before a uniqueness constraint was decided on, where cleanup wasn't done before attempting to enforce it retroactively.
A composite unique index attempted on multiple columns where the combination of values has duplicates, even if no single column does on its own.
Data imported from multiple sources without deduplication, later needing a unique constraint applied.

Solutions / Resolution

Identify the duplicate values first, per the official guidance:
```
SELECT column FROM table_name main WHERE 1 < (
    SELECT COUNT(*) FROM table_name sub WHERE main.column = sub.column
);
```
For a composite index, extend this with AND conditions across each column in the intended index.
Remove or consolidate the duplicate rows before retrying CREATE UNIQUE INDEX.
If duplicates are actually expected/acceptable, create a regular (non-unique) index instead, rather than forcing uniqueness that the data doesn't support.

Examples

Finding duplicates before creating a unique index

SELECT customer_email FROM customers main WHERE 1 < (
    SELECT COUNT(*) FROM customers sub WHERE main.customer_email = sub.customer_email
);

Finding duplicates across a composite key

SELECT first_name, last_name FROM customers main WHERE 1 < (
    SELECT COUNT(*) FROM customers sub
    WHERE main.first_name = sub.first_name AND main.last_name = sub.last_name
);

Creating a regular index instead, if duplicates are legitimate

CREATE INDEX idx_customers_email ON customers(customer_email);
-- non-unique, since duplicates are expected

Diagnostic Checks

Run the duplicate-detection query against the target column(s) before attempting CREATE UNIQUE INDEX.
Decide whether duplicates represent bad data to clean up, or a legitimate condition that means a unique index simply isn't the right constraint here.

Related Errors / Related Topics

-268 — "Unique constraint violated." The runtime-insert-time counterpart of this index-creation-time restriction — both concern uniqueness enforcement against actual data.
-316 — "Index index-name already exists in database." Another CREATE INDEX-time failure, though about naming rather than data uniqueness.

Run the duplicate-detection query first — it tells you exactly which rows need attention before you decide whether to clean the data or drop the uniqueness requirement.