You may find that the database included in OpenOffice delights you as much as it has me. This page tries to help you use it.

Remember that Open Office, including ooBase, is free. Don't let that fool you, though. Big organizations, governmental and civilian, have adopted it as their standard office suite... and saving million$, but still Getting The Job Done. And making things easy for users on different platforms... Linux, Mac, Windows all spoken here!

There's more about ooBase in the main index to this material. Adabas? Star Office? Ancient history.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is fully explained, and there are more tips, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software, 3/06- 12/18.

Data validation: Overview

Many times there are only a limited number of valid entries for a given field in a table you have created. For example, if you have collected data on a group of people, and you have a reason to want to know each person's gender (and you are going to be "old fashioned" and only recognize two... please... can we proceed as if that would be acceptable?) then you'll have a field which should only hold "male" or "female", or some other pair of codes, e.g. "m"/"f".

Continuing with our example: It would not give a human any trouble if sometimes you recorded an individual as "male" and other times you recorded him as "man". Computers do not thrive on inconsistency. It would be best if you used one and only one code for each gender throughout the database. Table constraints are one tool you can use to guard against invalid entries. I have shown how ListBoxes and ComboBoxes can help elsewhere. In this essay, we explore table constraints, which I suspect are, in general, more valuable.

For this tutorial we will pretend that we've been given the job of keeping track of the names of the children attending a certain prestigious boarding school where every child is a member of one of the following "houses", (i.e. internal units of the school): Ravenclaw, Gryffindor, Hufflepuff or Slytherin.

Digression...

Since writing this essay about using TABLE constraints on data, I've become aware of another tool. It is a little more complicated, in some senses, but I like it much better than either the contraints imposed by ListBoxes, or the system discussed here. It is based on using foreign keys. In a nutshell, if you wanted to restrict the entries to just, say 'rav','gry','huf','sly' (for our Hogwart's example), you would create a separate table with just four records, holding those values. You would then tell the school list table ("HSL", in the material that follows shortly) that only something found in the lookup table is acceptable as a value for a pupil's house.

I have been struggling with these things for some time. Various tutorials touch on the issues. So many, that I wrote a "sub-index" for you of things that discuss what the "relational" means in the term "relational database"... the sort that is common today, not that you would know that they are relational, given the way some people use them. Among them, I would commend the one in which I talked about such things in a tutorial about keeping track of stock market investments.

If I were you, I would skip the essay you are reading, and go to one I wrote later, Using Foreign Keys to improve database validity, which is, I hope, better. (I hope I am using "Foreign Keys" properly in that title!) You can always come back here if you don't like that one! I like the technique explained in that, be "Foreign Keys" a proper name or not, better than the one I talk about here.

For some comments about "Foreign Keys", I would refer you to a document from Microsoft.

Back to work... digression ends...

We'll build a simple table, allocate "codes" to stand for the four houses, and create a constraint which will prevent us from entering an invalid value for the child's house.

The school list database

