Informix Error -542: Cannot specify a column more than once in a constraint, trigger, or index.

Reasons / Common Causes

-542 fires when the same column name is listed more than once in a single constraint's column list, an index's column list, or an UPDATE trigger's triggering-column list.

1. A duplicated column name in an index's column list (CREATE INDEX ... (col1, col2, col1)) — a direct, common cause.
2. A duplicated column name in a constraint's column list (a composite PRIMARY KEY/UNIQUE/ FOREIGN KEY naming the same column twice).
3. A duplicated column name in an UPDATE trigger's triggering-column list, per the official guidance — CREATE TRIGGER ... UPDATE OF col1, col2, col1 ON ... naming the same column twice.

Solutions / Resolution

1. Remove the duplicate occurrence of the column name, per the official guidance, and retry the statement.
2. Review the full column list carefully in whichever of the three contexts (constraint, trigger, index) produced the error, since the duplication may not be adjacent in the list.

Examples

A duplicate in an index

CREATE INDEX idx_orders ON orders (customer_id, order_date, customer_id);
-- -542: customer_id listed twice

A duplicate in a trigger's triggering-column list

CREATE TRIGGER trg_orders_update UPDATE OF status, ship_date, status ON orders
  REFERENCING NEW AS post FOR EACH ROW (EXECUTE PROCEDURE log_change(post.status));
-- -542: status listed twice

Corrected

CREATE INDEX idx_orders ON orders (customer_id, order_date);

CREATE TRIGGER trg_orders_update UPDATE OF status, ship_date ON orders
  REFERENCING NEW AS post FOR EACH ROW (EXECUTE PROCEDURE log_change(post.status));

Diagnostic Checks

1. Scan the column list in the failing statement for repeated names, checking constraint, trigger triggering-column, and index column lists specifically, since the restriction applies to all three.

Related Errors / Related Topics

No closely related error codes are cross-referenced for -542 in this set yet.

Applies to three different contexts (constraints, triggers, indexes) — scan the specific column list in the failing statement for a repeated name.