几天查询数据库发现【用户表pub_user】里面的垃圾数据太多,于是准备删掉。但是删除时发现删不了,因为有一张表的外键与【用户表pub_user】相关联。于是手动在navicat删除了该表的外键。于是表【用户表pub_user】中的垃圾数据可以正常删除,再次手动还原外键时发现建不了。报错如下
问题解决:是因为删除【用户表pub_user】表中的数据后,外键表中的部分数据有的外键不在【用户表pub_user】中了。删掉了这写数据后即可新建外键了。
建立表外键关联
用户角色映射表。外键为角色id 与角色表中的字段相关联。
package com.comtop.map.store.uom.entity;
import com.comtop.map.store.entity.BaseEntity;
import com.fasterxml.jackson.annotation.JsonIgnore;
import org.hibernate.annotations.GenericGenerator;
import javax.persistence.*;
@Entity
@Table(name = "pub_user_role")
public class UserRoleEntity extends BaseEntity{
/** 主键id */
@Id
@GeneratedValue(generator = "CustomIDGen")
@GenericGenerator(name = "CustomIDGen", strategy = "com.comtop.map.store.core.id.CustomIDGen")
private Long id;
/** 用户编号 */
@Column
private Long userId;
/** 角色编号 */
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "role_id")
@JsonIgnore
private RoleEntity role;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public RoleEntity getRole() {
return role;
}
public void setRole(RoleEntity role) {
this.role = role;
}
}
角色表
package com.comtop.map.store.uom.entity;
import com.comtop.map.store.entity.BaseEntity;
import org.hibernate.annotations.GenericGenerator;
import javax.persistence.*;
import java.util.List;
@Entity
@Table(name = "pub_role")
public class RoleEntity extends BaseEntity{
/** 角色编号 */
@Id
@GeneratedValue(generator = "CustomIDGen")
@GenericGenerator(name = "CustomIDGen", strategy = "com.comtop.map.store.core.id.CustomIDGen")
private Long roleId;
/** 角色名 */
@Column(length = 50)
private String roleName;
/** 备注 */
@Column(length = 500)
private String remarks;
@Transient
private String [] perms;
@Transient
private List<UserRoleEntity> users;
public Long getRoleId() {
return roleId;
}
public void setRoleId(Long roleId) {
this.roleId = roleId;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRemarks() {
return remarks;
}
public void setRemarks(String remarks) {
this.remarks = remarks;
}
public String[] getPerms() {
return perms;
}
public void setPerms(String[] perms) {
this.perms = perms;
}
public List<UserRoleEntity> getUsers() {
return users;
}
public void setUsers(List<UserRoleEntity> users) {
this.users = users;
}
}