(I believe I've also used this database in at least one other tutorial, apart from the one on ListBox and ComboBox.)

Create the database.

Create a table in it. Call the table HSL. (For "Hogwart's School List")

In it, provide the following fields:

ID integer, auto value, primary key
Name, type Text(fix)[CHAR], length 30
House, type Text(fix)[CHAR], length 3

Create a form. You might as well use the wizard. (How appropriate!)

You only need a very basic form. Include all fields, use "datasheet" arrangement. Call it HSLwLookup for "Hogwart's School List With Lookup". Exit the wizard, electing "Work with the form" as what you will do next... even though you won't. After the wizard closes, close the form which will have just opened.

Don't put any data in the table yet. If you have entered some data, then make sure that no record in the "House" field holds anything other than "rav","gry","huf" or "sly".

Close the form, because we are going to work on the database some more... but by a route which may be unfamiliar.

Add constraints

In the main project manager window (the one with three panes, "Database", "Tasks" and another) click on the menu item "Tools", and choose "SQL". In the "Command to Execute" box, enter....

ALTER TABLE "HSL" ADD CONSTRAINT "House_List" CHECK ("House" IN ('rav','gry','huf','sly'));

... and you should get message...

"Command successfully executed."

(If you don't get that, check the data in the table... if any record has something other than 'rav','gry','huf','sly' in the "House" field, that... quite logically... will prevent the command from executing. Fix the offending records. Try again.

We'll talk about what that does in a moment. Before we do, notice:

HSL is just the name of the table we want to attach a constraint to.

"House" is a field in the table. "House_List" is a way of saying "the list of allowed values associated with the 'House' field". 'rav','gry', etc, are of course the list of acceptable values for the field, but notice the punctuation. Elsewhere we saw quotes ("s) and semicolons (;s).... now we have single quotes ('s) and commas. Sigh.

In your own databases, you will use different things in place of the "HSL", and in place of the "House"... but the rest will stay the same, even the "_List".

A detail: I think the names are case sensitive, i.e. if you called the field "House", as I told you to, you can't refer to it as "house".

What have we done? We've set a constraint on the table. You can now close the "Execute SQL Command" box and reopen the form. Try to enter an invalid value. Base will throw up a message box telling you....

"Error updating the current record. Check constraint violation House_List table: HSL in statement [UPDATE "HSL" SET "House"=? WHERE "ID"=?]

(Most of that, believe it or not, makes reasonable sense. It's a pity that the value you are trying to assign to "House" is replaced with the question mark, and that the record's ID is also replaced with a question mark, but otherwise, what you see is a taste of useful things to come.)

Just acknowledge the message, fix the value, and all will be well.

The table's constraint has triggered an exception event, and the database's default event handler has responded. I believe you could provide a different handler, one that would present a more user-friendly error message.... but that's another story for another time.

So far so good? I hope so. What I've presented can have its uses. However, as I said at the top... I am not convinced that neither what's here, nor "constaint by ListBox" is satisfactory.

Now I fear this page unravels somewhat. As I said... I hope what I presented is useable. But what follows will reveal some of my doubts about, problems with, this system.

The messy bits...

Here are some things that worry me when I use the technique described above, instead of using the foreign key constraint system which I normally use.

My questions/ worries...

Here's something I don't know how to do. Can anyone help? Is there a small tweak which would make that SQL command say that the House field can hold 'rav','gry','huf','sly', or be blank? I was hoping that something like...

... might be possible? (Email details at bottom of page.)

ALTER TABLE "HSL" ADD CONSTRAINT "House_List" CHECK ("House" IN ('rav','gry','huf','sly',NULL));

Removing constraints

I posted the following in an earlier version of this page:

"Help? I would welcome advice on the following....

"How can the designer remove a constraint after
 it has been applied?

"How can the database designer see what constraints
have been applied to a given table?"

I also posted the questions at the OpenOffice.Org Forum, and I soon had an answer from the marvelous Drew Jensen, who knows a lot about ooBase and is very generous in the time he devotes to supplying answers to people at the Forum. Until the line "end of material...", the following is heavily based on what Drew wrote. Any useful stuff probably came from him, but remember I've edited what he wrote, so problems may have been introduced which are not of his making!

(Start of material mostly written by Drew Jensen)....

1) To remove a constraint from a table, use "raw" SQL. I.e. Use use "Tools | SQL..." to issue a SQL command "by hand". The following, I hope, illustrates the relevant command:

(You would replace the HSL with the name of your table, and the House_List with the name of the constraint you wanted to drop.)

2) To add a constraint, Drew's method, using the Hogwart's School List example set up for this tutorial, again using "raw" SQL....

(That is very similar to what I had above...

ALTER TABLE "HSL" ADD CONSTRAINT "House_List" CHECK ("House" IN ('rav','gry','huf','sly'));

... but differs in using "House_constraint" where I used "House_List". I'm not sure about the pros and cons. I believe either "works" to achieve what we wanted!)

3) To list the constraints currently active in a table, Drew knew of only one way to do it with an HSQL embedded database... which is what you have when you use OpenOffice's database without "getting clever".

You have to use the SCRIPT command. With that, you can dump the database to a file. You can then read the constraints in that file.

To do that: Issue the following SQL command:

script 'c:\tmphsl.sql'

('c:\tmphsl.sql' is merely where the output is to be written. You can send the output to a different place or use a different filename.)

Oh... Girl Scout Cookies! That worked, previously. I'm now on a Windows 7 machine, and am getting "Exception: tmphsl.sql (Access is denied)". I've tried c:\tmphsl.sql and c:/tmphsl.sql, too... Still problems. Sigh.

(I think that's just a "simple" case of not trying to work directly with files in the root directory. Create a folder there (perhaps a saga in itself), perhaps, say, C:\MyRoot\, and then do what you like in that. (Not tested yet... let me know if it works for you?))

The command used to create a text file called c:\tmphsl.sql. The text file will contain all of the DDL and DML commands needed to rebuild the database. When Drew did the above using an example database he had, part of what he got was.....

CREATE CACHED TABLE "config"("CompName" VARCHAR(250),
"ContactName" VARCHAR(150),"ContactPhone" VARCHAR(10),
"ContactEmail" VARCHAR(100),
"SalesName" VARCHAR(100),"SalesInitials" VARCHAR(3),
"InvoiceSeed" INTEGER,"LastSynch" DATE,
CONSTRAINT "silly_constraint" CHECK("config"."SalesInitials" IN ('atj','ATJ','silly')

You can see a constraint he had added near the end of the above, where it is a part of the "create (table)" command.

Drew says that there is actually another way to check constraints using the system tables in the HSQL database (and thus, also, ooBase, usually) at runtime - but the simple dump and text search for the word constraint is easier to do using Base - and besides he could not remember what the system table and column name were when he was writing his helpful advice.

(End of material mostly written by Drew Jensen)

More help....

In July 07, I had two helpful contributions from Will. The first was some comments on the question of knowing what constraints have been applied....

(Start of adapted quote from Will)

The SQL standard includes a way to display all of the information about a particular table... you just "say"...

DESCRIBE "<TableName>"

... <TableName> being the name of the table you want the information about.

Unfortunately, HSQL's (and thus ooBase's) database engine used doesn't seem to implement this.... but the fact that a straightforward way to display a table's information exists in some SQLs should serve as encouragement to others to search farther for a way to do it in HSQL/ ooBase.

(End of adapted quote from Will)

I hope someone finds a better way to do it, and sends the information to me! (Editor)

Please help improve this tutorial?

Question: I asked the following question: How can I fix things so that, say, the pupil's name must begin with an H? A strange requirement, of course, but it would do to illustrate a process. I'm hoping something like.....

ALTER TABLE "HSL" ADD CONSTRAINT "Name" LIKE ("H*");

.... is possible. Why? Because if it is, the idea can be adapted to, for instance, be sure that a social security number fits the standard format (ddd-dd-dddd, where "d" is a digit)

Sadly, 10/12, Windows 7, OO 3.4.1, when I tried the above, I got...

1: Unexpected token: LIKE in statement [ALTER TABLE "HSL" ADD CONSTRAINT "Name" LIKE]

Answer(?):A user named Will kindly sent following, which I haven't tested yet, but believe should work...

(Start of another adapted quote from Will)

To do this, you use a subset of "regular expressions". (The term used in the narrow sense used by computer people)

Regular expressions in SQL are very easy:
Use "_" (underscore, not hyphen) to mark a place where any one character goes,
or use "%" to mark a place were one or more characters can go.

Thus, you had the query you asked about was done almost correctly, just change the "*" to a "%"...

ALTER TABLE "HSL" ADD CONSTRAINT "Name" LIKE ("H%");

To input US SSNs in your tables, use the following constraint:

ALTER TABLE "<TableName>" ADD CONSTRAINT "SSNFormat" LIKE ("___-__-____");

<TableName> being the name of the table you want to alter.

Sadly, at least one source says that "_" and "%" are the only regular expressions you can use in SQL.... you can't (maybe) avail yourself of all the other clever things that are possible with some of the excluded regular expressions... and, as mentioned above, at 10/12, my OO is refusing to recognize "LIKE". Sigh.

I got this information on the following webpage (googled "sql regular characters"), but there are plenty of pages that talk about it. The following is good, but read the bit at the bottom of it, "SQL Regular Expressions" first... Helpful SourceForge write up about regular expressions.

(End of adapted quote from Will)

Have you ever noticed how an answer just gives rise to more questions? I wonder if there's a way to say that the character must be a digit.... From my reading of Will's contribution, I believe that, if you set up the second constraint, you would find the following....

Allowed:
052-64-1234 (a valid US SSN)
aaa-bb-cccc (not a valid SSN)

NOT Allowed:
052 64 1234 (hyphens missing)
052-64-123 (too short)

... not all of which is as we would like it, of course.

Here's the link to my eddress one more time, to make helping everyone easy for you!

More Good Stuff

Here are some more barely edited scavengings from the web relevant to the above. This comes from...

http://www.oooforum.org/forum/viewtopic.phtml?t=167173&highlight=constraint+data+entry

In order to test the ideas below, I added a field called "YOB", for Year Of Birth, to the table "HSL". Data type integer.

Question: I am trying to limit the range on a numeric input on a form. I.e. if the user enters 2055 when the range is set from 1950-2050 it will return an error so that I can minimize data entry mistakes. I understand that this may need a SQL command. Any ideas?

Answer: On the form level you can do this with a numeric control which allows you to specify an minimum and a maximum (a "formatted control" I think it is called.)

Below form level (e.g. table/query/update) you can still enter arbitrary numbers within the range of the data type.

Try this command, using "Tools/ SQL" to issue it....

(The command has been "tweaked" to fit the Hogwart's example we've been working with. "HSL" and "YOB" would be different in a different context.)

ALTER TABLE "HSL" ADD CONSTRAINT "YOB_Limit" CHECK ("YOB" BETWEEN 1950 AND 2050)

(A detail.... Pedant, moi? The above ALLOWS 1950... but not, at least, 1949. Strictly speaking, I wouldn't say that 1950 is between 1950 and 2010. Would you? I imagine some young programmer would tell me that I should "know what he meant". Sigh.)

You can remove the constraint like this:

ALTER TABLE "HSL" DROP CONSTRAINT "YOB_Limit"

In HSQLDB such a constraint is limited to the field values of one row. It can not do any comparison between other rows nor tables.

To make a totally absurd test case, add two more fields to HSL: FirstYear and SecondYear, both of type integer. Then execute the following...

ALTER TABLE "HSL" ADD CONSTRAINT "YOB_Limit" CHECK ("FirstYear" <= "SecondYear")

Those fields make little sense in our database, but with the constraint just listed, we have a method to require that any number entered into "FirstYear" of a given record must be less than the number entered into "SecondYear" of that record. Very useful in some contexts. Just hard to create a reason to use that trick in the Hogwart's School List example! Thank you Will!

Note the slightly strange situation applying: The constraint is tied to the field YOB... which isn't involved in the numbers we are testing! But the constraint, as written, does "work"... except... it doesn't "complain" if one or the other of "FirstYear" or "SecondYear" is empty. (You could "fix" this by making both fields be of the "entry required" type... then the "failure" of the constraint would not arise. There's always some way to skin the cat... just be careful not to get too kludgy, won't you?)

Editorial Philosophy

I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.

I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted. Deta8ils at my page about how the material is split up and how it is posted.

Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also....

Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using the material for inmate education. Situations do exist where good internet connections are not possible!)

Translations are welcomed. Tell me about yours, so I can post links to it. (More information at the page about copyright waivers.)

Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE

If you liked this ooBase tutorial, see the main index for information other help from the same author.

Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.orgMostly passes. There were two "unknown attributes" in Google+ button code. Sigh.

. . . . . P a g e . . . E n d s . . . . .