Oninit
Talk to an expert →

Informix Error -446

-446 Illegal attempt to convert data type when using KEEP ANY CHECK

CONSTRAINT or KEEP ANY REFERENCING FOREIGN KEY

Supported ALTER TABLE statements that MODIFY the primary key while keeping check constraints or referencing foreign keys include:

Integer -> Serial, Bigint, Bigserial, Int8, or Serial8 Serial -> Integer, Bigint, Bigserial, Int8, or Serial8 Int8 -> Serial8, Bigint, or Bigserial Serial8 -> Int8, Bigint, or Bigserial Bigint -> Bigserial, Int8, or Serial8 Bigserial -> Bigint, Int8, or Serial8

All others are unsupported.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-446 fires on an ALTER TABLE that modifies a primary key's data type while using KEEP ANY CHECK CONSTRAINT or KEEP ANY REFERENCING FOREIGN KEY to preserve dependent constraints, but the requested type conversion isn't one of the specific compatible transformations supported in this context — only conversions like INTEGER to SERIAL/BIGINT/BIGSERIAL/INT8/SERIAL8 (and similar compatible numeric/serial transformations) are permitted.

  1. Attempting a fundamentally incompatible type change (e.g. numeric to character) while using KEEP ANY CHECK CONSTRAINT/KEEP ANY REFERENCING FOREIGN KEY — the direct cause.
  2. Assuming any type change is allowed as long as dependent constraints are being preserved, when in fact only a narrow set of numeric/serial conversions qualify.
  3. A schema-modernization effort (e.g. moving a key from INTEGER to BIGINT for growth headroom) accidentally including a broader, unsupported type change in the same statement.
Solutions / Resolution
  1. Restrict the type conversion to one of the specific supported transformations, per the official guidance — INTEGER to SERIAL/BIGINT/BIGSERIAL/INT8/SERIAL8, and similar compatible numeric/serial pairs.
  2. If a genuinely incompatible type change is needed, don't use KEEP ANY CHECK CONSTRAINT/ KEEP ANY REFERENCING FOREIGN KEY — instead, drop the dependent constraints explicitly first, perform the type change, then recreate the constraints appropriately (adjusting them if the new type requires it).
Examples
A supported numeric-to-serial conversion
ALTER TABLE orders MODIFY (order_id BIGSERIAL) KEEP ANY REFERENCING FOREIGN KEY;
-- valid: INTEGER-family to BIGSERIAL is a supported conversion
An unsupported conversion
ALTER TABLE orders MODIFY (order_id VARCHAR(20)) KEEP ANY REFERENCING FOREIGN KEY;
-- -446: numeric to character isn't a supported conversion here

Fix — drop and recreate the foreign key explicitly instead:

ALTER TABLE order_items DROP CONSTRAINT fk_order_items_orders;
ALTER TABLE orders MODIFY (order_id VARCHAR(20));
ALTER TABLE order_items MODIFY (order_id VARCHAR(20));
ALTER TABLE order_items ADD CONSTRAINT
    FOREIGN KEY (order_id) REFERENCES orders(order_id);
Diagnostic Checks
  1. Check whether the requested type conversion is one of the specific supported numeric/serial transformations.
  2. If not, plan to drop and recreate dependent constraints explicitly rather than relying on KEEP ANY CHECK CONSTRAINT/KEEP ANY REFERENCING FOREIGN KEY.
Related Errors / Related Topics
  • -356 — "Data type of the referencing and referenced columns do not match." A related foreign-key type-compatibility restriction, relevant when recreating a foreign key after a broader type change.
  • -362 — "Can have only one column of serial/(serial8 or bigserial) type." Another serial-type-related restriction, relevant when converting a key to SERIAL/SERIAL8.

Only a narrow set of numeric/serial type conversions are supported while preserving dependent constraints automatically — for anything broader, drop and recreate the constraints explicitly.