CODEWITHSUNDEEP
sqldatabaseinformix
Craig Angus
Craig Angus

Reputation: 23198

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

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.

Upvotes: 3

Views: 12383

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 754410

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.

Upvotes: 11

Related Questions