Split Database questions

I have converted my embedded Philately database to a Split Database to try and ensure data integrity. I have managed to get it up and running relatively well with a couple of minor issues.

The first does not appear to be causing any issues yet, but whenever I open the database I get a Macro error in the “setup” macro telling me the Variable is not defined. I resolved that by disabling “Option Explicit”, though, in future I might want to fix that problem.

This one, however is needed to ensure data integrity:
I have discovered in use that the new tables do not have the unique flag set on certain fields. As I have about 30 linked tables and about 3000 records in the master table so far, I do not want to have to spend another 2 hours re-creating all the tables. In simple terms, please, (I am new to Base and even newer to SQL) how do I convert said fields in each table to a unique value. i.e. (pseudo codes) set field myField in table myTable as Unique?

Thank You, Ratslinger, I knew it would be easy, just did not know how. Can you please point me at some good learning resources?

Hello,

The syntax to add a UNIQUE constraint is (done from menu Tools->SQL):

ALTER TABLE "tablename" ADD CONSTRAINT constraintname UNIQUE ("column")

To remove the constraint it is:

ALTER TABLE "tablename" DROP CONSTRAINT constraintname

constraintname is assigned by you. ie: my_constraint
Must be unique name in DB.

In the Base query section this statement will list all the table constraints:

 SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLE_CONSTRAINTS

Just a note. Always make a backup copy in case something goes wrong.

Did test above just before posting and all works. Best to be safe though.

@LSemmens,

When posting a comment, post below what you are responding to otherwise no one gets the notification. In other words, your comment only was set to notify you. Had I not looked at this post again, I would not be notified you responded.

Also, as you have been helped, please help others to know the question has been answered by clicking on the :heavy_check_mark: in upper left area of answer which satisfied the question.

For database info, it is always best to refer to the manual for the specific database you are using. Here is a link to HSQLDB v2.5 → HyperSQL version 2.5 Documentation.

Here is a link to more links → To learn LibreOffice Base are there introductions or tutorials?.

Sorry, Thanks again.