JPA joined column allow every value...

Question

I'm testing JPA, in a simple case File/FileVersions tables (Master/Details), with OneToMany relation, I have this problem: in FileVersions table, the field "file_id" (responsable for the relation with File table) accepts every values, not only values from File table.

How can I use the JPA mapping to limit the input in FileVersion.file_id only for values existing in File.id?

My class are File and FileVersion:

FILE CLASS

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="FILE_ID")
private Long id;

@Column(name="NAME", nullable = false, length = 30)
private String name;

//RELATIONS -------------------------------------------

@OneToMany(mappedBy="file", fetch=FetchType.EAGER)
private Collection <FileVersion> fileVersionsList;

//-----------------------------------------------------

FILEVERSION CLASS

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="VERSION_ID")
private Long id;

@Column(name="FILENAME", nullable = false, length = 255)
private String fileName;

@Column(name="NOTES", nullable = false, length = 200)
private String notes;

//RELATIONS -------------------------------------------

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name="FILE_ID", referencedColumnName="FILE_ID", nullable=false)
private File file;

//-----------------------------------------------------

and this is the FILEVERSION TABLE

CREATE TABLE  `JPA-Support`.`FILEVERSION` (
`VERSION_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`FILENAME` varchar(255) NOT NULL,
`NOTES` varchar(200) NOT NULL,
`FILE_ID` bigint(20) NOT NULL,
PRIMARY KEY (`VERSION_ID`),
KEY `FK_FILEVERSION_FILE_ID` (`FILE_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Answer 1

At the Java level, you describe and annotate associations between classes - which and you did - and your mapping looks fine.

At the database level, if you want to restrict the possible values in the file_id column to values that are primary keys in the FILE table, you should use a foreign key constraint. To do so, you will need to use InnoDB tables. Something like that:

CREATE TABLE  `JPA-Support`.`FILEVERSION` (
`VERSION_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`FILENAME` varchar(255) NOT NULL,
`NOTES` varchar(200) NOT NULL,
`FILE_ID` bigint(20) NOT NULL,
PRIMARY KEY (`VERSION_ID`),
FOREIGN KEY `FK_FILEVERSION_FILE_ID` (`FILE_ID`) REFERENCES FILE(ID)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

The table FILE also has to use InnoDB. Actually, use InnoDB tables for the tables for which you want to use referential integrity.

Answer 2

Thanks for help,

I know the SQL constraint to limit the input, but it is possible to create this SQL costraint using some annotation, without writing by hand the SQL in the database?

I'm new on JPA, I was thinking that using @JoinColumn annotation, JPA could create also the costraint...

Thank you again.