I found that URI column in OBJECTS and REVISIONS tables are limited to 255 chars in MySQL and DB2, due to the primary key constraint.
The purpose of this posting is to confirm this, and to seek opinions and workaround. Thanks. (1) MySQL Copied from JDBC How To: " create table objects(uri blob not null, primary key uriIndex (uri(255)), classname blob); " I believe PRIMARY KEY is used to enforce uniqueness as well as indexing. If this is true, URI exceeds 255, and PRIMARY KEY is only done on the first 255 chars, then the uniqueness is not enforced. The key is limited to 255 on a single column in MySQL (both MyISAM and innoDB), having a larger URI column only create a hidden problem. It is better to make the problem up front. For this reason, I would suggest changing above to " create table objects(uri varchar(255) not null, primary key uriIndex (uri(255)), classname blob); " How do you think? And anyone has an idea to make URI beyond 255 in MySQL? (2) DB2 The DB2 (version 7.1) has the same limitation on the length of column with primary key, as shown below. " db2 => create table obj(uri varchar(256) not null primary key, db2 (cont.) => classname varchar(3200)); SQL20075 The index or index extension "SQL021114150625950" cannot be created or altered because the length of "URI" is more than 255 bytes. SQLSTATE=54008 db2 => create table obj(uri blob(256) not null primary key, db2 (cont.) => classname varchar(3200)); SQL0350N LOB, DATALINK, or structured type column "URI" cannot be used in an index, a key, a unique constraint, a generated column, or a declared temporary table. SQLSTATE=42962 " Is there a way to go beyond 255 in DB2? Anyone has encountered the limitation in these two databases, and has found a workaround? -- Michael Wang http://www.unixlabplus.com/ -- To unsubscribe, e-mail: <mailto:slide-user-unsubscribe@;jakarta.apache.org> For additional commands, e-mail: <mailto:slide-user-help@;jakarta.apache.org>
