Oninit
Talk to an expert →

Informix Error -316

-316 Index <index-name> already exists in database. </index-name>

This statement tries to create an index with the name shown, but an index of that name already exists. Only one index of a given name can exist in a single database.

Check the spelling of the name. If it is as you intended, and you are sure it should not exist, make sure you are using the right database. To review the names of all indexes and their owners, join systables and sysindexes as follows:

SELECT T.tabname, I.idxname, I.owner FROM systables T, sysindexes I WHERE I.tabid = T.tabid AND T.tabid > 99

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-316 is the index-level counterpart of -310: index names must be unique within a database (across all tables, not just the one being indexed), and CREATE INDEX fails when the chosen name is already taken.

  1. A CREATE INDEX statement reusing a name already assigned to another index — the direct, most common cause.
  2. Running a setup/migration script more than once without a preceding drop or existence check.
  3. A generic index-naming convention (e.g. idx1, idx_status) reused across multiple tables without checking for prior use, since index names are unique database-wide, not per-table.
  4. Wrong database connected — the name conflicts in the database the session is actually attached to, not the one intended.
Solutions / Resolution
  1. Verify the index name's spelling and confirm the correct database is connected, per the official guidance.
  2. Query existing indexes to check what's already using the name:
    SELECT idxname, tabname, owner FROM sysindexes WHERE idxname = 'idx_orders_customer';
  3. Choose a more specific, table-qualified naming convention (e.g. idx_orders_customer_id rather than a generic idx1) to avoid collisions across tables.
  4. For repeatable setup scripts, drop the existing index first or check for its existence before creating it.
Examples
A generic name reused across tables
CREATE INDEX idx_status ON orders(status);
-- later, on a different table:
CREATE INDEX idx_status ON shipments(status);
-- -316: idx_status already exists (on orders), even though this
-- is a different table

Fix — use a table-qualified name:

CREATE INDEX idx_shipments_status ON shipments(status);
Checking for an existing index before recreating
SELECT idxname, tabname FROM sysindexes WHERE idxname = 'idx_orders_customer';
-- if found, DROP INDEX idx_orders_customer; before recreating,
-- or skip the CREATE INDEX entirely
Diagnostic Checks
  1. Query sysindexes for the name in question to confirm whether it's already taken and by which table.
  2. Confirm the currently connected database matches the one the script was intended for.
Related Errors / Related Topics
  • -310 — "Table table-name already exists in database." The table-level counterpart of this same uniqueness restriction.
  • -315 — "No create index permission." Another CREATE INDEX-time failure, though about privileges rather than naming.

Index names are unique across the whole database, not per-table — a table-qualified naming convention avoids most collisions of this kind.