One to one relation and column index.

View: New views

2 Messages — Rating Filter: Alert me

One to one relation and column index.

by Mirko-8 :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Hello,

I am developing a web app with - among other - two tables, Form and Info, in a one-to-one relationship.

@Entity
@Table(name="form")
@Indexed
public class Form implements Serializable, Cloneable {

    [... other fields ...]

    private Info info;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @PrimaryKeyJoinColumn
    @IndexedEmbedded(depth = 2)
    public Info getInfo() {
        return info;
    }

    public void setInfo(Info info) {
        this.info = info;
    }

    [... other getters and setters ...]
}

@Entity
@Table(name="info")
public class Form implements Serializable, Cloneable {

    [... other fields ...]

    private Form form;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "info")
    public Form getForm() {
        return form;
    }

    public void setForm(Form form) {
        this.form = form;
    }

    [... other getters and setters ...]
}

I have around 300 installations of the application working fine, but two of them have reported a problem that, ultimately, is due to a misalignment of these two tables. Here's one of the "problematic" script file, where it is evidentt that the two tables start with different indexes:

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE CACHED TABLE CAT_DATA(CAT_ID INTEGER NOT NULL,DATA_ID INTEGER NOT NULL,PRIMARY KEY(CAT_ID,DATA_ID))
CREATE CACHED TABLE COM_DATA(COM_ID INTEGER NOT NULL,DATA_ID INTEGER NOT NULL,PRIMARY KEY(COM_ID,DATA_ID))
CREATE CACHED TABLE EVENT_FORMS(EVENT_ID INTEGER NOT NULL,FORM_ID INTEGER NOT NULL,EVENT_SHORTCODE VARCHAR(255) NOT NULL,PRIMARY KEY(EVENT_ID,EVENT_SHORTCODE))
CREATE CACHED TABLE FORM_EVENTS(FORM_ID INTEGER NOT NULL,EVENT_ID INTEGER NOT NULL,FORM_SHORTCODE VARCHAR(255) NOT NULL,PRIMARY KEY(FORM_ID,FORM_SHORTCODE))
CREATE CACHED TABLE VES_DATA(VES_ID INTEGER NOT NULL,DATA_ID INTEGER NOT NULL,PRIMARY KEY(VES_ID,DATA_ID))
CREATE CACHED TABLE ACTION(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,ACTIONADDED TIMESTAMP,COMMENT VARCHAR(255),TITLE VARCHAR(255),ACTIONPLAN_ID INTEGER,ACTIONCREATOR VARCHAR(255),ACTIONCREATORUID VARCHAR(255),ACTIONEERNAME VARCHAR(255),ACTIONEERUID VARCHAR(255),ACTUALACTION VARCHAR(255),DATECOMPLETED TIMESTAMP,PROPESEDDUEDATE TIMESTAMP,PROPOSEDACTION VARCHAR(255),FORM_ID INTEGER,ACTION_INDEX INTEGER)
CREATE CACHED TABLE ACTIONPLAN(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,EVENT VARBINARY(255))
CREATE CACHED TABLE ATTACHMENT(ID VARCHAR(255) NOT NULL PRIMARY KEY,COMMENT VARCHAR(255),DELETEME BOOLEAN NOT NULL,FIELDNAME VARCHAR(255),HASTHUMBNAIL BOOLEAN,NAME VARCHAR(255),FORM_ID INTEGER,ATTACHMENT_INDEX INTEGER)
CREATE CACHED TABLE BOOLEANFIELD(ID INTEGER NOT NULL PRIMARY KEY,VALUE BOOLEAN)
CREATE CACHED TABLE CALENDARFIELD(ID INTEGER NOT NULL PRIMARY KEY,TIMEZONEOFFSET INTEGER NOT NULL,VALUE TIMESTAMP)
CREATE CACHED TABLE DATEFIELD(ID INTEGER NOT NULL PRIMARY KEY,VALUE TIMESTAMP)
CREATE CACHED TABLE DOUBLEFIELD(ID INTEGER NOT NULL PRIMARY KEY,VALUE DOUBLE)
CREATE CACHED TABLE DROPDOWN(ID VARCHAR(255) NOT NULL PRIMARY KEY,NAME VARCHAR(255))
CREATE CACHED TABLE DROPDOWN_DROPDOWNELEMENTS(DROPDOWN_ID VARCHAR(255) NOT NULL,ELEMENT VARCHAR(255),CONSTRAINT FK79320676257EDD44 FOREIGN KEY(DROPDOWN_ID) REFERENCES DROPDOWN(ID))
CREATE CACHED TABLE EVENT(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,CURRENTSTATE VARCHAR(255),SHORTCODE VARCHAR(255),ACTIONPLAN_ID INTEGER,CONSTRAINT FK5C6729A8FB4F804 FOREIGN KEY(ACTIONPLAN_ID) REFERENCES ACTIONPLAN(ID))
CREATE CACHED TABLE FIELD(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255),FORM_ID INTEGER)
CREATE CACHED TABLE FORM(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,SHORTCODE VARCHAR(255),TIMEZONEOFFSET BIGINT NOT NULL,EVENT_ID INTEGER,SEQUENCENUMBER BIGINT,CONSTRAINT FK300CC4C50DA2B0 FOREIGN KEY(EVENT_ID) REFERENCES EVENT(ID))
CREATE CACHED TABLE IFMC(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255))
CREATE CACHED TABLE IFMCA(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255),CATEGORYTYPE VARCHAR(255))
CREATE CACHED TABLE IFMD(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,EVENTTYPE VARCHAR(255),FLOWID VARCHAR(255),NAME VARCHAR(255),RETENTIONMONTHS INTEGER NOT NULL,THREEDIGITID VARCHAR(255),REVISIONDATE VARCHAR(255),REVISIONNUMBER INTEGER,IFORMMETACATEGORY_ID INTEGER,FORMCOUNT BIGINT,CONSTRAINT FK31477482CAE50 FOREIGN KEY(IFORMMETACATEGORY_ID) REFERENCES IFMCA(ID))
CREATE CACHED TABLE IFMVT(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VESSELTYPE VARCHAR(255))
CREATE CACHED TABLE INFO(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,CREATED TIMESTAMP,CREATEDBY VARCHAR(255),CURRENTSTATE VARCHAR(255),LASTREVIEWED TIMESTAMP,NAME VARCHAR(255),IFM_ID INTEGER,CLOSEDBY VARCHAR(255),CONSTRAINT FK3164AE478C693C FOREIGN KEY(IFM_ID) REFERENCES IFMD(ID))
CREATE CACHED TABLE INFORMATION(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,CREATED TIMESTAMP,INFFROM VARCHAR(255),INFOTYPE VARCHAR(255),MESSAGE VARCHAR(1000),READ TIMESTAMP,TITLE VARCHAR(255),INFTO VARCHAR(255))
CREATE CACHED TABLE INFORMATION_INFOIDS(INFORMATION_ID INTEGER NOT NULL,ELEMENT INTEGER,CONSTRAINT FK8258FB37C63A00F0 FOREIGN KEY(INFORMATION_ID) REFERENCES INFORMATION(ID))
CREATE CACHED TABLE INTEGERFIELD(ID INTEGER NOT NULL PRIMARY KEY,VALUE INTEGER,CONSTRAINT FKC52E367CB4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID))
CREATE CACHED TABLE STRINGFIELD(ID INTEGER NOT NULL PRIMARY KEY,VALUE VARCHAR(1000),CONSTRAINT FKA657A389B4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID))
CREATE CACHED TABLE IFMD_FUNCTIONS(IFMD_ID INTEGER NOT NULL,ELEMENT INTEGER,CONSTRAINT FKBB058890F53ABA38 FOREIGN KEY(IFMD_ID) REFERENCES IFMD(ID))
CREATE CACHED TABLE IFMD_GSMSIDS(IFMD_ID INTEGER NOT NULL,ELEMENT VARCHAR(255),CONSTRAINT FK8A1401BF53ABA38 FOREIGN KEY(IFMD_ID) REFERENCES IFMD(ID))
CREATE CACHED TABLE IFMD_UFNAME(IFMD_ID INTEGER NOT NULL,UFNAME VARCHAR(255),LOCALE_NAME VARCHAR(255) NOT NULL,PRIMARY KEY(IFMD_ID,LOCALE_NAME),CONSTRAINT FK8B0277E7F53ABA38 FOREIGN KEY(IFMD_ID) REFERENCES IFMD(ID))
CREATE CACHED TABLE RESPONSE(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,COMMENT VARCHAR(255),COMMENTERNAME VARCHAR(255),COMMENTERUID VARCHAR(255),DATEOFCOMMENT TIMESTAMP,FORM_ID INTEGER,CONSTRAINT FKEBB71441EDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID))
ALTER TABLE CAT_DATA ADD CONSTRAINT FK33157E93D92037EA FOREIGN KEY(CAT_ID) REFERENCES IFMCA(ID)
ALTER TABLE CAT_DATA ADD CONSTRAINT FK33157E93E4949E42 FOREIGN KEY(DATA_ID) REFERENCES IFMD(ID)
ALTER TABLE COM_DATA ADD CONSTRAINT FKBBACFC85CCA574E FOREIGN KEY(COM_ID) REFERENCES IFMC(ID)
ALTER TABLE COM_DATA ADD CONSTRAINT FKBBACFC8E4949E42 FOREIGN KEY(DATA_ID) REFERENCES IFMD(ID)
ALTER TABLE EVENT_FORMS ADD CONSTRAINT FKD1052E0AEDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID)
ALTER TABLE EVENT_FORMS ADD CONSTRAINT FKD1052E0AC50DA2B0 FOREIGN KEY(EVENT_ID) REFERENCES EVENT(ID)
ALTER TABLE FORM_EVENTS ADD CONSTRAINT FKF1E3A8F4EDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID)
ALTER TABLE FORM_EVENTS ADD CONSTRAINT FKF1E3A8F4C50DA2B0 FOREIGN KEY(EVENT_ID) REFERENCES EVENT(ID)
ALTER TABLE VES_DATA ADD CONSTRAINT FKBAAFE785E4949E42 FOREIGN KEY(DATA_ID) REFERENCES IFMD(ID)
ALTER TABLE VES_DATA ADD CONSTRAINT FKBAAFE785C9FD80EE FOREIGN KEY(VES_ID) REFERENCES IFMVT(ID)
ALTER TABLE ACTION ADD CONSTRAINT FKAB2F7E368FB4F804 FOREIGN KEY(ACTIONPLAN_ID) REFERENCES ACTIONPLAN(ID)
ALTER TABLE ACTION ADD CONSTRAINT FKAB2F7E36EDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID)
ALTER TABLE ATTACHMENT ADD CONSTRAINT FK8AF75923EDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID)
ALTER TABLE BOOLEANFIELD ADD CONSTRAINT FKC2788DD2B4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID)
ALTER TABLE CALENDARFIELD ADD CONSTRAINT FK7560FC7CB4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID)
ALTER TABLE DATEFIELD ADD CONSTRAINT FKF1884E0CB4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID)
ALTER TABLE DOUBLEFIELD ADD CONSTRAINT FK9FF0DFC9B4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID)
ALTER TABLE FIELD ADD CONSTRAINT FK5CEA0FAEDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID)
SET TABLE COM_DATA INDEX'3576 3576 3192 0'
SET TABLE VES_DATA INDEX'4016296 4016104 4016296 0'
SET TABLE BOOLEANFIELD INDEX'878688 878688 0'
SET TABLE CALENDARFIELD INDEX'2780264 2780264 0'
SET TABLE DOUBLEFIELD INDEX'4759096 4759096 0'
SET TABLE DROPDOWN INDEX'4536 0'
SET TABLE DROPDOWN_DROPDOWNELEMENTS INDEX'414760 5427328 0'
SET TABLE FIELD INDEX'1927288 1928232 21650'
SET TABLE FORM INDEX'3935576 3935576 385'
SET TABLE IFMC INDEX'1800 13'
SET TABLE IFMCA INDEX'3986480 19'
SET TABLE IFMD INDEX'4010952 4010952 10'
SET TABLE IFMVT INDEX'960 21'
SET TABLE INFO INDEX'5148408 5129880 384'
SET TABLE INTEGERFIELD INDEX'1421440 1421440 0'
SET TABLE STRINGFIELD INDEX'1421936 1421936 0'
SET TABLE IFMD_FUNCTIONS INDEX'5424264 5424264 0'
SET TABLE IFMD_GSMSIDS INDEX'5424408 5424408 0'
SET TABLE IFMD_UFNAME INDEX'5424192 5424192 0'
ALTER TABLE ACTION ALTER COLUMN ID RESTART WITH 1
ALTER TABLE ACTIONPLAN ALTER COLUMN ID RESTART WITH 1
ALTER TABLE EVENT ALTER COLUMN ID RESTART WITH 1
ALTER TABLE FIELD ALTER COLUMN ID RESTART WITH 21650
ALTER TABLE FORM ALTER COLUMN ID RESTART WITH 385
ALTER TABLE IFMC ALTER COLUMN ID RESTART WITH 13
ALTER TABLE IFMCA ALTER COLUMN ID RESTART WITH 19
ALTER TABLE IFMD ALTER COLUMN ID RESTART WITH 10
ALTER TABLE IFMVT ALTER COLUMN ID RESTART WITH 21
ALTER TABLE INFO ALTER COLUMN ID RESTART WITH 384
ALTER TABLE INFORMATION ALTER COLUMN ID RESTART WITH 670
ALTER TABLE RESPONSE ALTER COLUMN ID RESTART WITH 1
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10

