how to make two attributes unique?

[Spezifikum <in...@spezifikum.com>]

Hi,
i have organisational units (ou) within divisions. The name within the 
division should be unique.
ou:
name=CHAR(80)
divisionID=INT CONSTRAINT OperationalUnitDivisionID_FK REFERENCES division

These two attributes should be unique in combination.
I only found many examples on how to make one attribute unique, but i 
guess this is a dumb newbie question - sorry.
As a side note: There never will be OUs nested within OUs.

Thanks
Malte

Re: how to make two attributes unique?

[José Ventura <st...@gmail.com>]

What you need is an UNIQUE constraint with more than one column.
Follow the reference on ALTER TABLE syntax here:
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj13590.html

In your case, the statement is:

ALTER TABLE my_table ADD CONSTRAINT unique_ou_per_div UNIQUE ( divisionId, ou )

Update: Just realized that the above documentation does not clearly
indicate that there can be multiple columns inside the parenthesis.
Maybe we should file a doc bug?

On Mon, Oct 10, 2011 at 5:19 AM, Spezifikum <in...@spezifikum.com> wrote:
> Hi,
> i have organisational units (ou) within divisions. The name within the
> division should be unique.
> ou:
> name=CHAR(80)
> divisionID=INT CONSTRAINT OperationalUnitDivisionID_FK REFERENCES division
>
> These two attributes should be unique in combination.
> I only found many examples on how to make one attribute unique, but i guess
> this is a dumb newbie question - sorry.
> As a side note: There never will be OUs nested within OUs.
>
> Thanks
> Malte
>