How do I create a field in a table with that must be Unique?

[craig]

Hi, In BASE. I have created a table for my database for customers which works fine with all the fields etc and a Primary Key that auto increments for CustomerID which must be unique. How can I create another field that must be unique that will bring an error if someone tries to imput a repeated data entry.

For instance I am trying to create a field that displays the first four letters of the surname plus a number i.e. 01 for the first person with this surname. Then If there is another surname its should be 02…and so on. If someone tries putting in two of the same it should bring up an error. Can this be done? The field will presumably be a VARCHAR as I want it to be letters and numbers?

I can’t figure it out, please help…

[w_whalley]

You want to give your column a unique constraint. Here is a link to the general SQL method.

To apply this to the default embedded LibreOffice database, use these specific steps.

For example if your column is called MYCODE, then in the SQL tool (from the main menu choose Tools/SQL, and execute this command 'ALTER TABLE MYTABLE ADD CONSTRAINT MYCONSTRAINT UNIQUE (“MYCODE”);

[uglybug]

the link above " use these specific steps. " is no longer functioning.
several posts on this forum refer to this website which seems to be inactive.
http://www.oooforum.org/

[tohuwawohu]

There’s no GUI to do this; instead, use Menu “Tools” → “SQL...” with the following SQL command:

ALTER TABLE "Customers" ADD UNIQUE ("CustomerID");

or

ALTER TABLE "Customers" ADD CONSTRAINT customer_id_unique UNIQUE ("CustomerID");

You can find more info in the HSQLDB User Guide on SQL Syntax (ALTER TABLE).