I’m using Libre Office 4.3.5.2 Base with the inbuilt HSQL. (on Windows XP)
I am setting up a database, creating the tables in design view. But some of the commands need to be input via Tools/SQL e.g. adding table constraints such as UNIQUE or CHECK. I am naming my constraints and keeping a list of them. But what I can’t see how to do is to look at the underlying SQL so that I can check up on previously entered constraints. Is there a way of doing this?
many thanks
Ruth
Insert > Query (SQL view) with SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES" to get available system information. In your case you need probably Insert > Query (SQL view) with SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_TABLE_CONSTRAINTS".
Thank you, that has helped. It gives me the name and type of the constraint. Is it possible to get precise details of the constraint e.g. the range to which I have restricted a set of values?
Have you already looked at the other system informations? Perhaps SELECT * FROM “INFORMATION_SCHEMA”.“SYSTEM_CHECK_CONSTRAINTS” will work for you.
Sorry about the late reply. SELECT * FROM “INFORMATION_SCHEMA”.“SYSTEM_CHECK_CONSTRAINTS” is perfect. It displays the check clause for each check cosntraint. Thank you so much,
Also, if you want to do it using an updated version of HSQLDB (version 2.3.x) then try this code (either as a saved query or execute in the Tools/SQL window with ‘show output of select statements’ ticked)…
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
The above code is a bit different to that given by Regina.
Thank you for the reply. I’m not sure how to find out which version of HSQLDB I’ve got, but I haven’t upgraded anything from what came with LibreOffice. I tried your code, but it didn’t work with my version.
OK. Since you are using the default Base installation (which uses the outdated HSQLDB version 1.8 as the ‘engine’), the above code is not relevant. If you ever do upgrade your installation - see here - then the above code will work. TIP: Make sure you backup your database regularly, because the default ‘embedded’ configuration is prone to corruption.
The other answers here didn’t work for me.
Instead see this question and answer..
What I learned, was that the default embedded database for LO 5.2.3.3 is the old HSQLDB, version 1.8.0.
Find the version information in the file properties in folder database in the *.odb file. Remember, that the *.odb file is a simple zip-container and you can look into it using a packer like 7-Zip.
@Regina, this is much easier. Thanks! And now not to diminish your answer, but just to note that the other answer linked to above is still worth something because it provides the version for both embedded, and split at the same time which might be helpful at times. (I guess it depends on what “underlying” means in this question). In other words, your answer is short and quick, but only seems to provide the version for the embedded, but not split db. Still thanks as it very much helps.