Oninit
Talk to an expert →

Informix Error -550

-550 Total length of columns in constraint is too long.

The total size of all the columns listed in a UNIQUE, PRIMARY KEY, or FOREIGN KEY clause is limited. The limit is the same as the restriction on the total size of all columns in a composite index. IBM Informix Dynamic Server allows 16 key parts and a width of 390 bytes on a 2K page platform. On 4K page platforms or using a non-default page size allows greater than 390 bytes for the width of indexed columns. IBM Informix SE supports 8 columns and a width of 126 bytes. Other Informix database servers allow 16 columns and 255 bytes. The limit depends on the database server in use, but all servers support a total of at least 120 bytes.

For additional information, see the CREATE TABLE statement in the IBM Informix Guide to SQL: Syntax.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-550 is the same platform-dependent width/column-count limit as -517, applied to a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint's column list rather than to an explicit CREATE INDEX. Constraints of these kinds are backed by an index, so the same composite-index limits apply.

  1. Too many columns in a composite UNIQUE/PRIMARY KEY/FOREIGN KEY — per the official guidance, up to 16 key parts on IBM Informix Dynamic Server/Universal Server, 8 on Informix SE.
  2. Total key width exceeding the page-size-dependent byte limit — per the official guidance, 390 bytes on a 2K-page platform (more on 4K-page or non-default page-size platforms), 126 bytes on Informix SE, 255 bytes on other Informix servers; every server supports at least 120 bytes.
  3. Wide VARCHAR/CHAR columns included in the constraint's column list, summing toward the byte limit faster than expected across several columns.
Solutions / Resolution
  1. Reduce the number of columns in the constraint, per the official guidance, if it exceeds the platform's key-part limit.
  2. Reduce the width of the columns included, per the official guidance, particularly wide variable-length columns.
  3. Move to a larger page size for the dbspace, per the official guidance, since 4K (or larger) pages raise the byte-width ceiling above the 2K-page default.
Examples
Exceeding the limit
CREATE TABLE documents (
  title VARCHAR(255), author VARCHAR(100), publisher VARCHAR(100),
  UNIQUE (title, author, publisher)
);
-- -550: combined key width exceeds this platform's limit for the dbspace's page size
Narrowing the constraint
CREATE TABLE documents (
  title VARCHAR(255), author VARCHAR(100), publisher VARCHAR(100),
  UNIQUE (title, author)
);
Diagnostic Checks
  1. Sum the declared widths of the columns in the constraint and compare against the platform's documented limit for the dbspace's page size.
  2. Count the columns in the constraint against the 16-key-part (or 8, on Informix SE) limit.
Related Errors / Related Topics
  • -517 — "The total size of the index is too large or too many parts in index." The identical limit, hit via an explicit CREATE INDEX rather than a UNIQUE/PRIMARY KEY/FOREIGN KEY constraint.

The same key-width/column-count limit as -517 — narrow the constraint's column list, or move to a dbspace with a larger page size.