At 12:23 PM 28/03/2009, you wrote:

>Is there an alternative for the UNIQUE constraint?

Not directly.

>I tried to create a table using the following, but I got an error message saying an index is too long or something:
>
>CREATE TABLE tblFiles (
> ID INTEGER
> NOT NULL
> PRIMARY KEY
> CHECK (VALUE >= 0),
> Filename CHAR (260) CHARACTER SET UTF8
> NOT NULL,
> Volume INTEGER
> NOT NULL
> DEFAULT -1
> CHECK (VALUE >= -1)
> REFERENCES tblVolumes (ID)
> ON UPDATE SET DEFAULT
> ON DELETE SET DEFAULT,
>
> UNIQUE (Volume, Filename)
>);

You haven't said what version of Fb you're using. All versions have limits on the total byte-count of the keys of an index; pre 2.x it is 252 BYTES. V2.x it is a quarter of the page size. Since you're using charset UTF8, it's not (or shouldn't be) v.1.5.x, 'cuz it won't work...

Now, regardless, you have to allow 4 bytes for each UTF8 character. There are also bytes eaten for its being a compound index. Ivan Prenosil has a neat little calculator at his site for figuring out whether your index will work or not for your page size: see http://www.volny.cz/iprenosil/interbase/ip_ib_indexcalculator.htm
By that calculation, you will need a page size of 8192 to enable the index for that constraint to happen. (CHAR (N) is the wrong choice of data type for this, by the by, although it doesn't affect the max. size of the index).

>I just want to enforce all Volume/Filename pairs to be unique. As this is for a program which I'm making to track what files I stuff on each DVD I make, Filename may not necessarily be unique by itself, because I might have a "\blah.ext" on multiple disks.

Another comment: that's not a valid table declaration anyway...you have to declare a FOREIGN KEY constraint explicitly and it's not a great idea to try and declare FK constraints in the same transaction as you define the table...it will likely cause your next exception, after you deal with the index overflow issue.

./heLen