RimuDB Logo
Database Access Framework for Java

Database Identity Columns

Each type of database has a slightly different way of implementing identity columns. In some databases, the columns are designated by an SQL keyword such as 'AUTOINCREMENT' or 'IDENTITY' in the CREATE TABLE statement. In other databases, a sequence object is created in the database, and the column associated with the sequence. Each insert statement references the sequence object. RimuDB supports both types of database identity columns.

In order for RimuDB to be able to generate the correct SQL statements, it needs to have information in the table descriptor describing how an identity column should be handled. The table descriptors can be edited by hand, or the editor tool can be used to update them. The advantage of using the editor is that information in the format of the table descriptor XML is reliably generated each time it is saved.

In the editor tool, selecting the identity check box designates the column as an identity column. For databases that use just an SQL Keyword (MySQL, SQL Server, H2, HsqlDB) this is all that need to be done. However, for Postgres and Oracle, a sequence name must be entered in the editor.

 

Table Descriptor Without a Sequence

An example of using the editor for a Table Descriptor that does not require a sequence:

Editor Import 1

 


Example table descriptor XML created:

	
<?xml version="1.0" encoding="UTF-8"?>
<tabledescriptor xsi:schemaLocation="http://www.rimudb.org/schemas 
http://www.rimudb.org/schemas/tabledescriptor-3.0.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.rimudb.org/schemas">
    <information package="org.rimudb.testdb" name="OrderAttribute" table="ORDERATTRIBUTE"/>
    <column columnname="id" propertyname="id" size="10" keynumber="1" type="INTEGER" autoincrement="true"/>
    <column columnname="orderNr" propertyname="orderNr" size="10" type="INTEGER"/>
    <column columnname="name" propertyname="name" size="30" type="CHAR"/>
    <column columnname="value" propertyname="namevalue" size="30" type="CHAR"/>
</tabledescriptor>

 

Table Descriptor With a Sequence

An example of using the editor for a table descriptor that requires a sequence:

Editor Import 1

 

Example table descriptor XML created:

	
<?xml version="1.0" encoding="UTF-8"?>
<tabledescriptor xsi:schemaLocation="http://www.rimudb.org/schemas 
http://www.rimudb.org/schemas/tabledescriptor-3.0.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.rimudb.org/schemas">
    <information package="org.rimudb.testdb" name="OrderTransaction" table="ORDERTRANSACTION"/>
    <column columnname="ID" propertyname="id" size="10" keynumber="1" type="INTEGER" autoincrement="true" sequence="ORDERTRANSACTION_SEQ"/>
    <column columnname="orderNr" propertyname="orderNr" size="10" type="INTEGER"/>
    <column columnname="name" propertyname="name" size="30" type="CHAR"/>
    <column columnname="value" propertyname="value" size="30" type="CHAR"/>
</tabledescriptor>