Ask Your Question
0

Split Database questions

asked 2019-09-02 04:40:18 +0200

LSemmens gravatar image

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?

edit retag flag offensive close merge delete

Comments

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

LSemmens gravatar imageLSemmens ( 2019-09-02 05:55:11 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-09-02 05:09:37 +0200

Ratslinger gravatar image

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
edit flag offensive delete link more

Comments

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 gravatar imageRatslinger ( 2019-09-02 05:13:15 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2019-09-02 06:39:08 +0200 )edit

Sorry, Thanks again.

LSemmens gravatar imageLSemmens ( 2019-09-02 06:57:33 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-09-02 04:40:18 +0200

Seen: 32 times

Last updated: Sep 02