Hi,

> CREATE TABLE USERS
> (
> USER_ID BIGINT NOT NULL,
> USER_NAME VARCHAR(100) NOT NULL,
> PASSWORD_HASH VARCHAR(64) DEFAULT '' NOT NULL,
> EMAIL_ADDRESS VARCHAR(100),
> PRINT_MAILBOX_NAME VARCHAR(100) NOT NULL,
> LASTLOGIN TIMESTAMP,
> SCAN_MAILBOX_ID BIGINT NOT NULL,
>
> PRIMARY KEY (USER_ID),
> UNIQUE (USER_NAME),
> UNIQUE (PRINT_MAILBOX_NAME),
> UNIQUE (SCAN_MAILBOX_ID)
> );
>
> When I try to create this table, I get the error
> "unsuccessful metadata update, key size too big for index"

From looking at the above DDL, I can't say that I think the
keysize would be too big.

What default characterset does this database have?

> I've read http://www.mers.com/IBINDEXLENGHT.HTML, which was the only
> data I could find on the subject. The way I see it, I have a few ways
> around this problem:
>
> 1. Break up this table into multiple tables, tie them together with
> foreign keys, and enforce uniqueness in each separate table. A view
> could be used to bring them together.

I have no idea why this should help.

> 2. Drop the UNIQUE constraints and write an ON UPDATE OR INSERT
> trigger that enforces uniqueness for each key by throwing an exception
> if it finds a duplicate.
>
> The first sounds icky from a table-design POV. I assume both would
> impose a performance penalty, although reads would be more freuent to
> this data then writes (except for the lastlogin).
>
> Is there a better way out of this? If not, which option would you
> recommend?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com