0

I try to write mapping to tables. I decided that id and versionId - composite-id for table test. But in table test_question I have composite-id consists from question_id, test_id and versionId. I can't understand as I can use one composite-id id-versionId as part other composite-id.

I have next peace of script. 

  Create Table Test
(

  id int unsigned not null,
  versionId int unsigned not null,
  test_text varchar(200) not null,
  subject_id int unsigned not null,
  primary key (id, versionId),
  Foreign key (subject_id) REferences Subject(id)
  On delete cascade on update cascade
)
Engine InnoDB CHARACTER SET utf8;

Create Table Question
(
  id varchar(36) not null,
  question_text varchar(200) not null,
  primary key(id)
)
Engine InnoDB CHARACTER SET utf8;


Create table Test_Question
(
  test_id int unsigned not null,
  question_id varchar(36) not null,
  versionId int unsigned not null,
  primary key(test_id, question_id, versionId),
  Foreign key(test_id,versionId) References Test(id, versionId),
  Foreign key(question_id) References Question(id)
)
Engine InnoDB CHARACTER SET utf8;

And my mapping

Test.hbm.xml

<hibernate-mapping>
    <class name="by.bsuir.testapp.model.Test" table="TEST">
        <composite-id>
            <key-property name="testId" type="long" column="TEST_ID" />
            <key-property name="versionId" type="long" column="VERSION_ID" />
            <generator class="assigned"/>
        </composite-id>

        <property name="testName" type="string">
            <column name="TESTNAME" />
        </property>
        <many-to-one name="subject" class="by.bsuir.testapp.model.Subject"
            fetch="join">
            <column name="SUBJECT_ID" />
        </many-to-one>
    </class>
</hibernate-mapping>

and for TestQuestion.hbm.xml

<hibernate-mapping>
    <class name="by.bsuir.testapp.model.TestQuestion" table="TEST_QUESTION">
        <composite-id>
            <key-property name="test" type="long" column="TEST_ID" />
            <key-property name="question" type="long" column="QUESTION_ID" />
            <generator class="assigned" />
        </composite-id>
    </class>
</hibernate-mapping>

What should be id in table TestQuestion? I mean that QUESTION_ID-TEST_ID-VERSION_ID is whole composite id.

Ray
  • 1,788
  • 7
  • 55
  • 92
  • which key columns are being generated in the TEST table - both 'id' and 'versionId'? why are you generating a composite id. it's typical to have a composite key when this is a many-to-many table (the composite key is a set of FK columns). but if you're generating the PK, it's already unique. – dlgrasse Nov 08 '12 at 17:56
  • @dlgrasse yes, in table Test key is id and versionId.I'm new in Hibernate. And I can't understand as make composite key or you mean to id? – Ray Nov 08 '12 at 18:33
  • In Hibernate, the composite-id keys certainly need to match the primary-key columns in the table. for TextQuestion you need to have a tag for each of test_id, question_id, and version_id. but from a data-modeling perspective, i would still ask why you have multiple PK columns being generated. a single generated PK column is sufficient to guarantee uniqueness. a composite id is necessary for uniqueness in cases where the columns are FKs to other tables. – dlgrasse Nov 08 '12 at 19:05
  • @dlgrasse I use composite multiply pk that's why I can have the same testid but with different versions. And in table TestQuestion I want to save all history – Ray Nov 08 '12 at 19:14
  • ok - i missed the Question table, so TestQuestion is a valid composite. sorry. but in this case, since they're FKs, i don't think the generator tag is needed - the TestQuestion table id columns aren't being generated. btw - i see mismatches between the table column names and the names given in your Hibernate files. – dlgrasse Nov 08 '12 at 19:29
  • @dlgrasse of course, that's why I ask advice(shema db independent I use hibernate.hbm2ddl.auto but I want get I table according script of shema). I can't understand in table TestQuestion absent one uniq key. it means that I need composite-id from to FK, but I don't know as I can do this. – Ray Nov 08 '12 at 19:55

0 Answers0