Hi,
I got a problem on insert when a column of the primary key is constraint by a foreign key.
To sum up, I have a table Questionnaire, I have a column year in its primary key.
Then I have a table Questionnairetemplate with a primary key (year, questionnairetemplateid)
A questionnaire is linked to a Questionnairetemplate with a many-to-one relationship.
So in my questionnaire table, the column year is used both in the primary key and in the foreign key and I think ( but I may be wrong ) this is the origin of the following problem.
When I try to insert a Questionnaire, the generated SQL query is :
Code:
insert into accountingaudit.questionnaire (ConcludedTimestamp, ConcludedUserId, LastEditedTimestamp, LastEditedUserId, ConcludingRemarks, Year, CompanyId, FinancialStatementId, ScreeningTypeId) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
you can see that the column 'questionnairetemplateid' is not present in the query but it is required so I got the message :
Code:
java.sql.BatchUpdateException: Cannot add or update a child row: a foreign key constraint fails (`accountingaudit/questionnaire`, CONSTRAINT `Ref_36` FOREIGN KEY (`Year`, `QuestionnaireId`) REFERENCES `questionnairetemplate` (`Year`, `QuestionnaireId`) ON DELETE NO ACTION ON UPDATE NO ACT)
The questionnairetemplate instance is correctly initialize into the questionnaire instance before the call to persist so I don't know why the column is not present.
I hope I have explained correctly my problem.
Any feedback would be really welcome because this issue prevents me to add Questionnaire into my database :(
Below some additionnal details.
Thanks in advance,
Regards,
Christophe
[org.hibernate.ejb.Version] Hibernate EntityManager 3.2.0.CR1
[org.hibernate.cfg.annotations.Version] Hibernate Annotations 3.2.0.CR1
[org.hibernate.cfg.Environment] Hibernate 3.2 cr2
[org.hibernate.cfg.Environment] hibernate.properties not found
[org.hibernate.cfg.Environment] Bytecode provider name : javassist
Code:
@Entity
@Table(name = "questionnairetemplate", catalog = "accountingaudit", uniqueConstraints = {})
public class Questionnairetemplate implements java.io.Serializable {
@EmbeddedId
@AttributeOverrides( {
@AttributeOverride(name = "year", column = @Column(name = "Year", unique = false, nullable = false, insertable = true, updatable = true)),
@AttributeOverride(name = "questionnaireId", column = @Column(name = "QuestionnaireId", unique = false, nullable = false, insertable = true, updatable = true)) })
public QuestionnairetemplateId getId() {
return this.id;
}
@OneToMany(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY, mappedBy = "questionnairetemplate")
public Set<Questionnaire> getQuestionnaires() {
return this.questionnaires;
}
Code:
@Entity
@Table(name = "questionnaire", catalog = "accountingaudit", uniqueConstraints = {})
public class Questionnaire implements java.io.Serializable {
@EmbeddedId
@AttributeOverrides( {
@AttributeOverride(name = "year", column = @Column(name = "Year", unique = false, nullable = false, insertable = true, updatable = true)),
@AttributeOverride(name = "companyId", column = @Column(name = "CompanyId", unique = false, nullable = false, insertable = true, updatable = true)),
@AttributeOverride(name = "financialStatementId", column = @Column(name = "FinancialStatementId", unique = false, nullable = false, insertable = true, updatable = true)),
@AttributeOverride(name = "screeningTypeId", column = @Column(name = "ScreeningTypeId", unique = false, nullable = false, insertable = true, updatable = true)) })
public QuestionnaireId getId() {
return this.id;
}
@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumns( {
@JoinColumn(name = "Year", referencedColumnName = "Year", unique = false, nullable = false, insertable = false, updatable = false),
@JoinColumn(name = "QuestionnaireId", referencedColumnName = "QuestionnaireId", unique = false, nullable = false, insertable = false, updatable = false) })
public Questionnairetemplate getQuestionnairetemplate() {
return this.questionnairetemplate;
}
Code:
@Embeddable
public class QuestionnaireId implements java.io.Serializable {
@Column(name = "Year", unique = false, nullable = false, insertable = true, updatable = true)
public int getYear() {
return this.year;
}
@Column(name = "CompanyId", unique = false, nullable = false, insertable = true, updatable = true)
public long getCompanyId() {
return this.companyId;
}
@Column(name = "FinancialStatementId", unique = false, nullable = false, insertable = true, updatable = true)
public short getFinancialStatementId() {
return this.financialStatementId;
}
@Column(name = "ScreeningTypeId", unique = false, nullable = false, insertable = true, updatable = true)
public byte getScreeningTypeId() {
return this.screeningTypeId;
}
Code:
CREATE TABLE `questionnaire` (
`Year` int(4) UNSIGNED NOT NULL DEFAULT '0',
`CompanyId` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
`FinancialStatementId` smallint(6) UNSIGNED NOT NULL DEFAULT '0',
`ScreeningTypeId` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
`QuestionnaireId` int(11) UNSIGNED NOT NULL DEFAULT '0',
`LastEditedTimestamp` datetime,
`LastEditedUserId` int(10) UNSIGNED DEFAULT '0',
`ConcludingRemarks` mediumtext,
`ConcludedTimestamp` datetime,
`ConcludedUserId` int(10) UNSIGNED DEFAULT '0',
PRIMARY KEY(`Year`, `CompanyId`, `FinancialStatementId`, `ScreeningTypeId`)
)
ALTER TABLE `questionnaire` ADD
CONSTRAINT `Ref_35` FOREIGN KEY (`Year`, `CompanyId`, `FinancialStatementId`)
REFERENCES `financialstatement`(`Year`, `CompanyId`, `FinancialStatementId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
ALTER TABLE `questionnaire` ADD
CONSTRAINT `Ref_36` FOREIGN KEY (`Year`, `QuestionnaireId`)
REFERENCES `questionnairetemplate`(`Year`, `QuestionnaireId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
ALTER TABLE `questionnaire` ADD
CONSTRAINT `Ref_37` FOREIGN KEY (`ScreeningTypeId`)
REFERENCES `screeningtype`(`ScreeningTypeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
Login
FAQ
Search
