[FIXED] Spring Boot + PostgreSQL： Cause: org.postgresql.util.PSQLException: ERROR: null value in column "front_menu_id" violates not-null constraint

Issue

I don’t know why no matter whether there is a primary key in the JSON data passed in from the front end, the primary key is said to be empty when adding data, I have set the primary key to auto-growth in Entity

Before the primary key of the database was manually entered, the order was chaotic, and the numbers were random. I don’t know if it will affect the addition.

my entity:

@Data
@EqualsAndHashCode(callSuper = true)
@TableName(value = "front_menu_table")
public class FrontMenuTable extends BaseEntity {

private static final long serialVersionUID = 1L;

@TableId(value = "front_menu_id", type = IdType.AUTO)
private Long frontMenuId;

@NotNull(message = "上级菜单不能为空")
private Long parentId;

@NotBlank(message = "菜单名称不能为空")
private String menuName;

private String path;

@NotBlank(message = "菜单授权码不能为空")
private String perms;

private String component;

@NotNull(message = "菜单类型不能为空")
private Integer menuType;

private String icon;

private Integer ordernum;

private Integer statu;

@TableField(exist = false)
private List<FrontMenuTable> children = new ArrayList<>();

my controller:

@PostMapping("/save")
@PreAuthorize("hasAuthority('sys:menu:save')")
public ApiRestResponse save(@Validated @RequestBody FrontMenuTable frontMenuTable, 
Principal principal) {
    frontMenuTable.setCreater(principal.getName());
    frontMenuTable.setCreateTime(LocalDateTime.now());
    frontMenuTableService.save(frontMenuTable);
    return ApiRestResponse.success(frontMenuTable);
}

JSON

{ "component": "show", "icon": "bars", "menuName": "TEST", "menuType": 1, "ordernum": 11, "parentId": 0, "path": "mnm", "perms": "sys:yyy", "statu": 0 }

error message

2021-11-07 16:22:21.889  WARN 43898 --- [nio-8080-exec-6] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: null value in column "front_menu_id" violates not-null constraint
  详细：Failing row contains (null, 0, TEST, mnm, sys:yyy, show, 1, bars, 11, 0, jiangwen, 2021-11-07 16:22:21.87667, null, null).
### The error may exist in org/jiangwen/mapper/FrontMenuTableMapper.java (best guess)
### The error may involve org.jiangwen.mapper.FrontMenuTableMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO front_menu_table  ( parent_id, menu_name, path, perms, component, menu_type, icon, ordernum, statu, creater, create_time )  VALUES  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
### Cause: org.postgresql.util.PSQLException: ERROR: null value in column "front_menu_id" violates not-null constraint
  详细：Failing row contains (null, 0, TEST, mnm, sys:yyy, show, 1, bars, 11, 0, jiangwen, 2021-11-07 16:22:21.87667, null, null).
; ERROR: null value in column "front_menu_id" violates not-null constraint
  详细：Failing row contains (null, 0, TEST, mnm, sys:yyy, show, 1, bars, 11, 0, jiangwen, 2021-11-07 16:22:21.87667, null, null).; nested exception is org.postgresql.util.PSQLException: ERROR: null value in column "front_menu_id" violates not-null constraint
  详细：Failing row contains (null, 0, TEST, mnm, sys:yyy, show, 1, bars, 11, 0, jiangwen, 2021-11-07 16:22:21.87667, null, null).]

Solution

It's hard to tell what exactly @TableId is doing since it is some custom annotation. You can use

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

and then, depending on your DataBase create a sequence or set this field to auto-increment. For example in MySQL it will be:

alter table front_menu_table modify id long auto_increment;

Answered By - Michal Ruszkowski