CODEWITHSUNDEEP
sqlinformix
Craig Angus
Craig Angus

Reputation: 23198

What does "Total Length of columns in constraint 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.

Upvotes: 1

Views: 1365

Answers (2)

Sumedha
Sumedha

Reputation: 36

Above answer is complete. But thought of adding some helpful links in case someone runs to this issue again. Pagesize on Informix depends on Operating System. On my recent experience, I found it's 4K on Win 2008, OSX - Lion and 2K on SUSE EL4. You can find the page size by using 'onstat -D'.

I wrote http://sumedha.blogspot.com/2013/03/how-to-increase-informix-page-size.html with this experience. Following documentation link from IBM is also very helpful.

http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.admin.doc%2Fids_admin_0564.htm

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562871

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

Upvotes: 3

Related Questions