How do I get a foreign key reference in JPA to have "ON UPDATE CASCADE ON DELETE CASCADE" functionality?

Question

I've been fooling with this non-stop for the last 6 hours at least and I just can't understand what the issue is. I have two classes set up in my SpringBoot project (User and Item) and I have a bidirectional relationship between the two classes as follows:

• @OneToMany relationship from User to Item (one User can have many Items)
• @ManyToOne relationship from Item to User (only one User per Item)

This is my User class:

@Data
@Entity
@AllArgsConstructor
@Table(name="users")
public class User implements Serializable {   

    @Id
    private @NotNull String id;
    private @NotNull String username;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "pk.owner", cascade = CascadeType.ALL)
    private List<Item> ownedItems;

    ...
}

This is my Item class (which uses a composite primary key):

@Data
@Entity
@AllArgsConstructor
@Table(name="items")
public class Item implements Serializable {


    @Data
    @Embeddable
    public static class PrimaryKey implements Serializable {

        private @NotNull String id;

        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(                
                name="user_id",
                referencedColumnName = "id",
                foreignKey = @ForeignKey(name = "FK_Item_UserID"),
                nullable = false,     // I have tried using these
                updatable = false,    // and excluding these and
                insertable = false    // that changes nothing
        )
        private @NotNull User owner;
    }


    @EmbeddedId
    private @NotNull PrimaryKey pk;

    private @NotNull String name, item_condition, categories;
    private @NotNull LocalDateTime postDate;
}

The problem is the table that gets created by Hibernate (returned by the SQL query 'SHOW CREATE TABLE items' is this:

CREATE TABLE `items` (
  `id` varchar(255) NOT NULL,
  `categories` varchar(255) NOT NULL,
  `item_condition` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `post_date` datetime NOT NULL,
  `user_id` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`user_id`),
  KEY `FK_Item_UserID` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Notice the foreign key reference is just a KEY and not a FOREIGN KEY. The next issue is that it doesn't do anything on update or delete, which is a huge problem. I want the foreign key for this table to read like:

FOREIGN KEY ('user_id') REFERENCES Users('id') ON UPDATE CASCADE ON DELETE CASCADE

Can anyone explain to me how to achieve this and why what I have isn't working?

Answer 1

RESOLVED

@danblack informed me that I needed to change the engine to "Innodb". I did this by adding spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL55Dialect to my application.properties file.

THANKS @danblack!