The properties file is as follows:

#HSQL Database Engine 1.8.0.10
#Mon Oct 05 10:13:06 GMT 2009
hsqldb.script_format=0
runtime.gc_interval=0
sql.enforce_strict_size=false
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=true
hsqldb.cache_scale=14
version=1.8.0
hsqldb.default_table_type=cached
hsqldb.cache_file_scale=1
hsqldb.log_size=200
modified=yes
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0

I've tried to replicate the problem without success. I really do not understand how it is possible that two tables in a one-to-one relationship, for some reason, end up having two different indexes, hence preventing me from joining the two tables. Am I doing something wrong? Is the annotation that specifies the relational mapping wrong? Could a power failure cause similar problems?

Thank you very much for your help.

Regards

Mirko Zanotti

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Re: One to one relation and column index.

by Mike Ressler-2 :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Mirko,

Typically your tables will all start with different indexes. This shouldn't affect the relations between the tables however. It will just affect the primary key of the row that is created next. As long as your relation is set up appropriately, the other table will simply reference the id of the newly created row in the first table.

Unless I'm misunderstanding what you mean by "misalignment"? Is there a chance to create one of your objects without creating the other? Looks like the database you pointed us to has more Forms than Infos. Is there an exception being thrown at some point outside the context of a transaction? Hibernate may have persisted the Form and then borked (technical term) the persisting of the Info object.

