[DRAFT[ Auto generated fields - Using AutoField

[DrewJensen]

This tutorial is specific to the embedded HSQLdb database engine in Base ( This is the default database type ) however, many of the techniques will translate into other relational database engines that Base will works with, such as PostgreSQL, MySQL and SQl Server, as well.

When creating a table for use in Base it is required that the table have a Primary Key field, if the table is going to be updatable via the data view window and forms. Perhaps the most common way to create this Primary Key field is with the use of an Auto_Incrementing integer field. In fact the Base table designer has support for this right in the table design window:

When an Auto_increment field is created this way the following is true:

1. Whenever a new record is added to the table a default value for this field will be created
2. The first value will be 0, for each record afterwards the value will be incremented by 1
3. Numbers will not be reused. ( until all possible values for an integer or bigint data type have been exhausted ) This means that if you add 3 records the ID field would hold the values 0,1,2. If you then deleted the 3rd record and inserted a new one the ID field would now hold the values 0,1,3
4. The text in the table design window is INACCURATE, values can be assigned to these fields directly, when done with an SQL command - this should be avoided however
5. The value is created WHEN the new record is inserted in the table. This means that in a form when you click on 'New Record' the field will not have the next value yet, instead it will have the string <AutoField>, telling you that a value will be created
6. The Base table designer will create this field using a special type specifier - IDENTITY
7. Only one of these auto_increment ( IDENTITY ) fields can used in a table

Now, before looking at other ways to create auto generated fields it is worthwhile to look at a few of the other features that come along with this IDENTITY type field. I already mentioned that when a record is added via a form that the field is marked as being an AutoField and in the form you can not actually enter data for the field. But what if you need to update the field using an SQL command.

For example - assume a table Contacts that has these fields:
ContactID IDENTITY, FirstName VARCHAR(50), LastName VARCHAR(50)

You could add records to this table using an SQL INSERT COMMAND such as this:

Code: Select all

INSERT INTO "Contacts" ( "FirstName", "LastName" ) VALUES ( "Ted", "Dancen" );

With that statement the ContactID field would be set to the next value because the field was not given a value. But you can also use this command:

Code: Select all

INSERT INTO "Contacts"  VALUES ( NULL, "Ted", "Dancen" );

That command does exactly the same thing as the first. Because I did not explicitly listing the field names I had to supply values for every field in the table and by using NULL for the ContactID field the database engine again supplied the next value.

How about another situation - Suppose I again have the Contacts table, but this time I also have an old SpreadSheet file with Contact information, First Name and Last Name in separate columns in the spreadsheet. Can I bring this old data into my table and still get the auto_incremented field value?

Yes I can actually. It works pretty much just like that first SQL command where I simply did not supply a value for the ContactID field. What I would do then is simply this.

• Open the Calc file with my old contact list.
  Highlight the values in the two columns that I want to move into the Base table and copy to the clipboard. ( Remember to insert a row before the first data if you need to, so that you can give a dummy name to each column, otherwise you will lose the first row of data on the import )
  Now go to the Base window, select the Contacts table and PASTE. This will bring up the Copy Table wizard.
  Select the Contacts table and 'Append data'
  What I will do then is to manipulate the lists in the wizard so that I am not supplying any value for the ContactID field:

[DrewJensen]

Alright then, before finishing with the default behavior of an IDENTITY just a couple more pieces of information.

In the example above I moved data from a Calc file into a Base table that already had an Auto_increment field (ContactID) created and the database added these generated values. Now what if I had moved the data in Base first with the Copy Table Wizard so that the table was created with only the two name fields:
Contacts ( FirstName VARCHAR(50), LastName VARCHAR(50) )

Could I still add an auto_incrementing field to this table?

Yes I can!

One way is to simply open the table design for editing, add a ContactID field to the end of the columns and set AutoValue to 'Yes'. When this column is added to the table then a value will be inserted for every record currently in the table.

Wow - look at that process again. I added a new column, defined it as an integer and set AutoValue to true - I DID NOT define it as a primary key for that table, but the table has a primary key now. Why?

I said earlier that the special type of IDENTITY can only be used once in any table, this is one of the reasons. With HSQLdb any column of type IDENTITY will automatically be defined as the primary key for the table.

NOTE If you try to add an AutoValue field to an existing table and also designate this as the Primary Key field the Base GUI designer will report an error. ( this is actually an error in the Base UI )

Finally, when working with these AutoField ( IDENTITY ) fields it is sometimes helpful to know what the last number assigned was. ( This is particularly useful when working with macros ). The HSQLdb engine offers a special function to get this value, IDENTITY(). The HSQLdb documentation describes the IDENTITY function as:

The last inserted value into an identity column for a connection is available using the function IDENTITY(), for example (where Id is the identity column):

It is important to understand this part of that definition, 'for a connection'. What this means is that the IDENTITY() function is not specific to any one table, but to the database as a whole and to the current connection.

To demonstrate what this means I will use 2 tables:
Table1 ( ID Identity, Name VARCHAR(50) )
Table2 ( ID Identity, Company VARCHAR(50 ) )


Now suppose I add 3 records to Table1. The ID field in Table1 would now have the values 0,1,2 and if I called the IDENTITY() function it would return the number 2, as this was the last identity value created.

OK - I now add 1 record to Table2. The ID field in Table2 has the value 0 and if I call the IDENTITY() function is would return the number 0.

Add another record to Table1 and call the IDENTITY() function again - it returns the number 3.

[DrewJensen]

OK - well, this is all fine and dandy - add an AutoField and it automatically adds 1 to an integer field.

What if that is not what you want however:

• Suppose you want the first number to be something other then 0 or the increment value to be something other then 1?
  How about a generated value that includes both text and numbers?

The answer to both questions is YES - but you must use the standard SQL command ALTER TABLE to achieve these.

To start I will look at changing the behavior for the IDENTITY type - if you recall this is the short hand name for the AutoValue field created with the table designer. HSQLdb allows two ways to declare a field as type IDENTITY - the short way and the long way:

• The short way FieldName IDENTITY
  The long way FieldName GENERATED BY DEFAULT AS IDENTITY ( START WITH <n>[, INCREMENT BY <m>])

The Base table designer uses the short way. This uses the default settings then and is equivalent to a command of GENERATED BY DEFAULT AS IDENTITY ( START WITH 0, INCREMENT BY 1).

OK - but what if I want my values to start with say 1000 and increment by 10 each time a new record is added. How can I do this if the table designer always uses the default settings. Well, it turns out that a field's definition can be altered even after a table has been created. ( as long as certain rules are followed ) To make this alteration Base allows me to execute standard SQL data definition commands against the database using the SQL window.

Alright then - remember the example table Contacts from the earlier post. The one we created by importing two columns FirstName and LastName from a Calc file. Last time I added the ContactID field using the GUI table designer. But if I where to use the actual SQL command instead then I could use the optional settings - AND since I am doing this with the SQL command I can also specifically tell the database to place the column at the beginning of the data row by using the BEFORE option.
So here is the command that is entered into the SQL window:

Code: Select all

ALTER TABLE "Contacts" ADD  "ContactID"  INTEGER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1000, INCREMENT BY 10 )  BEFORE "FirstName"

Looking at the table now I see that the results where exactly what I wanted.

NOTE This section will show the use of the DEFAULT clause for field definitions. Some readers may notice that in the GUI table designer there is a text box named 'Default Value', you may think this is used for the same purpose. IT IS NOT - the 'Default Value' entry in the GUI table designer is used solely for supplying a value for data aware controls attached to this field in Base data views and forms. The entry in this control is treated as a static string and can not be used to call functions or perform calculations when records are added to a table.