Field 'record_id' doesn't have a default value - Spring JPA Error

Question

I'm working on adding a feature to an already developed spring boot web application. The primary entity that has child entities is a Record. It has a few columns/variables that I want to now be in its own, separate entity (CustomerOrder) and exist in a one-to-one relationship with the Record. To summarize:

Record {

-thing 1

-thing 2

-thing 3

}

is now becoming:

CustomerOrder {

-thing 1

-thing 2

-thing 3

}

Record { CustomerOrder }

I'm having some issues with what I've produced. Here is the CustomerOrder model's relevant relationship data:

@Entity
@Table(name="customer_orders")
public class CustomerOrder {

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

    ... other columns

    @OneToOne(orphanRemoval = true, cascade = CascadeType.ALL, mappedBy="customerOrder", fetch = FetchType.EAGER)
    private Record record;


}

And then here is the Record model's relevant data:

@Entity
@Table(name="records")
public class Record extends Auditable<String> implements Serializable {

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

    ... other columns

    @OneToOne
    @JoinColumn(name="customer_order_id", nullable = false, unique = true)
    private CustomerOrder customerOrder;
}

My issue exists when I try to POST a record, when a user tries creating one in the ui. Here is the POST method for a record:

    @PostMapping
    public ResponseEntity<?> saveRecord(@RequestBody Record recordBody, BindingResult result) {
        if(!result.hasErrors()) {
            if(recordBody.getHardwareItems().isEmpty()) {
                record = recordsService.save(recordBody);
            } else {
                // Save the record first, recordId is required on hardwareItems
                // TODO: investigate Spring Hibernate/JPA rules - is there a way to save parent before children to avoid a null recordId
                CustomerOrder customerOrder = recordBody.getCustomerOrder();
                recordBody.setCustomerOrder(new CustomerOrder());
                customerOrder.setRecord(record);
                customerOrder = customerOrdersService.save(customerOrder);
                record = recordsService.save(recordBody);
            }
        } else {
            return new ResponseEntity<>(result.getAllErrors(), HttpStatus.BAD_REQUEST);
        }
        // Return the location of the created resource
        uri = ServletUriComponentsBuilder.fromCurrentRequest().path("/{recordId}").buildAndExpand(record.getId()).toUri();
        return new ResponseEntity<>(uri, HttpStatus.CREATED);
    }

The error I receive is the following:

2021-02-19 02:35:50.989  WARN 31765 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1364, SQLState: HY000
2021-02-19 02:35:50.989 ERROR 31765 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : Field 'record_id' doesn't have a default value

This makes sense to me at least, since I'm trying to save the CustomerOrder object that depends on a Record object, which has yet to have been persisted. So, how do I go about changing up the order and/or creating and persisting a Record object so that I can then save the CustomerOrder object to it?

Also, I am using mysql and here is the migration script that I already have. Must I add something here for the customer_orders table?

-- Add a sample user
INSERT IGNORE INTO users (first_name, last_name, email, password, enabled, role)
VALUES ('Sample', 'User', 'sample@email.com', 'sample password', true, 'ROLE_ADMIN');

-- Customer Reference Values
INSERT IGNORE INTO customers (name) VALUES ('value1');
INSERT IGNORE INTO customers (name) VALUES ('value2');
INSERT IGNORE INTO customers (name) VALUES ('value3');
INSERT IGNORE INTO customers (name) VALUES ('value4');
INSERT IGNORE INTO customers (name) VALUES ('value5');
INSERT IGNORE INTO customers (name) VALUES ('value6');
INSERT IGNORE INTO customers (name) VALUES ('value7');
INSERT IGNORE INTO customers (name) VALUES ('value8');

Here is the mysql script for the Records table and CustomerOrders table:

