Spring Data JPA + Postgres - Unable to insert data with One to One mapping

Question

I have a UserInfo and AddressInfo entity classes and they have one to one association. UserInfo uses sequence to add primary key but I am getting below error;

org.hibernate.id.IdentifierGenerationException: attempted to assign id from null one-to-one property

UserInfo Entity

@Entity
@Table(name = "userinfo")
public class UserInfo {
    
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "userid_seq")
    @SequenceGenerator(name="userid_seq", allocationSize=1)
    private Long userId;
    
    private String firstName;
    
    private String lastName;
    
    private String email;   
    
    private String username;
   
    private String password;
    
    @OneToOne(mappedBy = "userInfo", cascade = CascadeType.ALL)
    @PrimaryKeyJoinColumn
    private AddressInfo addressInfo;
    
    //Getters - Setters
    
}

AddressInfo Entity

@Entity
@Table(name = "addressinfo")
public class AddressInfo {  
        
    @Id
    private Long addressId;
    
    private String homeAddress;
    
    private String homeCity;
    
    private String homeState;
    
    private String homeZip; 
    
    @OneToOne
    @MapsId
    @JoinColumn(name = "address_id")
    private UserInfo userInfo;
    
    //Getters - Setters
    
}

UserInfo Table

CREATE TABLE userinfo (
  user_id INTEGER NOT NULL DEFAULT nextval('userid_seq') PRIMARY KEY ,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,      
  email VARCHAR(100) UNIQUE NOT NULL,
  username VARCHAR(20) NOT NULL,    
  password VARCHAR(20) NOT NULL
);

AddressInfo Table

CREATE TABLE addressinfo (
    address_id INTEGER NOT NULL PRIMARY KEY,
    home_address VARCHAR(100) NULL,     
    home_city VARCHAR(100) NULL,    
    home_state VARCHAR(100) NULL,   
    home_zip VARCHAR(100) NULL,
    CONSTRAINT fk_addinfo
      FOREIGN KEY(address_id) 
      REFERENCES userinfo(user_id)
);

I thought my sequence had some issues but I could see that if I remove the child entity then its is inserting into the UserInfo table successfully but getting above error when I add OneToOne mapping. Seems like I have issues with my Primary key. I see similar questions but I could not find what is going on here. A help would be really appreciated.

Belayer · Accepted Answer · 2021-08-25 23:59:27Z

Two solutions come to mind:

A single transaction that inserts both Userinfo and Addressinfo. With the second insert using the sequence currval to define the id.

    create table addressinfo (
        address_id integer  default currval('userid_seq') primary key,
        home_address varchar(100) null,     
        home_city varchar(100) null,    
        home_state varchar(100) null,   
        home_zip varchar(100) null,
        constraint fk_addinfo
          foreign key(address_id) 
          references userinfo(user_id)
    );
    
    
    do $$
    begin
    insert into userinfo (first_name, last_name, email, username, password)
      values ('Jane','Smith','[email protected]','js','psojHvIEJNB'); 
      
    insert into addressinfo( home_address, home_city, home_state,home_zip) 
      values ('1 Joe''s Lane','Smithtown', 'NV', '0123456789asdfgh');
    end;
     $$;

A single statement handling the insert for both tables:

     with newuser (user_id) as 
          (insert into userinfo (first_name, last_name, email, username, password)
                values ('Joe','Smith','[email protected]','js2','+949+fsrgwDGKJS58') 
             returning user_id 
          ) --select id from newuser;
     insert  into addressinfo(address_id, home_address, home_city, home_state,home_zip) 
          select user_id,'1 Joe''s Lane','Smithtown', 'NV', '0123456789asdfgh'  
            from newuser;

However neither is a good plan. A 1:1 relationships are always questionable. In this case they are not. What happens when user Jane Smith stores her address, then insists that her husband, Joe, have the same address. You wind up with DUPLICATE addresses. See example here. You might be better off giving addressinfo its own PK sequence and putting address_id as a column and FK into userinfo

Actually you are absolutely right, I could not see that point. I am going with your suggestion. Will add address_id as column in Userinfo. Thank you very much!

Collectives™ on Stack Overflow

Spring Data JPA + Postgres - Unable to insert data with One to One mapping

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related