What is the max length of an Informix column and can it be increased?

Question

I am trying to create a table with the following:

CREATE TABLE GTW_WORKFLOW_MON 
(
    WORKFLOW_NAME VARCHAR(255) NOT NULL, 
    WORKFLOW_LOADED NUMERIC(20) NOT NULL, 
    ACTIVITY_NAME VARCHAR(255) NOT NULL,
    FLAGS INTEGER NOT NULL,
    MONITOR_NAME VARCHAR(255) NOT NULL,
    CLASSNAME VARCHAR(255) NOT NULL,
    STR0 VARCHAR(255),
    STR1 VARCHAR(255),
    STR2 VARCHAR(255), 
    NUM0 VARCHAR(255), 
    NUM1 VARCHAR(255), 
    NUM2 VARCHAR(255), 
    DATE0 VARCHAR(255),
    DATE1 VARCHAR(255), 
    DATE2 VARCHAR(255), 
    PRIMARY KEY (WORKFLOW_NAME, WORKFLOW_LOADED, ACTIVITY_NAME, MONITOR_NAME)
)

It fails due to column length not being large enough.

Solution

It would help if the SQL statement was syntactically valid and if you provided the exact error message. When reformatted and syntax corrected, the statement looks like:

CREATE TABLE gtw_workflow_mon
(
    workflow_name   VARCHAR(255) NOT NULL,
    workflow_loaded NUMERIC(20) NOT NULL,
    activity_name   VARCHAR(255) NOT NULL,
    flags           INTEGER NOT NULL,
    monitor_name    VARCHAR(255) NOT NULL,
    classname       VARCHAR(255) NOT NULL,
    str0            VARCHAR(255),
    str1            VARCHAR(255),
    str2            VARCHAR(255),
    num0            VARCHAR(255),
    num1            VARCHAR(255),
    num2            VARCHAR(255),
    date0           VARCHAR(255),
    date1           VARCHAR(255),
    date2           VARCHAR(255),
    PRIMARY KEY(workflow_name, workflow_loaded, activity_name, monitor_name)
);

And, when that is run on a system with 2KB pages, the error message is:

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

The standard way of getting a brief explanation of an error message is finderr; it says:

$ finderr -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 depends on the database server
in use, but all servers support a total of 120 bytes. The limit is the
same as the restriction on the total size of all columns in a composite
index. For additional information, see the CREATE TABLE statement in
the IBM Informix Guide to SQL: Syntax.

$

The 'a total of 120 bytes' should be 'a total of at least 120 bytes'; that lower-bound applies to Informix SE. In IDS (Informix Dynamic Server), the lower-bound is 255 bytes, but it is bigger in more recent systems, and also bigger when the page size is bigger.

You have a variety of options.

• You can consider why your names need to be 255 characters each - is that sensible (would, say, 64 be sufficient)?
• If your server version is recent enough (10.00 or later, I believe), you could create the table in a dbspace with a larger page size.

Since the key is a maximum of 3*255+(20/2+1) = 776 bytes, and the rule of thumb is you need to be able to store 5 maximum-length key values + ROWID/FRAGID overhead (8 bytes) per page, you would need a 4 KB page size. (Had you been running on AIX, you probably wouldn't have noticed the issue.)

Also, you should not be storing date values in VARCHAR(255); you should use DATE or perhaps DATETIME YEAR TO DAY (a weird way of spelling DATE - though the underlying format is different, using 5 bytes on disk instead of 4 for a plain DATE), or perhaps DATETIME YEAR TO SECOND (a funny way of spelling TIMESTAMP), or ... The 'num0, num1, num2' fields are also dubious, too; if they are meant to store numbers, use NUMERIC or DECIMAL -- DECIMAL(20) in most IDS databases means a 20-digit floating point decimal number.

Edited to add:

And, to answer the direct question, VARCHAR columns can only be up to 255 bytes long; LVARCHAR columns can be up to about 32 KB; CHAR columns can be up to 32 KB; TEXT columns can be up to 2 GB, and CLOB columns can be even larger. The total length of a row is limited to about 32 KB (but BYTE, TEXT, BLOB and CLOB columns count as a fixed size descriptor towards that 32 KB total - the actual data is stored outside the row). There are some version dependencies that I'm not bringing out - if you are using IDS 10.00 or later, this is accurate.