-- -----------------------------------------------------
-- Table `myapp`.`records`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `myapp`.`records` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `customer_order_id` BIGINT NOT NULL,
  `record_id` BIGINT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `UK_7m7wsqy68b7omkufvckoqv2hf` (`customer_order_id` ASC) VISIBLE,
  INDEX `FKta31a9q1llknlo2n0jw741987` (`customer_id` ASC) VISIBLE,
  CONSTRAINT `FK3q3clytyrx7s8edp9ok821j3`
    FOREIGN KEY (`customer_order_id`)
    REFERENCES `myapp`.`customer_orders` (`id`),
  CONSTRAINT `FKta31a9q1llknlo2n0jw741987`
    FOREIGN KEY (`customer_id`)
    REFERENCES `myapp`.`customers` (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 27
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `myapp`.`customer_orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `myapp`.`customer_orders` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `record_id` BIGINT NOT NULL,
  `record_id_test` BIGINT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `UK_ilew9pg8y4qnyhmjg38k1fev2` (`record_id_test` ASC) VISIBLE,
  INDEX `FK5rpb3u59bblj7h70wjr5mvb01` (`record_id` ASC) VISIBLE,
  CONSTRAINT `FK5rpb3u59bblj7h70wjr5mvb01`
    FOREIGN KEY (`record_id`)
    REFERENCES `myapp`.`records` (`id`),
  CONSTRAINT `FKk7a0g7djyhymr54ehoftkhyfw`
    FOREIGN KEY (`record_id_test`)
    REFERENCES `myapp`.`records` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

Answer 1

Adding @OneToOne(cascade = CascadeType.PERSIST) for an annotation above the customerOrder variable is all I essentially had to do to fix this issue. Another developer in this group helped me out. Then, I could get rid of all references to CustomerOrder values in the POST method and just had to have a setter for the CustomerOrder in the PUT method for the Record.

Answer 2

Your schema does not match your entities. Your customer_orders table should not have a record_id column. You specify that your records table should have a column customer_order_id. You define this explicitly with the annotation:

@JoinColumn(name="customer_order_id", nullable = false, unique = true)

However, your annotation on the other side

@OneToOne(orphanRemoval = true, cascade = CascadeType.ALL, mappedBy="customerOrder", fetch = FetchType.EAGER)

indicates that this is simply a backref that uses the foreign key in the other table. Drop the column from your schema. I assume that the other columns that are in your DB schema but not your entity are not relevant to your use case since you have comments that say that your entity has additional fields. However, the foreign key does not make sense and it does not match your entity. Remove the column from your DB schema.

Answer 3

In the table creation script, you also seem to be including a column called RECORD_ID in the RECORDS table. And it already has the standard ID column to store the primary key. Not sure if you really need that RECORD_ID column in there. That is marked as NOT NULL and no default value set in the table creation script, hence the error you are getting.

Looking at your constraints in the table creation script, looks like that should be named CUSTOMER_ID and not RECORD_ID.

Please check this as a first step.

Answer 4

Remove the two record_id columns or provide a default value for them. They are not mapped to anything in your entities, therefore Hibernate is not including them in insert statements, which causes the failure since they are both configured as NOT NULL.

I guess they were somehow intended to hold the reference between Record and CustomerOrder, but that is mapped to customer_order_id by

@JoinColumn(name="customer_order_id", nullable = false, unique = true)

Answer 5

If my understanding is correct, you are having a two way mapping in one to one relationship. So, customer_order has a record_id column and record has a customer_order_id column. This is not ideal and hibernate doesn't support this type of mapping automatically. If you think about it, if both columns are not nullable, then you can't save the data b/c you can't save both simultaneously. You have to save one first.

If you want to keep this, you have to make one of them non-nullable. Then save one, then the other, then re-save the first one with the id of the other that you now have generated.

Here hibernate is trying to save the customer_order first, b/c you have stated customer_order_id is in the records table and is not nullable. This is the default. I would recommend keeping this, and remove the other record_id column in customer_orders table.

UPDATE: For clarification, here is one solution. Since record_id and record_id_test are not nullable, set their value in the code, then save. It should work.

If I missed something, please provide more information, like the underlying DB, the location of the record_id column in your model, etc.