Split Database questions

[LSemmens]

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?

[LSemmens]

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

[Ratslinger]

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

[Ratslinger]

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.

[Ratslinger]

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

[LSemmens]

Sorry, Thanks again.