--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 12:23 PM 28/03/2009, you wrote:
> >Is there an alternative for the UNIQUE constraint?
>
> Not directly.

Nuts.

> 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...

I'm using 2.1.1 embedded, with the ADO.NET Data Provider v.2.5.0 Beta 1 for .NET 3.5/2.0 if it matters.

> 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.

Ok, I'll do that then.

> (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).

Why is that? I thought having a fixed-length string would be better since look ups would just require some simple arithmetic.

Though having thought more about it, I guess that having to compare two 260 char strings would be slower than comparing their lengths and then the strings themselves.

> 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.

Ah, thanks for the heads up. I've never really used databases before and guides for getting started don't mention this kind of stuff, so I just worked off the IB 6 language reference for what was and wasn't possible.
Though after upping the page size, the code I have now works, I'll assume it's because of the order in which I created the tables and that there are no circular dependencies. I've change it now though.