Search This Blog

Friday, 3 February 2012

Creating Column, Table and Database level constraints

In earlier posts we saw how to use the hibernate hbm2ddl tool. We were able to create column constraints set unique column constraints and add not-null checks. In addition to those we saw before, Hibernate also allows creation of table level constraints such as foreign keys.
These varied constraints help ensure that the integrity of the data is maintained.To ensure the data consistency, Hibernate provides support for the following SQL constraints:
  1. Column constraints: These constraints restrict the data that can be added to a column.
  2. Table constraints: These constraints apply to a single row or multiple rows.
  3. Database constraints: These constraints ensure the rules are observed when forming relations between the rows of different tables.
I implemented two classes with the varying constraints available. These constraints were specified in the hbm files. 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.data.model">
    <class name="User" check="len(NAME)>= len(LAST_NAME)">
        <id name="id" type="long">
            <generator class="native" />
        </id>
        <property name="name">
            <column name="NAME" not-null="true" unique-key="USER_UK_1" />
        </property>
<property name="lastName">
            <column name="LAST_NAME" not-null="true" unique-key="USER_UK_1" />
        </property>
        <property name="weight" type="big_decimal">
            <column name="WEIGHT" scale="2" precision="5" default="1.0" />
        </property>

        <property name="userCode" type="integer">
            <column name="USER_CODE" not-null="true" check="(USER_CODE>0)" />
        </property>

        <many-to-one name="country" class="Country" foreign-key="USER_FK1">
            <column name="COUNTRY_ID"></column>
        </many-to-one>
    </class>
</hibernate-mapping>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.data.model">
    <class name="Country">
        <id name="id" type="long">
            <generator class="native" />
        </id>
        <property name="name">
            <column name="NAME" not-null="true" unique-key="COUNTRY_UK_1" />
        </property>

        <set name="users" cascade="all" inverse="true">
            <key column="COUNTRY_ID" on-delete="cascade"></key>
            <one-to-many class="User" />
        </set>
    </class>
</hibernate-mapping>
The column level constraints have been highlighted in red.These include setting the scale and precision for big decimal types. Using the "default" attribute allows setting the default values for the columns. The "not-null" attribute is also a special type of column constraint. It is also possible to define a business rule using the check constraint. For user_code property a check has been set that the value must be a positive integer.
The table level constraints have been highlighted in blue. For the user table a "check" constraint has been set indicating that the length of the name property is greater than the last_name property. The unique property is also a type of table level constraint. This is set using the "unique-key" attribute in the column element. Here a unique constraint has been added that the combination of name and last_name must be unique.
The database level constraints have been highlighted in purple.The most common rules that span multiple tables are foreign keys. Here a foreign-key attribute has been added to the column element for country property of user. This ensures that the DDL includes a foreign key of the same name. If not specified the foreign key generated will have a randomly generated name.
There is also an "on-delete" attribute available which allows us to specify what should the action be when a record is deleted from the main table. Should the database also delete records in the table where this value was a foreign key? Or should it simple ignore and throw the database exception if any such relations are present?In the hbm for country table we have specified that on delete of a particular record from Country table, all user records for that country must also be removed from the Users table. In the above case the cascade delete settings of Hibernate aren't used. Instead a single query "Delete from country" manages removal of records from both tables.
The SQL script that was generated by the Hibernate tool is as below:
create table Country (
        id integer not null auto_increment,
        NAME varchar(255) not null,
        primary key (id),
        unique (NAME)
    )
    create table User (
        id bigint not null auto_increment,
        NAME varchar(255) not null,
        LAST_NAME varchar(255) not null,
        WEIGHT numeric(5,2) default 1.0,
        USER_CODE integer not null check ((USER_CODE>0)),
        COUNTRY_ID integer,
        primary key (id),
        unique (NAME, LAST_NAME),
        check (len(NAME)>= len(LAST_NAME))
    )
    alter table User 
        add index USER_FK1 (COUNTRY_ID), 
        add constraint USER_FK1 
        foreign key (COUNTRY_ID) 
        references Country (id)

3 comments:

  1. What dialect are you using to generate the schema?
    Have you tried other than in memory DB (no JavaDB nor HSQLDB)?

    ReplyDelete
  2. Hi,I executed all examples using MySQL.

    ReplyDelete
  3. Hi,
    Very useful stuff.

    I have a question, can we add multiple check constraints?

    ReplyDelete