Ask Your Question

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

asked 2012-07-25 15:16:18 +0200

craig gravatar image

updated 2012-08-12 13:35:44 +0200

tohuwawohu gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-17 03:48:53.860793

2 Answers

Sort by » oldest newest most voted

answered 2012-07-25 16:39:19 +0200

w_whalley gravatar image

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");

edit flag offensive delete link more

answered 2012-07-25 16:20:37 +0200

tohuwawohu gravatar image

There's no GUI to do this; instead, use Menu "Tools" -> "SQL..." with the following SQL command:

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


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

edit flag offensive delete link more

Question Tools


Asked: 2012-07-25 15:16:18 +0200

Seen: 4,941 times

Last updated: Jul 25 '12