Hi Pieter,

> imagine you have the following table:
>
> CREATE TABLE SUBSCRIPTION (
> SUBPK integer not null primary key,
> SUBURL varchar(1024) not null
> );
>
> I want to make the SUBURL a unique one, but since it's a varchar(1024), this
> doesn't work as the column length is too long.
>
> Is there anything else I can do from the database side to ensure this field
> stays unique? Can I accomplish something with triggers?

Yes, with a BEFORE INSERT and BEFORE UPDATE trigger, or if you
use Firebird 1.5, then you can do that with one trigger. Thanks
to the new multi-action-triggers.

For example, check in the trigger, if there already such an URL
exists. If yes, then simply throw an user-defined exception.

Something like that:


CREATE EXCEPTION EXC_NOURLDUP 'No URL duplicats allowed!';


SET TERM ^^ ;
CREATE TRIGGER TRI_SUBSCRIPTION_CHECKURL FOR SUBSCRIPTION ACTIVE BEFORE INSERT POSITION 32767 AS
begin
if (exists(select SUBURL from SUBSCRIPTION where SUBURL = new.SUBURL)) then
begin
exception exc_nourldup;
end
end
^^
SET TERM ; ^^


Hope that gives you an idea.


Best Regards,
Thomas Steinmaurer
LogManager Serie - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database and MS SQL Server
Upscene Productions
http://www.upscene.com