SQL Error: 23503, SQLState: 23503 in SpringBoot 2.1.4.RELEASE app

Question

I have a SpringBoot 2.1.4.RELEASE RESTful Web Service app., using Spring Initializer, embedded Tomcat, Thymeleaf template engine, and package as an executable JAR file.

I have this class:

@Entity
@Table(name="t_menu_alert_notification")
public class MenuAlertNotification implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;


    public MenuAlertNotification() {
        super();
    }


    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @JsonProperty("id")
    private Long id;    

    @JsonProperty("subject")
    protected String subject;

    @JsonIgnore
    protected Integer trend;

    @JsonIgnore
    protected String message;

    @JsonProperty("notified")
    private Boolean notified;

    @Column(name = "is_read")
    protected Boolean read;

    @JsonProperty("creationDate")
    @Convert(converter = LocalDateTimeAttributeConverter.class)
    protected LocalDateTime creationDate;


    @ManyToOne(fetch = FetchType.EAGER)
        @JoinColumn(name = "menu_alert_id")
    @JsonIdentityInfo(generator=ObjectIdGenerators.PropertyGenerator.class, property="name")
    @JsonIdentityReference(alwaysAsId=true)
    protected MenuAlert menuAlert;
..
}

and this method in the repository:

  @Transactional(propagation =Propagation.REQUIRED,
                    isolation=Isolation.SERIALIZABLE,
                    readOnly=false,
                    transactionManager="transactionManager")
    @Modifying
        @Query("update MenuAlertNotification n set n.read = :status where n.id in :notificationIdList and n.menuAlert.menu.user.id = :userId")
        void changemenuNotificationListReadStatus(  @Param("notificationIdList") List<Long> notificationIdList, 
                                                        @Param("userId") long userId, 
                                                        @Param("status") boolean status);

I have created a Junit test :

MenuAlertNotification menuAlertNotification = new MenuAlertNotification (menuAlert);        
        menuAlertNotificationService.save(menuAlertNotification);               
        assertFalse (menuAlertNotification.getRead());      
        Long menuAlertNotificationId = menuAlertNotification.getId();       
        List<Long> notificationIdList = new ArrayList <Long>();     
        notificationIdList.add  (menuAlertNotificationId);

        menuAlertNotificationService
                .changeMenuNotificationReadStatus (notificationIdList, user.getId(), Boolean.TRUE);

when I save the object, everything is fine, but when I call the method changeMenuNotificationReadStatus I got this error:

019-04-16 11:21  [main] WARN  o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions(137) - SQL Error: 23503, SQLState: 23503
2019-04-16 11:21  [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions(142) - Referential integrity constraint violation: "FK56KKTN0YV9SJJIOJJVJBPAGNW: PUBLIC.T_MENU_ALERT_NOTIFICATION FOREIGN KEY(MENU_ALERT_ID) REFERENCES PUBLIC.T_MENU_ALERT(ID) (1)"; SQL statement:
delete from t_menu_alert where id=? [23503-197]

Answer 1

The ID is not generated when you do MenuAlertNotification menuAlertNotification = new MenuAlertNotification (menuAlert); as well as Long menuAlertNotificationId = menuAlertNotification.getId(); this will always return null.

You should change the second line of your junit test to

menuAlertNotification = menuAlertNotificationService.save(menuAlertNotification);

I assume your service menuAlertNotificationService.save(menuAlertNotification); returns something like return notificationRepo.save(entity).

This way you have the new object populated with ID after the row is inserted and hence will not get the stated exception.