Informix Error -704: Primary key already exists on the table.

Reasons / Common Causes

-704 fires when ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY (or an equivalent) is attempted against a table that already has a primary key — per the official guidance, a table can have only one.

1. A second PRIMARY KEY constraint attempted on a table that already has one — the direct, only cause.
2. A migration/deployment script re-run without checking whether the primary key was already added, attempting to add it a second time.

Solutions / Resolution

1. Drop the existing primary key first, if it genuinely needs to be replaced with a different one — DROP CONSTRAINT requires the constraint's actual name (there is no DROP CONSTRAINT PRIMARY KEY shorthand), so look it up first via sysconstraints:

   ALTER TABLE orders DROP CONSTRAINT u123_456;  -- the name from the sysconstraints query below
   ALTER TABLE orders ADD CONSTRAINT PRIMARY KEY (order_id, region_id);
   

2. Or recognize the existing primary key is already sufficient and skip the ALTER TABLE entirely, if that was the actual goal.
3. Check the table's existing primary key before attempting to add a new one:

   SELECT constrname FROM sysconstraints
   WHERE tabid = (SELECT tabid FROM systables WHERE tabname = 'orders') AND constrtype = 'P';
   

Examples

The disallowed second primary key

CREATE TABLE orders (order_id INT PRIMARY KEY, status CHAR(10));
ALTER TABLE orders ADD CONSTRAINT PRIMARY KEY (status);
-- -704: orders already has a primary key on order_id

Corrected — drop the old one first

SELECT constrname FROM sysconstraints
WHERE tabid = (SELECT tabid FROM systables WHERE tabname = 'orders') AND constrtype = 'P';
-- e.g. returns 'u123_456'
ALTER TABLE orders DROP CONSTRAINT u123_456;
ALTER TABLE orders ADD CONSTRAINT PRIMARY KEY (status);

Diagnostic Checks

1. Query sysconstraints for constrtype = 'P' on the table to confirm whether a primary key already exists before attempting to add one.

Related Errors / Related Topics

• -703 — "Primary key on table table-name has a field with a null key value." A related primary-key error, about a NULL value in the key rather than the table already having one.

A table can have only one primary key — drop the existing one first if it genuinely needs to be replaced.