tags:

views:

431

answers:

1

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.
+2  A: 

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

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