Is there any way to edit the sql which I assume is generated when a table is created using the wizard

[argy]

I’ve created a table using the wizard which includes a date record. I’ve got the data entry form working as I’d like it to work except that I now realise that, because the user is required to record data only once per day, it would be useful to prevent the user from entering multiple records for the same date.

I’m assuming that by limiting the date field to be a unique value I can achieve my objective.

Is there any way to access or otherwise edit the SQL that libreoffice must generate even when the table has been created in wizard or design mode?

I’m working with LibO 4.4.4.8 if I remember correctly…

[doug]

The solution is available via Tools → Index design. In the dialog box that appears, click the button to create a new index () and then select the desired column and click the unique box, then save. This will create a UNIQUE constraint on the column selected.

Alternately, it is possible to achieve the same result using Tools → SQL using the following command:

ALTER TABLE "Table1"
ADD CONSTRAINT "my_key" UNIQUE ("fld1")

However, it usually is safer to stick with the GUI when dealing with internal HSQLDB.

(if this answers your question, please accept the answer by clicking the check box () to the left)

[argy]

Hi Doug,

That worked perfectly thanks.

The alternative approach had to be applied because the GUI approach took issue with a system created key that it didn’t want to change (probably down to something that I failed to do…). It’s good to know that this facility exists. I could use the GUI to check that the change had been implemented which was nice to see.

I’m now left wondering if theres any way to send a mesage to the errant user in case they tey to duplicate a daily record along the lines that “The record for (show the date) has already been made. Please selcet another date”

Thanks again…

[doug]

A form provides several ways to enforce integrity which are user-friendly. In this situation I usually would test the input by reading the .Date of the form control, running a SQL statement to check for duplicates, and then preventing the user from updating or leaving. That is a lot of work in this situation to get a custom box. Instead consider the On Error Form event, which hopefully passes the error to the macro, searching the message for the right text, doing MsgBox etc …