Dear All,

I have tables designed as below, but i always get Caused by: java.sql.SQLException: Integrity constraint violation: SYS_FK_16 table: BOSS in statement [insert into COMPANY (WORKER_ID, WORKER_DEPAT_ID, BOSS_ID, BOSS_DEPAT_ID, COMPANY_ID) values (1, 2, 1, 11, 1)]
at org.hsqldb.Trace.getError(Unknown Source)
for insert record to table Company

Could any one give me some hints ?

CREATE TABLE WORKER (
depat_id int NOT NULL ,
worker_id int NOT NULL,
salary decimal(20,4) NOT NULL,
primary key (depat_id,worker_id)
)

CREATE TABLE BOSS (
depat_id int NOT NULL ,
boss_id int NOT NULL,
salary decimal(20,4) NOT NULL,
primary key (depat_id,boss_id)
)

CREATE TABLE COMPANY(
company_id int NOT NULL PRIMARY KEY,
boss_id int NOT NULL,
boss_depat_id int NOT NULL ,
worker_id int NOT NULL,
worker_depat_id int NOT NULL ,
foreign key (boss_id, boss_depat_id) references BOSS (boss_id,depat_id),
foreign key (worker_id, worker_depat_id) references WORKER (worker_id,depat_id)
)

insert Boss first, then Insert Worker, then Insert Company
Session s= SessionManager.currentSession();
Boss b1=new Boss();
b1.setComp_id(new BossPK(new Integer(1),new Integer(11)));
b1.setSalary(new BigDecimal(7000));

Worker w1=new Worker();
w1.setComp_id(new WorkerPK(new Integer(1),new Integer(2)));
w1.setSalary(new BigDecimal(3000));

Company c=new Company();
c.setBoss(b1);
c.setWorker(w1);
c.setCompanyId(new Integer(1));
HashSet hs=new HashSet();
hs.add(c);
w1.setCompanies(hs);
b1.setCompanies(hs);
s.save(w1);
s.save(b1);
s.save(c);

s.flush();

blow is mapping files
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>


<class
name="org.hibernate.test.Boss"
table="BOSS"
>
<meta attribute="class-description" inherit="false">
@hibernate.class
table="BOSS"
</meta>

<composite-id name="comp_id" class="org.hibernate.test.BossPK">
<meta attribute="field-description" inherit="false">
@hibernate.id
generator-class="assigned"
</meta>
<key-property
name="depatId"
column="DEPAT_ID"
type="java.lang.Integer"
>
<meta attribute="field-description">
@hibernate.property
column="DEPAT_ID"
</meta>
</key-property>
<key-property
name="bossId"
column="BOSS_ID"
type="java.lang.Integer"
>
<meta attribute="field-description">
@hibernate.property
column="BOSS_ID"
</meta>
</key-property>
</composite-id>

<property
name="salary"
type="java.math.BigDecimal"
column="SALARY"
not-null="true"
length="20"
>
<meta attribute="field-description">
@hibernate.property
column="SALARY"
length="20"
not-null="true"
</meta>
</property>

<!-- Associations -->
<!-- derived association(s) for compound key -->
<!-- end of derived association(s) -->

<!-- bi-directional one-to-many association to Company -->
<set
name="companies"
lazy="true"
inverse="true"
cascade="none"
>
<meta attribute="field-description">
@hibernate.set
lazy="true"
inverse="true"
cascade="none"

@hibernate.collection-key
column="BOSS_ID"
@hibernate.collection-key
column="BOSS_DEPAT_ID"

@hibernate.collection-one-to-many
class="org.hibernate.test.Company"
</meta>
<key>
<column name="BOSS_ID" />
<column name="BOSS_DEPAT_ID" />
</key>
<one-to-many
class="org.hibernate.test.Company"
/>
</set>

</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<!--
Created by the Middlegen Hibernate plugin 2.1

http://boss.bekk.no/boss/middlegen/
http://www.hibernate.org/
-->

<class
name="org.hibernate.test.Worker"
table="WORKER"
>
<meta attribute="class-description" inherit="false">
@hibernate.class
table="WORKER"
</meta>

<composite-id name="comp_id" class="org.hibernate.test.WorkerPK">
<meta attribute="field-description" inherit="false">
@hibernate.id
generator-class="assigned"
</meta>
<key-property
name="depatId"
column="DEPAT_ID"
type="java.lang.Integer"
>
<meta attribute="field-description">
@hibernate.property
column="DEPAT_ID"
</meta>
</key-property>
<key-property
name="workerId"
column="WORKER_ID"
type="java.lang.Integer"
>
<meta attribute="field-description">
@hibernate.property
column="WORKER_ID"
</meta>
</key-property>
</composite-id>

<property
name="salary"
type="java.math.BigDecimal"
column="SALARY"
not-null="true"
length="20"
>
<meta attribute="field-description">
@hibernate.property
column="SALARY"
length="20"
not-null="true"
</meta>
</property>

<!-- Associations -->
<!-- derived association(s) for compound key -->
<!-- end of derived association(s) -->

<!-- bi-directional one-to-many association to Company -->
<set
name="companies"
lazy="true"
inverse="true"
cascade="none"
>
<meta attribute="field-description">
@hibernate.set
lazy="true"
inverse="true"
cascade="none"

@hibernate.collection-key
column="WORKER_ID"
@hibernate.collection-key
column="WORKER_DEPAT_ID"

@hibernate.collection-one-to-many
class="org.hibernate.test.Company"
</meta>
<key>
<column name="WORKER_ID" />
<column name="WORKER_DEPAT_ID" />
</key>
<one-to-many
class="org.hibernate.test.Company"
/>
</set>

</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>

<class
name="org.hibernate.test.Company"
table="COMPANY"
>
<meta attribute="class-description" inherit="false">
@hibernate.class
table="COMPANY"
</meta>

<id
name="companyId"
type="java.lang.Integer"
column="COMPANY_ID"
>
<meta attribute="field-description">
@hibernate.id
generator-class="assigned"
type="java.lang.Integer"
column="COMPANY_ID"

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


<!-- Associations -->

<!-- bi-directional many-to-one association to Worker -->
<many-to-one
name="worker"
class="org.hibernate.test.Worker"
not-null="true"
>
<meta attribute="field-description">
@hibernate.many-to-one
not-null="true"
@hibernate.column name="WORKER_ID"
@hibernate.column name="WORKER_DEPAT_ID"
</meta>
<column name="WORKER_ID" />
<column name="WORKER_DEPAT_ID" />
</many-to-one>
<!-- bi-directional many-to-one association to Boss -->
<many-to-one
name="boss"
class="org.hibernate.test.Boss"
not-null="true"
>
<meta attribute="field-description">
@hibernate.many-to-one
not-null="true"
@hibernate.column name="BOSS_ID"
@hibernate.column name="BOSS_DEPAT_ID"
</meta>
<column name="BOSS_ID" />
<column name="BOSS_DEPAT_ID" />
</many-to-one>

</class>
</hibernate-mapping>

It could be transaction related issue. Though you have saved dependent objects first, they have not been committed to theDB since all of these might happen inside a single transaction. You may want to set the Auto-commit property if you have not already done so.