0

I need to use uniqueidentifier as Primary key for each table instead of ID (integer). The logic described below works for ID(Integer) whereas it fails for rowguid(uniqueidentifier).

I have the following tables in (MS SQL Server) DB. 

CREATE TABLE Person(
        [ROWGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT             [DF_PERSON_ROWGUID]  DEFAULT (newsequentialid()),
        [NICKNAME] [varchar](15) NULL,
        [EMAIL] [varchar](50) NULL,
        [DATE_OF_BIRTH] [date] NULL)


 CREATE TABLE School(
            [ROWGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT             [DF_PERSON_ROWGUID]  DEFAULT (newsequentialid()),
            [PERSONID] [varchar](15) NULL,
            [EMAIL] [varchar](50) NULL,
            [DATE_OF_BIRTH] [date] NULL)

It is a one-to-many relationship (Person may have multiple schools) . PERSONID of School table is ROWGUID of Person table.

On the JPA side, the problem is that, I am not able to insert a Person object with school . However, I'm able to

  • insert a Person object alone in to table,
  • make a select statement for a person and retrieve its schools. So I believe it proves that the FK relation works fine.

So back to the problem where I cannot make an insert; I have the following exception when trying to persist ;

Cannot insert the value NULL into column 'PERSONID', table 'SCHOOL'; column does not allow nulls. INSERT fails.

So basically, it cannot create a Person object, generate its ROWGUID, and then wire it to the column PERSONID of the table School. This is what I presume. And I believe it is because of the way I generate ID on JPA side. Here how it is; 

public class Person implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ROWGUID", columnDefinition = "uniqueidentifier")
    private String rowguid;
    ...


public class School implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ROWGUID", columnDefinition="uniqueidentifier")
    private String rowguid;

//bi-directional many-to-one association to Person
    @ManyToOne
    @JoinColumn(name="PROFILEID")
    private Person person;

    public School() {
    }
    ...

Any idea how to overcome it ?

IMPORTANT : The same logic works fine when the Primary key for each entity is ID(int) as following ; 

public class Person implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="ID")
    private int id;


 public class School implements Serializable {
        private static final long serialVersionUID = 1L;

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

    //bi-directional many-to-one association to Person
        @ManyToOne
        @JoinColumn(name="PROFILEID")
        private Person person;

        public School() {
        }

Basically there is something wrong when I convert the following generation of ID ; 

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

INTO 

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ROWGUID", columnDefinition="uniqueidentifier")
    private String rowguid;
shamaleyte
  • 1,882
  • 3
  • 21
  • 38
  • If your Person always needs school, you can use @Inheritance(strategy = InheritanceType.JOINED). Basically School extends Person (but it's probably a bad design) – Jama Djafarov Apr 14 '15 at 14:45
  • Please note that not all JPA implementations are the same. I assume you use OpenJPA2. I think you just need to remove "@GeneratedValue(strategy = GenerationType.IDENTITY)" from School. And define the relationship in Person as @ManyToMany(mappedBy="person") – Jama Djafarov Apr 14 '15 at 14:53
  • Jama, yes it is openJpa. but why to change the relationship ? you think one-to-many causing the problem ? – shamaleyte Apr 14 '15 at 17:22
  • yes. I think in Hibernate you can specify Foreign-Key annotation (or something like that). But in OpenJPA you have to use @ManyToMany(mappedBy="person") in Parent object, and describe the mapping in the Child object. 99% sure it's the only way (except a different strategy). I was trying to solve the same issue for a while. – Jama Djafarov Apr 14 '15 at 20:43
  • Company-Branch example: http://stackoverflow.com/questions/11938253/jpa-joincolumn-vs-mappedby – Jama Djafarov Apr 14 '15 at 20:51
  • Thanks for the answer but, please check my further explanation in the edit part. – shamaleyte Apr 15 '15 at 08:15

1 Answers1

0

This is a standard implementation:

Parent-Child relationship (the PK in Parent, FK in Child table):

@Entity(name ="Parent")
public class Parent {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private String parentID;

    @ManyToMany(mappedBy = "parent") 
    private List<Child> children;
}

@Entity(name ="Child")
public class Child{
    @Id
    private String parentID;

    @ManyToMany
    @JoinColumn(name="parentID")
    private Parent parent;
}

I think you might have an issue with your design. "Cannot insert the value NULL into column 'PERSONID', table 'SCHOOL';" sounds very strange to put PersonID inside School table.

Jama Djafarov
  • 358
  • 3
  • 11
  • Jama, I may have forgotten to put emphasize on "ROWGUID" - uniqueidentifier. When I replicate the exact senario with "ID" integer instead of rowguid (uniqueidentifier) , I have no problem at all. One-to-Many relationship works perfectly ! . The problem happens when I convert ID (Integer) into rowguid (uniqueidentifier) . I will edit my question with further details. – shamaleyte Apr 15 '15 at 08:07
  • I guess there is a misunderstanding. I'm not trying to put a PERSON into SCHOOL . I'm trying to create a person object with school and put it into person table. – shamaleyte Apr 16 '15 at 10:44