JPA Toplink cannot add foreign keys in jointable

[Kshitiz]

Hi,

I am developing an application where there is many to many relationship between users and their roles. User entity class is given below:

@Entity
@Table(name="user")

public class UserEntity {
* *
* @Id*
* @SequenceGenerator(name="seq_userId",sequenceName="seq_userId",initialValue=1,allocationSize=1)*
* @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="seq_userId")*
* private int userId;*
* private String emailId;*
* @ManyToMany(fetch=FetchType.EAGER, cascade = CascadeType.ALL)*
@JoinTable(name="userrole",
joinColumns=
@JoinColumn(name="userId", referencedColumnName="userId", columnDefinition="INTEGER"),
inverseJoinColumns=
@JoinColumn(name="roleId", referencedColumnName="roleId", columnDefinition="INTEGER")
)
private List<RoleEntity> roles;
* *
}

But whenever I run the code, the following error appears:

[TopLink Fine]: 2012.01.24 09:32:31.999--ServerSession(30604342)--Connection(8970080)--Thread(Thread[main,5,main])--CREATE TABLE userrole (userId INTEGER, roleId INTEGER, PRIMARY KEY (userId, roleId))

[TopLink Fine]: 2012.01.24 09:32:32.190--ServerSession(30604342)--Connection(2855942)--Thread(Thread[main,5,main])--ALTER TABLE userrole ADD CONSTRAINT FK_userrole_userId FOREIGN KEY (userId) REFERENCES user (userId)

[TopLink Warning]: 2012.01.24 09:32:32.331--ServerSession(30604342)--Thread(Thread[main,5,main])--Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0 (Build b41-beta2 (03/30/2007))): oracle.toplink.essentials.exceptions.DatabaseExcep tion

Internal Exception: java.sql.SQLException: Can't create table '.\enrapplication\#sql-720_22.frm' (errno: 150)
Error Code: 1005

Call: ALTER TABLE userrole ADD CONSTRAINT FK_userrole_userId FOREIGN KEY (userId) REFERENCES user (userId)
Query: DataModifyQuery()

It is able to create userrole table but is not able to add foreign keys in it. Now I have seen many posts rekated to this problem and have come up with primarykeyjoincolumn annotation. Is it not used for one to one mapping? And if it can be used here, can you plzz tell me how?

[Kshitiz]

I solved my problem just by capitalizing the column names (userId - USERID) and (roleId - ROLEID). Also, there was sone mismatching problem between column names defined in join table and in actual tables whose columns are being referred in the join table .