Foreign key constraints in hbm2ddl CREATE TABLE

tpischke · **Posted:** Sat Mar 12, 2005 10:23 am

After successfully converting our project to Hibernate using bottom-up engineering, I'm now in the process of switching over to top-down engineering and struggling through the hbm2ddl growing pains.

As part of the process, I'm working on a FirstSQLDialect which I will later submit to Hibernate, should you have interest.

My Question:
FirstSQL does not, at the time of writing, support the ALTER TABLE syntax, thus the creation of foreign key constraints via this syntax results in syntax exceptions.

FirstSQL -does- support foreign key constraints declared in CREATE TABLE statements. Can I coax hbm2ddl into generating foreign key constraints in the following manner?

CREATE TABLE author (
book_metadata_key INT NOT NULL,
author_key INT NOT NULL,
name CLOB NOT NULL,
address CLOB,
email CLOB,
url CLOB,
PRIMARY KEY (author_key),
FOREIGN KEY (book_metadata_key) REFERENCES book_metadata (book_metadata_key)
);

Hibernate version: 3.0 rc1

Mapping documents:
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-cascade="none" default-access="property" auto-import="true">


<class name="sb.biz.model.Author" table="author" mutable="true" polymorphism="implicit" dynamic-update="false" dynamic-insert="false" batch-size="1" select-before-update="false" optimistic-lock="version">
<meta attribute="class-description" inherit="false">
@hibernate.class
table="author"
</meta>
<meta attribute="implement-equals" inherit="false">true</meta>

<id name="authorKey" type="java.lang.Integer" column="author_key" unsaved-value="null">
<meta attribute="field-description" inherit="true">
@hibernate.id
generator-class="increment"
type="java.lang.Integer"
column="author_key"

</meta>
<generator class="increment"/>
</id>

<property name="name" type="text" column="name" not-null="true" unique="false" update="true" insert="true">
<meta attribute="use-in-tostring" inherit="true">true</meta>
<meta attribute="use-in-equals" inherit="true">true</meta>
<meta attribute="field-description" inherit="true">
@hibernate.property
column="name"
not-null="true"
</meta>
</property>
<property name="address" type="text" column="address" not-null="false" unique="false" update="true" insert="true">
<meta attribute="use-in-tostring" inherit="true">true</meta>
<meta attribute="field-description" inherit="true">
@hibernate.property
column="address"
</meta>
</property>
<property name="email" type="text" column="email" not-null="false" unique="false" update="true" insert="true">
<meta attribute="use-in-tostring" inherit="true">true</meta>
<meta attribute="field-description" inherit="true">
@hibernate.property
column="email"
</meta>
</property>
<property name="url" type="text" column="url" not-null="false" unique="false" update="true" insert="true">
<meta attribute="use-in-tostring" inherit="true">true</meta>
<meta attribute="field-description" inherit="true">
@hibernate.property
column="url"
</meta>
</property>




<many-to-one name="bookMetadata" class="sb.biz.model.BookMetadata" not-null="true" unique="false" outer-join="auto" update="true" insert="true">
<meta attribute="use-in-equals" inherit="true">true</meta>
<meta attribute="field-description" inherit="true">
@hibernate.many-to-one
not-null="true"
@hibernate.column name="book_metadata_key"
</meta>
<column name="book_metadata_key"/>
</many-to-one>

</class>
</hibernate-mapping>

Full stack trace of any exception that occurs:

