Wednesday, February 9, 2011

what does "Total Length of columns in constaint is too long" err mean in Informix?

I get the Total length of columns in constraint is too long. erro from the following

sql] Failed to execute:   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, S

TR0 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) )

  [sql] java.sql.SQLException: Total length of columns in constraint is too long.
  • Your primary key constraint is 785 bytes (255+20+255+255). If you increase your database page size to 4K it should work, barely. You should also reconsider if you need your columns to be as wide as you are defining them.

    I found a discussion group where an engineer, Radhika Gadde, describes that the maximum index size is related to page size. He says:

    which error you are getting while creation of Tables. Maximum Index key length can be calculated as follows:

    [(PAGESIZE -93)/5] -1

    like for 2k it is [( 2048-93)/5] -1 =[1955/5] -1 =391-1=390

    if PAGESIZE is 4K it is [(4096-93)/5] -1 =4003/5-1=800-1 =799

    Jonathan Leffler : Nit pick - NUMERIC(20) is equivalent to DECIMAL(20) and occupies (20/2)+1 = 11 bytes. But your basic answer is correct.

0 comments:

Post a Comment