Mike

On Mon, Oct 26, 2009 at 8:01 AM, Mirko Zanotti <mirko.zanotti@...> wrote:

Hello,

I am developing a web app with - among other - two tables, Form and Info, in a one-to-one relationship.

@Entity
@Table(name="form")
@Indexed
public class Form implements Serializable, Cloneable {

    [... other fields ...]

    private Info info;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @PrimaryKeyJoinColumn
    @IndexedEmbedded(depth = 2)
    public Info getInfo() {
        return info;
    }

    public void setInfo(Info info) {
        this.info = info;
    }

    [... other getters and setters ...]
}

@Entity
@Table(name="info")
public class Form implements Serializable, Cloneable {

    [... other fields ...]

    private Form form;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "info")
    public Form getForm() {
        return form;
    }

    public void setForm(Form form) {
        this.form = form;
    }

    [... other getters and setters ...]
}

I have around 300 installations of the application working fine, but two of them have reported a problem that, ultimately, is due to a misalignment of these two tables. Here's one of the "problematic" script file, where it is evidentt that the two tables start with different indexes:

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE CACHED TABLE CAT_DATA(CAT_ID INTEGER NOT NULL,DATA_ID INTEGER NOT NULL,PRIMARY KEY(CAT_ID,DATA_ID))
CREATE CACHED TABLE COM_DATA(COM_ID INTEGER NOT NULL,DATA_ID INTEGER NOT NULL,PRIMARY KEY(COM_ID,DATA_ID))
CREATE CACHED TABLE EVENT_FORMS(EVENT_ID INTEGER NOT NULL,FORM_ID INTEGER NOT NULL,EVENT_SHORTCODE VARCHAR(255) NOT NULL,PRIMARY KEY(EVENT_ID,EVENT_SHORTCODE))
CREATE CACHED TABLE FORM_EVENTS(FORM_ID INTEGER NOT NULL,EVENT_ID INTEGER NOT NULL,FORM_SHORTCODE VARCHAR(255) NOT NULL,PRIMARY KEY(FORM_ID,FORM_SHORTCODE))
CREATE CACHED TABLE VES_DATA(VES_ID INTEGER NOT NULL,DATA_ID INTEGER NOT NULL,PRIMARY KEY(VES_ID,DATA_ID))
CREATE CACHED TABLE ACTION(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,ACTIONADDED TIMESTAMP,COMMENT VARCHAR(255),TITLE VARCHAR(255),ACTIONPLAN_ID INTEGER,ACTIONCREATOR VARCHAR(255),ACTIONCREATORUID VARCHAR(255),ACTIONEERNAME VARCHAR(255),ACTIONEERUID VARCHAR(255),ACTUALACTION VARCHAR(255),DATECOMPLETED TIMESTAMP,PROPESEDDUEDATE TIMESTAMP,PROPOSEDACTION VARCHAR(255),FORM_ID INTEGER,ACTION_INDEX INTEGER)
CREATE CACHED TABLE ACTIONPLAN(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,EVENT VARBINARY(255))
CREATE CACHED TABLE ATTACHMENT(ID VARCHAR(255) NOT NULL PRIMARY KEY,COMMENT VARCHAR(255),DELETEME BOOLEAN NOT NULL,FIELDNAME VARCHAR(255),HASTHUMBNAIL BOOLEAN,NAME VARCHAR(255),FORM_ID INTEGER,ATTACHMENT_INDEX INTEGER)
CREATE CACHED TABLE BOOLEANFIELD(ID INTEGER NOT NULL PRIMARY KEY,VALUE BOOLEAN)
CREATE CACHED TABLE CALENDARFIELD(ID INTEGER NOT NULL PRIMARY KEY,TIMEZONEOFFSET INTEGER NOT NULL,VALUE TIMESTAMP)
CREATE CACHED TABLE DATEFIELD(ID INTEGER NOT NULL PRIMARY KEY,VALUE TIMESTAMP)
CREATE CACHED TABLE DOUBLEFIELD(ID INTEGER NOT NULL PRIMARY KEY,VALUE DOUBLE)
CREATE CACHED TABLE DROPDOWN(ID VARCHAR(255) NOT NULL PRIMARY KEY,NAME VARCHAR(255))
CREATE CACHED TABLE DROPDOWN_DROPDOWNELEMENTS(DROPDOWN_ID VARCHAR(255) NOT NULL,ELEMENT VARCHAR(255),CONSTRAINT FK79320676257EDD44 FOREIGN KEY(DROPDOWN_ID) REFERENCES DROPDOWN(ID))
CREATE CACHED TABLE EVENT(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,CURRENTSTATE VARCHAR(255),SHORTCODE VARCHAR(255),ACTIONPLAN_ID INTEGER,CONSTRAINT FK5C6729A8FB4F804 FOREIGN KEY(ACTIONPLAN_ID) REFERENCES ACTIONPLAN(ID))
CREATE CACHED TABLE FIELD(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255),FORM_ID INTEGER)
CREATE CACHED TABLE FORM(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,SHORTCODE VARCHAR(255),TIMEZONEOFFSET BIGINT NOT NULL,EVENT_ID INTEGER,SEQUENCENUMBER BIGINT,CONSTRAINT FK300CC4C50DA2B0 FOREIGN KEY(EVENT_ID) REFERENCES EVENT(ID))
CREATE CACHED TABLE IFMC(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255))
CREATE CACHED TABLE IFMCA(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255),CATEGORYTYPE VARCHAR(255))
CREATE CACHED TABLE IFMD(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,EVENTTYPE VARCHAR(255),FLOWID VARCHAR(255),NAME VARCHAR(255),RETENTIONMONTHS INTEGER NOT NULL,THREEDIGITID VARCHAR(255),REVISIONDATE VARCHAR(255),REVISIONNUMBER INTEGER,IFORMMETACATEGORY_ID INTEGER,FORMCOUNT BIGINT,CONSTRAINT FK31477482CAE50 FOREIGN KEY(IFORMMETACATEGORY_ID) REFERENCES IFMCA(ID))
CREATE CACHED TABLE IFMVT(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VESSELTYPE VARCHAR(255))
CREATE CACHED TABLE INFO(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,CREATED TIMESTAMP,CREATEDBY VARCHAR(255),CURRENTSTATE VARCHAR(255),LASTREVIEWED TIMESTAMP,NAME VARCHAR(255),IFM_ID INTEGER,CLOSEDBY VARCHAR(255),CONSTRAINT FK3164AE478C693C FOREIGN KEY(IFM_ID) REFERENCES IFMD(ID))
CREATE CACHED TABLE INFORMATION(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,CREATED TIMESTAMP,INFFROM VARCHAR(255),INFOTYPE VARCHAR(255),MESSAGE VARCHAR(1000),READ TIMESTAMP,TITLE VARCHAR(255),INFTO VARCHAR(255))
CREATE CACHED TABLE INFORMATION_INFOIDS(INFORMATION_ID INTEGER NOT NULL,ELEMENT INTEGER,CONSTRAINT FK8258FB37C63A00F0 FOREIGN KEY(INFORMATION_ID) REFERENCES INFORMATION(ID))
CREATE CACHED TABLE INTEGERFIELD(ID INTEGER NOT NULL PRIMARY KEY,VALUE INTEGER,CONSTRAINT FKC52E367CB4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID))
CREATE CACHED TABLE STRINGFIELD(ID INTEGER NOT NULL PRIMARY KEY,VALUE VARCHAR(1000),CONSTRAINT FKA657A389B4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID))
CREATE CACHED TABLE IFMD_FUNCTIONS(IFMD_ID INTEGER NOT NULL,ELEMENT INTEGER,CONSTRAINT FKBB058890F53ABA38 FOREIGN KEY(IFMD_ID) REFERENCES IFMD(ID))
CREATE CACHED TABLE IFMD_GSMSIDS(IFMD_ID INTEGER NOT NULL,ELEMENT VARCHAR(255),CONSTRAINT FK8A1401BF53ABA38 FOREIGN KEY(IFMD_ID) REFERENCES IFMD(ID))
CREATE CACHED TABLE IFMD_UFNAME(IFMD_ID INTEGER NOT NULL,UFNAME VARCHAR(255),LOCALE_NAME VARCHAR(255) NOT NULL,PRIMARY KEY(IFMD_ID,LOCALE_NAME),CONSTRAINT FK8B0277E7F53ABA38 FOREIGN KEY(IFMD_ID) REFERENCES IFMD(ID))
CREATE CACHED TABLE RESPONSE(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,COMMENT VARCHAR(255),COMMENTERNAME VARCHAR(255),COMMENTERUID VARCHAR(255),DATEOFCOMMENT TIMESTAMP,FORM_ID INTEGER,CONSTRAINT FKEBB71441EDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID))
ALTER TABLE CAT_DATA ADD CONSTRAINT FK33157E93D92037EA FOREIGN KEY(CAT_ID) REFERENCES IFMCA(ID)
ALTER TABLE CAT_DATA ADD CONSTRAINT FK33157E93E4949E42 FOREIGN KEY(DATA_ID) REFERENCES IFMD(ID)
ALTER TABLE COM_DATA ADD CONSTRAINT FKBBACFC85CCA574E FOREIGN KEY(COM_ID) REFERENCES IFMC(ID)
ALTER TABLE COM_DATA ADD CONSTRAINT FKBBACFC8E4949E42 FOREIGN KEY(DATA_ID) REFERENCES IFMD(ID)
ALTER TABLE EVENT_FORMS ADD CONSTRAINT FKD1052E0AEDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID)
ALTER TABLE EVENT_FORMS ADD CONSTRAINT FKD1052E0AC50DA2B0 FOREIGN KEY(EVENT_ID) REFERENCES EVENT(ID)
ALTER TABLE FORM_EVENTS ADD CONSTRAINT FKF1E3A8F4EDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID)
ALTER TABLE FORM_EVENTS ADD CONSTRAINT FKF1E3A8F4C50DA2B0 FOREIGN KEY(EVENT_ID) REFERENCES EVENT(ID)
ALTER TABLE VES_DATA ADD CONSTRAINT FKBAAFE785E4949E42 FOREIGN KEY(DATA_ID) REFERENCES IFMD(ID)
ALTER TABLE VES_DATA ADD CONSTRAINT FKBAAFE785C9FD80EE FOREIGN KEY(VES_ID) REFERENCES IFMVT(ID)
ALTER TABLE ACTION ADD CONSTRAINT FKAB2F7E368FB4F804 FOREIGN KEY(ACTIONPLAN_ID) REFERENCES ACTIONPLAN(ID)
ALTER TABLE ACTION ADD CONSTRAINT FKAB2F7E36EDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID)
ALTER TABLE ATTACHMENT ADD CONSTRAINT FK8AF75923EDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID)
ALTER TABLE BOOLEANFIELD ADD CONSTRAINT FKC2788DD2B4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID)
ALTER TABLE CALENDARFIELD ADD CONSTRAINT FK7560FC7CB4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID)
ALTER TABLE DATEFIELD ADD CONSTRAINT FKF1884E0CB4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID)
ALTER TABLE DOUBLEFIELD ADD CONSTRAINT FK9FF0DFC9B4821D4B FOREIGN KEY(ID) REFERENCES FIELD(ID)
ALTER TABLE FIELD ADD CONSTRAINT FK5CEA0FAEDF5DD44 FOREIGN KEY(FORM_ID) REFERENCES FORM(ID)
SET TABLE COM_DATA INDEX'3576 3576 3192 0'
SET TABLE VES_DATA INDEX'4016296 4016104 4016296 0'
SET TABLE BOOLEANFIELD INDEX'878688 878688 0'
SET TABLE CALENDARFIELD INDEX'2780264 2780264 0'
SET TABLE DOUBLEFIELD INDEX'4759096 4759096 0'
SET TABLE DROPDOWN INDEX'4536 0'
SET TABLE DROPDOWN_DROPDOWNELEMENTS INDEX'414760 5427328 0'
SET TABLE FIELD INDEX'1927288 1928232 21650'
SET TABLE FORM INDEX'3935576 3935576 385'
SET TABLE IFMC INDEX'1800 13'
SET TABLE IFMCA INDEX'3986480 19'
SET TABLE IFMD INDEX'4010952 4010952 10'
SET TABLE IFMVT INDEX'960 21'
SET TABLE INFO INDEX'5148408 5129880 384'
SET TABLE INTEGERFIELD INDEX'1421440 1421440 0'
SET TABLE STRINGFIELD INDEX'1421936 1421936 0'
SET TABLE IFMD_FUNCTIONS INDEX'5424264 5424264 0'
SET TABLE IFMD_GSMSIDS INDEX'5424408 5424408 0'
SET TABLE IFMD_UFNAME INDEX'5424192 5424192 0'
ALTER TABLE ACTION ALTER COLUMN ID RESTART WITH 1
ALTER TABLE ACTIONPLAN ALTER COLUMN ID RESTART WITH 1
ALTER TABLE EVENT ALTER COLUMN ID RESTART WITH 1
ALTER TABLE FIELD ALTER COLUMN ID RESTART WITH 21650
ALTER TABLE FORM ALTER COLUMN ID RESTART WITH 385
ALTER TABLE IFMC ALTER COLUMN ID RESTART WITH 13
ALTER TABLE IFMCA ALTER COLUMN ID RESTART WITH 19
ALTER TABLE IFMD ALTER COLUMN ID RESTART WITH 10
ALTER TABLE IFMVT ALTER COLUMN ID RESTART WITH 21
ALTER TABLE INFO ALTER COLUMN ID RESTART WITH 384
ALTER TABLE INFORMATION ALTER COLUMN ID RESTART WITH 670
ALTER TABLE RESPONSE ALTER COLUMN ID RESTART WITH 1
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10

The properties file is as follows:

#HSQL Database Engine 1.8.0.10
#Mon Oct 05 10:13:06 GMT 2009
hsqldb.script_format=0
runtime.gc_interval=0
sql.enforce_strict_size=false
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=true
hsqldb.cache_scale=14
version=1.8.0
hsqldb.default_table_type=cached
hsqldb.cache_file_scale=1
hsqldb.log_size=200
modified=yes
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0

I've tried to replicate the problem without success. I really do not understand how it is possible that two tables in a one-to-one relationship, for some reason, end up having two different indexes, hence preventing me from joining the two tables. Am I doing something wrong? Is the annotation that specifies the relational mapping wrong? Could a power failure cause similar problems?

Thank you very much for your help.

Regards

Mirko Zanotti

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user