Can someone help me with removing unique constraint? I have very simple model containing two classes: Exam and Room. Exam will be held in multiple rooms. More than one assistant will be assigned to each room.

@Entity
public class Exam implements Serializable {

@Id @GeneratedValue(strategy=GenerationType.AUTO)
private Integer id;

/* List of room where exam will take place... */
@OneToMany(cascade=CascadeType.ALL)
@IndexColumn(name="roomno", base=0)
@JoinColumn(name="exam")
private List<Room> rooms = new ArrayList<Room>(50);

/* Place Assistants into Room-s. */
@CollectionOfElements
private Map<String,Room> assistantRoom = new HashMap<String, Room>(50);

/* Other data... */
}


@Entity
public class Room implements Serializable {

@Id
private String name;

/* This room is for which exam? */
@ManyToOne
@JoinColumn(name="exam",insertable=false,updatable=false,nullable=true)
private Exam exam;

/* Other (exam+room) specific data... */
}

So, in Exam I have a Map assistantRoom mapping each assistant (by name; assistants are not Entities in database) to designated room. Important is to note that more than one assistants can be placed in the same room.

For this model SchemaExport will produce:

CREATE TABLE `exam_room` (
`Exam_id` int(11) NOT NULL,
`assistantRoom_name` varchar(255) NOT NULL,
`mapkey` varchar(255) NOT NULL default '',
PRIMARY KEY (`Exam_id`,`mapkey`),
UNIQUE KEY `assistantRoom_name` (`assistantRoom_name`),
KEY `FK729D50FBA6C61A5D` (`assistantRoom_name`),
KEY `FK729D50FB9090388B` (`Exam_id`),
CONSTRAINT `FK729D50FB9090388B` FOREIGN KEY (`Exam_id`) REFERENCES `exam` (`id`),
CONSTRAINT `FK729D50FBA6C61A5D` FOREIGN KEY (`assistantRoom_name`) REFERENCES `room` (`name`)
);

marking assistantRoom_name as unique, and preventing the placement of two or more assistants into same room.

How to avoid this?

Thanks.

I have seen this question posted over and over again and no one ever responds to it. I have the same exact problem.

<many-to-one name="alert" column="ALERT_KEY" class="Alert" not-null="false"/>

That not-null="false" technically should prevent a foreign key constraint from happening, but still it creates one. If anyone here can please tell us how to avoid a FK constraint on a many to one join MANY of us would be very thankful.