Syntax error at 23
alter table author add constraint FKAC2D218BC9348A1C foreign key ( book_m

COM.FirstSQL.Dbcp.JdbcException: Syntax error at 23
at COM.FirstSQL.Dbcp.JdbcStatement.execute(JdbcStatement.java:86)
at sb.data.DatabaseBuilder.execSql(DatabaseBuilder.java:136)
at sb.data.DatabaseBuilder.importFile(DatabaseBuilder.java:121)
at sb.data.DatabaseBuilder.build(DatabaseBuilder.java:54)
at sb.data.DatabaseBuilder.build(DatabaseBuilder.java:47)
at sb.data.DatabaseBuilder.main(DatabaseBuilder.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:78)
Exception in thread "main"

Name and version of the database you are using: FirstSQL 3.00 beta2

The generated SQL (show_sql=true):
create table author (
author_key integer not null,
name clob not null,
address clob,
email clob,
url clob,
book_metadata_key integer,
primary key (author_key)
);

alter table author add constraint FKAC2D218BC9348A1C foreign key (book_metadata_key) references book_metadata;

tpischke · **Posted:** Sat Mar 12, 2005 11:13 am

I'll keep this topic going, journal-style, as I work on a solution.

I'm perusing the source code, and it looks like the classes of interest are org.hibernate.cfg.Configuration and org.hibernate.mapping.Table.

I'm thinking I may need to override the following method in Configuration:

Code:

public String[] generateSchemaCreationScript(Dialect dialect) throws HibernateException {
      secondPassCompile();

and the following method in Table:

Code:

public String sqlCreateString(Dialect dialect, Mapping p, String defaultCatalog, String defaultSchema) throws HibernateException

I would modify the latter to insert foreign key constraints within the CREATE TABLE statement, and the former to skip the ALTER TABLE statements in this case.

If I don't hear from someone on the Hibernate Team, I'll assume this is the preferred approach.

tpischke · **Posted:** Sat Mar 12, 2005 11:55 am

Hmm, may be necessary to modify Table source directly, since the Table instance is created in the Mappings class which does not appear to be configurable to instantiate a subclass of Table.

Should be able to confine Configuration changes to a subclass, since I have control over its instantiation.

max · **Posted:** Sat Mar 12, 2005 12:15 pm

if one wants to change something in the sql creation of tables then one should make it in such a way that the dialect controls wether or not a certain construct is used or not.

so you should simply update Table to do its stuff differently and add a method to the dialect that states wether or not one or the other mechanism should be used.

/max

tpischke · **Posted:** Sat Mar 12, 2005 12:21 pm

agreed. I'll modify Table in such a way that it asks the Dialect whether to create foreign keys in the CREATE TABLE statement, or in separate ALTER TABLE statements.

This would mean adding at least one new method to the Dialect Class, something like:

Code:

boolean createForeignKeysInCreateTableStatement(){
    return false; // default for current hibernate behavior
}

max · **Posted:** Sat Mar 12, 2005 12:34 pm

and then assure that alter statements isnt executed...

tpischke · **Posted:** Sat Mar 12, 2005 12:35 pm

alternatively, Table could infer the FK creation strategy from the existing Dialect.hasAlterTable()

max · **Posted:** Sat Mar 12, 2005 12:37 pm

probably yes - but please remember that it is not possible to define cyclic foreign keys without alter table syntax....so some constraints will not be "creatable"

tpischke · **Posted:** Sat Mar 12, 2005 12:40 pm

Good Point. Our app does not have cyclic foreign keys. I suppose, by extension of this observation, that FirstSQL does not support cyclic foreign keys.

tpischke · **Posted:** Sat Mar 12, 2005 1:55 pm

works. Here's the first-draft modified Table method. I may still need to add similar support for check constraints in the CREATE TABLE statement, and clean everything up, but I am successfully generating a working database complete with foreign keys using hbm2dll. :)

Since I chose to use the Dialect.hasAlterTable() method, there is no need to change Configuration to suppress the ALTER TABLE statements.

Code:

public String sqlCreateString(Dialect dialect, Mapping p, String defaultCatalog, String defaultSchema) throws HibernateException {
        StringBuffer buf = new StringBuffer("create table ").append(getQualifiedName(dialect, defaultCatalog, defaultSchema)).append(" (");

        boolean identityColumn = idValue != null && idValue.createIdentifierGenerator(dialect, defaultCatalog, defaultSchema, null) instanceof IdentityGenerator;

        // Try to find out the name of the primary key to create it as identity if the IdentityGenerator is used
        String pkname = null;
        if (primaryKey != null && identityColumn) {
            pkname = ((Column) primaryKey.getColumnIterator().next()).getQuotedName(dialect);
        }

        Iterator iter = getColumnIterator();
        while (iter.hasNext()) {
            Column col = (Column) iter.next();

            buf.append(col.getQuotedName(dialect)).append(' ');

            if (identityColumn && col.getQuotedName(dialect).equals(pkname)) {
                // to support dialects that have their own identity data type
                if (dialect.hasDataTypeInIdentityColumn()) {
                    buf.append(col.getSqlType(dialect, p));
                }
                buf.append(' ').append(dialect.getIdentityColumnString(col.getSqlTypeCode(p)));
            } else {
                buf.append(col.getSqlType(dialect, p));
                if (col.isNullable()) {
                    buf.append(dialect.getNullColumnString());
                } else {
                    buf.append(" not null");
                }
            }

            if (col.isUnique()) {
                if (dialect.supportsUnique()) {
                    buf.append(" unique");
                } else {
                    UniqueKey uk = getOrCreateUniqueKey(col.getQuotedName(dialect) + '_');
                    uk.addColumn(col);
                }
            }
            if (col.hasCheckConstraint() && dialect.supportsColumnCheck()) {
                buf.append(" check (").append(col.getCheckConstraint()).append(")");
            }
            if (iter.hasNext()) {
                buf.append(", ");
            }

        }
        if (primaryKey != null) {
            /*if ( dialect instanceof HSQLDialect && identityColumn ) {
            // skip the primary key definition
            // ugly, ugly hack!
            }
            else {*/
            buf.append(", ").append(primaryKey.sqlConstraintString(dialect));
            //}
        }

        if (dialect.supportsUniqueConstraintInCreateAlterTable()) {
            Iterator ukiter = getUniqueKeyIterator();
            while (ukiter.hasNext()) {
                UniqueKey uk = (UniqueKey) ukiter.next();
                buf.append(", ").append(uk.sqlConstraintString(dialect));
            }
        }
        
        //  **** NEW CODE START ****
        if (foreignKeys != null && !dialect.hasAlterTable()) {
            System.out.println("iterate fks");
            for (Iterator fkIter = foreignKeys.values().iterator(); fkIter.hasNext();) {
                ForeignKey fk = (ForeignKey) fkIter.next();
                if (fk.isPhysicalConstraint()) {
                    buf.append(", foreign key (");
                    for (Iterator fkColIter = fk.getColumnIterator(); fkColIter.hasNext();) {
                        final Column column = (Column) fkColIter.next();
                        buf.append(column.getName());
                        if (fkColIter.hasNext()) {
                            buf.append(", ");
                        }
                    }
                }
                buf.append(") references ");
                buf.append(fk.getReferencedTable().getName() + " (");
                Iterator fkRefIter = fk.getReferencedTable().getPrimaryKey().getColumnIterator();
                while (fkRefIter.hasNext()){
                    final Column column = (Column) fkRefIter.next();
                    buf.append(column.getName());
                    if (fkRefIter.hasNext()) {
                        buf.append(", ");
                    }
                }
                buf.append(")");
            }
        }
        // **** NEW CODE END ****


        /*Iterator idxiter = getIndexIterator();
        while ( idxiter.hasNext() ) {
        Index idx = (Index) idxiter.next();
        buf.append(',').append( idx.sqlConstraintString(dialect) );
        }*/

        if (dialect.supportsTableCheck()) {
            Iterator chiter = checkConstraints.iterator();
            while (chiter.hasNext()) {
                buf.append(", check (").append(chiter.next()).append(')');
            }
        }

        return buf.append(')').append(dialect.getTableTypeString()).toString();
    }

max · **Posted:** Sat Mar 12, 2005 2:32 pm

patches should be put in our jira

tpischke · **Posted:** Sat Mar 12, 2005 5:46 pm

It turns out that the latest release of FirstSQL (3.00) has added support for the ALTER TABLE statement.

I just tested hbm2ddl with the latest release from the FirstSQL website, and foreign key generation now works fine without any need for the patch in my previous post.

Given this, I won't bother submitting the patch through JIRA, unless somebody else thinks it would be useful.

Fesperman · **Posted:** Sat Mar 12, 2005 5:54 pm

The new version of FirstSQL/J (3.00) does support ALTER TABLE ADD CONSTRAINT. We use SQL92 format for adding constraints. It does require your physical database be in 3.00 format since new system tables were added to definition_schema to support constraint names. Your problem was that you were using a pre-3.00 physical database (already communicated via private email). Version 3.00 creates databases in 3.00 format by default.

FirstSQL/J Version 3.00 is in public beta, but it is a very solid beta (release quality). We added ALTER TABLE ADD CONSTRAINT in response to Hibernate users. For more info and download links, go to http://www.firstsql.com/pr300beta.html.

Note about cyclic keys: FirstSQL/J has always supported cyclic keys of all varieties. See http://www.firstsql.com/ixrefer.htm. In the past, we supported cyclic keys by allowing you to create the referencing table first and then to create the referenced table (it hooked up the keys at that point). In 3.00, you can do it with ALTER TABLE. We haven't yet updated ixrefer.htm with info about the new facility.

We encourage everyone to try the new version of FirstSQL/J. If you're using Java, you should try a Java DBMS. (Excuse the blatant advertising!)