Composite primary key with a Foreign key not working in MySQL

[Ravi Nom]

My Phone table has a composite primary of phone number and id. id is also a foreign key to the Student table.

I am seeing the below error when I run it.

23:30:28,228 ERROR SqlExceptionHelper:147 - Column 'id' cannot be null org.hibernate.exception.ConstraintViolationException: could not execute statement



Schema:

Table: student
Columns:
id (PK)
fName
lName
mName


Table: Phone
Columns:
phoneNumber (PK)
color
id(PK)(FK references to Student id)



Student.java

import java.io.Serializable;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;

@Entity
@SuppressWarnings("serial")
public class Student implements Serializable {

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

private String fName;

private String lName;

private String mname;

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "id")
private Set<Phone> phones;

/**
* @return the fName
*/
public String getfName() {
return fName;
}

/**
* @return the id
*/
public int getId() {
return id;
}

/**
* @return the lName
*/
public String getlName() {
return lName;
}

/**
* @return the mname
*/
public String getMname() {
return mname;
}

/**
* @return the phones
*/
public Set<Phone> getPhones() {
return phones;
}

/**
* @param fName
* the fName to set
*/
public void setfName(final String fName) {
this.fName = fName;
}

/**
* @param id
* the id to set
*/
public void setId(final int id) {
this.id = id;
}

/**
* @param lName
* the lName to set
*/
public void setlName(final String lName) {
this.lName = lName;
}

/**
* @param mname
* the mname to set
*/
public void setMname(final String mname) {
this.mname = mname;
}

/**
* @param phones
* the phones to set
*/
public void setPhones(final Set<Phone> phones) {
this.phones = phones;
}

}




Phone.java

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;

@IdClass(PhonePK.class)
@Entity
@SuppressWarnings("serial")
public class Phone implements Serializable {

@Id
private String phoneNumber;

// @Id
// @ManyToOne
// @JoinColumn(name = "id", insertable = false, updatable = false)
// private String id;

@Id
@ManyToOne
@JoinColumn(name = "id", insertable = false, updatable = false)
private Student student;

// public String getId() {
// return id;
// }
//
// public void setId(String id) {
// this.id = id;
// }

private String color;

/**
* @return the color
*/
public String getColor() {
return color;
}

/**
* @return the phoneNumber
*/
public String getPhoneNumber() {
return phoneNumber;
}

/**
* @return the student
*/
public Student getStudent() {
return student;
}

/**
* @param color
* the color to set
*/
public void setColor(final String color) {
this.color = color;
}

/**
* @param phoneNumber
* the phoneNumber to set
*/
public void setPhoneNumber(final String phoneNumber) {
this.phoneNumber = phoneNumber;
}

/**
* @param student
* the student to set
*/
public void setStudent(final Student student) {
this.student = student;
}

}




PhonePK.java

import java.io.Serializable;

@SuppressWarnings("serial")
public class PhonePK implements Serializable {

private String phoneNumber;
//private String id;

private Student student;

// public String getId() {
// return id;
// }
//
// public void setId(String id) {
// this.id = id;
// }

/**
* @return the phoneNumber
*/
public String getPhoneNumber() {
return phoneNumber;
}

/**
* @return the student
*/
public Student getStudent() {
return student;
}

/**
* @param phoneNumber
* the phoneNumber to set
*/
public void setPhoneNumber(final String phoneNumber) {
this.phoneNumber = phoneNumber;
}

/**
* @param student
* the student to set
*/
public void setStudent(final Student student) {
this.student = student;
}

@Override
public boolean equals(final Object obj) {
if (this == obj) {
return true;
}
if (obj == null) {
return false;
}
if (getClass() != obj.getClass()) {
return false;
}
PhonePK other = (PhonePK) obj;
if (phoneNumber == null) {
if (other.phoneNumber != null) {
return false;
}
} else if (!phoneNumber.equals(other.phoneNumber)) {
return false;
}
if (student == null) {
if (other.student != null) {
return false;
}
} else if (!student.equals(other.student)) {
return false;
}
// if (id == null) {
// if (other.id != null) {
// return false;
// }
// } else if (!id.equals(other.id)) {
// return false;
// }

return true;
}

@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((phoneNumber == null) ? 0 : phoneNumber.hashCode());
result = prime * result + ((student == null) ? 0 : student.hashCode());
// result = prime * result + ((id == null) ? 0 : id.hashCode());

return result;
}

}




hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">;
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.password">pwd</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property>
<property name="hibernate.connection.username">user</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="connection.pool_size">1</property>
<property name="hbm2ddl.auto">create</property>
</session-factory>
</hibernate-configuration>



Main.java

import java.util.LinkedHashSet;
import java.util.Set;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class Main {

public static void main(final String args[]) {

Configuration configuration = new Configuration();
Transaction transaction = null;

configuration.addAnnotatedClass(Student.class);
configuration.addAnnotatedClass(Phone.class);
configuration.addAnnotatedClass(PhonePK.class);
configuration.configure("hibernate.cfg.xml");

SessionFactory sessionFactory = configuration.buildSessionFactory();
System.out.println("Session Factory!!!!" + sessionFactory);

Session session = sessionFactory.openSession();


Student student = new Student();
student.setfName("Bob");
student.setlName("Buster");
Set<Phone> phones = new LinkedHashSet<Phone>();
Phone ph1 = new Phone();
ph1.setColor("Black");
ph1.setPhoneNumber("1111111111");

Phone ph2 = new Phone();
ph2.setColor("Blue");
ph2.setPhoneNumber("2222222222");
phones.add(ph1);
phones.add(ph2);

student.setPhones(phones);

try {
transaction = session.beginTransaction();
session.save(student);
transaction.commit();
} catch (HibernateException e) {
transaction.rollback();
e.printStackTrace();
} finally {
session.close();
}

}
}





Console output:

23:30:24,291 INFO SchemaExport:343 - HHH000227: Running hbm2ddl schema export
23:30:24,296 DEBUG SQL:104 - alter table Phone drop foreign key
FK_aoj0eivd0ap3drxnoyk4xj10q
23:30:25,613 DEBUG SQL:104 - drop table if exists Phone
23:30:25,967 DEBUG SQL:104 - drop table if exists Student
23:30:26,230 DEBUG SQL:104 - create table Phone (phoneNumber varchar(255) not null,
color varchar(255), id integer not null, primary key (phoneNumber, id))
23:30:26,731 DEBUG SQL:104 - create table Student (id integer not null auto_increment,
fName varchar(255), lName varchar(255), mname varchar(255), primary key (id))
23:30:26,792 DEBUG SQL:104 - alter table Phone add index FK_aoj0eivd0ap3drxnoyk4xj10q
(id), add constraint FK_aoj0eivd0ap3drxnoyk4xj10q foreign key (id) references Student
(id)
23:30:27,352 INFO SchemaExport:405 - HHH000230: Schema export complete
Session Factory!!!!org.hibernate.internal.SessionFactoryImpl@548997d1
23:30:27,823 DEBUG SQL:104 - insert into Student (fName, lName, mname) values (?, ?, ?)
23:30:27,886 TRACE BasicBinder:84 - binding parameter [1] as [VARCHAR] - Bob
23:30:27,887 TRACE BasicBinder:84 - binding parameter [2] as [VARCHAR] - Buster
23:30:27,888 TRACE BasicBinder:72 - binding parameter [3] as [VARCHAR] - <null>
23:30:28,005 DEBUG SQL:104 - select phone_.phoneNumber, phone_.id, phone_.color as
color2_0_ from Phone phone_ where phone_.phoneNumber=? and phone_.id=?
23:30:28,009 TRACE BasicBinder:84 - binding parameter [1] as [VARCHAR] - 1111111111
23:30:28,010 TRACE BasicBinder:72 - binding parameter [2] as [INTEGER] - <null>
23:30:28,102 DEBUG SQL:104 - select phone_.phoneNumber, phone_.id, phone_.color as
color2_0_ from Phone phone_ where phone_.phoneNumber=? and phone_.id=?
23:30:28,103 TRACE BasicBinder:84 - binding parameter [1] as [VARCHAR] - 2222222222
23:30:28,104 TRACE BasicBinder:72 - binding parameter [2] as [INTEGER] - <null>
23:30:28,222 DEBUG SQL:104 - insert into Phone (color, phoneNumber, id) values (?, ?,

?)
23:30:28,223 TRACE BasicBinder:84 - binding parameter [1] as [VARCHAR] - Black
23:30:28,224 TRACE BasicBinder:84 - binding parameter [2] as [VARCHAR] - 1111111111
23:30:28,224 TRACE BasicBinder:72 - binding parameter [3] as [INTEGER] - <null>
23:30:28,227 WARN SqlExceptionHelper:145 - SQL Error: 1048, SQLState: 23000



Edited 2 time(s). Last edit at 06/30/2013 11:08PM by Ravi Nom.