英文:
PSQLException "insert or update on table violates foreign key constraint", "Key is not present in a table"
问题
我正在学习Spring Security。我使用Hibernate和PostgreSQL。我有一个用户登录项目，但在运行时出现错误。当插入user_authority值时出现错误。错误如下：
org.postgresql.util.PSQLException: 错误: 在表"user_authority"上插入或更新违反了外键约束"fkhi46vu7680y1hwvmnnuh4cybx"
详细信息: 在表"users"中不存在键(user_id)=(1)。
我从一个基础项目中构建了这个项目，该基础项目使用MySQL数据库，所以问题可能是数据库应用程序。这个基础项目在这里：DZone
以下是一个User实体代码、一个错误消息和hibernate.cfg.xml。如何修复它？
package com.howtodoinjava.demo.spring.domain;
import org.hibernate.validator.constraints.NotEmpty;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;
@Entity
@Table(name = "users")
public class User implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@NotEmpty
@Column(nullable = false, unique = true)
private String username;
@NotEmpty
private String password;
private Date dateCreated;
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(name = "user_authority",
joinColumns = @JoinColumn(name = "user_id"),
inverseJoinColumns = @JoinColumn(name = "authority_id")
)
private Set<Authority> authorities = new HashSet<>();
public User() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getDateCreated() {
return dateCreated;
}
public void setDateCreated(Date dateCreated) {
this.dateCreated = dateCreated;
}
public Set<Authority> getAuthorities() {
return authorities;
}
public void setAuthorities(Set<Authority> authorities) {
this.authorities = authorities;
}
}
hibernate.cfg.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.archive.autodetection">class,hbm</property>
<property name="hibernate.show_sql">true</property>
<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/postgres</property>
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="connection.username">postgres</property>
<property name="connection.password">postgres</property>
<property name="dialect">org.hibernate.dialect.PostgreSQL95Dialect</property>
<property name="hibernate.hbm2ddl.auto">create</property>
<property name="connection.pool.size">1</property>
<property name="hibernate.enable_lazy_load_no_trans">true</property>
<property name="hibernate.jdbc.lob.non_contextual_creation">true</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.acquire_increment">2</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_statements">150</property>
<mapping class="com.howtodoinjava.demo.spring.domain.User"/>
<mapping class="com.howtodoinjava.demo.spring.domain.Authority"/>
<mapping class="com.howtodoinjava.demo.spring.domain.AuthorityType"/>
</session-factory>
</hibernate-configuration>
请注意，上述内容是已翻译的版本，仅包括您提供的代码和部分描述，以及相关的配置文件和版本信息。如有需要，您可以继续提问。
英文:
I am learning Spring Security. I use Hibernate and PostgreSQL. I have a user login project but the error is present when running it. The user_authority values cannot be inserted. The error is this
org.postgresql.util.PSQLException: ERROR: insert or update on table "user_authority" violates foreign key constraint "fkhi46vu7680y1hwvmnnuh4cybx"
Detail: Key (user_id)=(1) is not present in table "users".
A base project which I have taken to build the project on, has a MySQL database, so the issue may be the database application. This base project is there: DZone
Here you are a User entity code, an error message and hibernate.cfg.xml. How to fix it?
package com.howtodoinjava.demo.spring.domain;
import org.hibernate.validator.constraints.NotEmpty;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;
@Entity
@Table(name = "users")
public class User implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@NotEmpty
@Column(nullable = false, unique = true)
private String username;
@NotEmpty
private String password;
private Date dateCreated;
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(name = "user_authority",
joinColumns = @JoinColumn(name = "user_id"),
inverseJoinColumns = @JoinColumn(name = "authority_id")
)
private Set<Authority> authorities = new HashSet<>();
public User() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getDateCreated() {
return dateCreated;
}
public void setDateCreated(Date dateCreated) {
this.dateCreated = dateCreated;
}
public Set<Authority> getAuthorities() {
return authorities;
}
public void setAuthorities(Set<Authority> authorities) {
this.authorities = authorities;
}
}
(...)
Hibernate: alter table if exists user_authority add constraint FKhi46vu7680y1hwvmnnuh4cybx foreign key (user_id) references users
2020-09-18 18:50:57 INFO SchemaCreatorImpl:489 - HHH000476: Executing import script 'ScriptSourceInputFromUrl(file:/home/u/tomcat/webapps/ROOT/WEB-INF/classes/import.sql)'
2020-09-18 18:50:57 DEBUG SQL:92 - INSERT INTO authority (name, id) VALUES ('ROLE_ADMIN', 1)
Hibernate: INSERT INTO authority (name, id) VALUES ('ROLE_ADMIN', 1)
2020-09-18 18:50:57 DEBUG SQL:92 - INSERT INTO authority (name, id) VALUES ('ROLE_USER', 2)
Hibernate: INSERT INTO authority (name, id) VALUES ('ROLE_USER', 2)
2020-09-18 18:50:57 DEBUG SQL:92 - INSERT INTO user_authority (authority_id, user_id) VALUES (1, 1)
Hibernate: INSERT INTO user_authority (authority_id, user_id) VALUES (1, 1)
2020-09-18 18:50:57 DEBUG NewPooledConnection:207 - com.mchange.v2.c3p0.impl.NewPooledConnection@62880d handling a throwable.
org.postgresql.util.PSQLException: ERROR: insert or update on table "user_authority" violates foreign key constraint "fkhi46vu7680y1hwvmnnuh4cybx"
Detail: Key (user_id)=(1) is not present in table "users".
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:440)
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applyImportSources(SchemaCreatorImpl.java:491)
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:180)
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135)
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:155)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:313)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:452)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:710)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:726)
at org.springframework.orm.hibernate5.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:616)
at org.springframework.orm.hibernate5.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:600)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1862)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1799)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1287)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1207)
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:874)
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:778)
at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:226)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1358)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1204)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:557)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:879)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:878)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550)
at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:401)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:292)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:103)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4676)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5139)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:717)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:690)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:705)
at org.apache.catalina.startup.HostConfig.manageApp(HostConfig.java:1727)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.util.modeler.BaseModelMBean.invoke(BaseModelMBean.java:288)
at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:819)
at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:801)
at org.apache.catalina.mbeans.MBeanFactory.createStandardContext(MBeanFactory.java:456)
at org.apache.catalina.mbeans.MBeanFactory.createStandardContext(MBeanFactory.java:405)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.util.modeler.BaseModelMBean.invoke(BaseModelMBean.java:288)
at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:819)
at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:801)
at com.sun.jmx.remote.security.MBeanServerAccessController.invoke(MBeanServerAccessController.java:468)
at javax.management.remote.rmi.RMIConnectionImpl.doOperation(RMIConnectionImpl.java:1468)
at javax.management.remote.rmi.RMIConnectionImpl.access$300(RMIConnectionImpl.java:76)
at javax.management.remote.rmi.RMIConnectionImpl$PrivilegedOperation.run(RMIConnectionImpl.java:1309)
at java.security.AccessController.doPrivileged(Native Method)
at javax.management.remote.rmi.RMIConnectionImpl.doPrivilegedOperation(RMIConnectionImpl.java:1408)
at javax.management.remote.rmi.RMIConnectionImpl.invoke(RMIConnectionImpl.java:829)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:357)
at sun.rmi.transport.Transport$1.run(Transport.java:200)
at sun.rmi.transport.Transport$1.run(Transport.java:197)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:573)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:834)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:688)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:687)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
2020-09-18 18:50:57 DEBUG SqlUtils:207 - Attempted to convert SQLException to SQLException. Leaving it alone. [SQLState: 23503; errorCode: 0]
org.postgresql.util.PSQLException: ERROR: insert or update on table "user_authority" violates foreign key constraint "fkhi46vu7680y1hwvmnnuh4cybx"
Detail: Key (user_id)=(1) is not present in table "users".
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
(...)
hibernate.cfg.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.archive.autodetection">class,hbm</property>
<property name="hibernate.show_sql">true</property>
<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/postgres</property> <!-- BD Mane -->
<property name="connection.driver_class">org.postgresql.Driver</property> <!-- DB Driver -->
<property name="connection.username">postgres</property> <!-- DB User -->
<property name="connection.password">postgres</property> <!-- DB Password -->
<property name="dialect">org.hibernate.dialect.PostgreSQL95Dialect</property>
<property name="hibernate.hbm2ddl.auto">create</property>
<property name="connection.pool.size">1</property>
<property name="hibernate.enable_lazy_load_no_trans">true</property>
<property name="hibernate.jdbc.lob.non_contextual_creation">true</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.acquire_increment">2</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_statements">150</property>
<mapping class="com.howtodoinjava.demo.spring.domain.User"/>
<mapping class="com.howtodoinjava.demo.spring.domain.Authority"/>
<mapping class="com.howtodoinjava.demo.spring.domain.AuthorityType"/>
</session-factory>
</hibernate-configuration>
EDIT
Here you are libraries versions:
<spring.version>5.2.0.RELEASE</spring.version>
<hibernate.version>5.4.2.Final</hibernate.version>
<hibernate.validator>5.4.1.Final</hibernate.validator>
<c3p0.version>0.9.5.2</c3p0.version>
<jstl.version>1.2.1</jstl.version>
<tld.version>1.1.2</tld.version>
<servlets.version>3.1.0</servlets.version>
<jsp.version>2.3.1</jsp.version>
<hsqldb.version>1.8.0.10</hsqldb.version>
<spring-security.version>5.2.0.RELEASE</spring-security.version>
<postgresql.version>42.2.5</postgresql.version>
<log4j-api.version>2.13.3</log4j-api.version>
<hibernate-validator.version>6.0.13.Final</hibernate-validator.version>
<tiles-extras.version>3.0.8</tiles-extras.version>
<javax.servlet-api.version>4.0.1</javax.servlet-api.version>
<jstl.version>1.2</jstl.version>
The inserting is processed by using import.sql file, that is placed in resources directory:
INSERT INTO authority (name, id) VALUES ('ROLE_ADMIN', 1);
INSERT INTO authority (name, id) VALUES ('ROLE_USER', 2);
INSERT INTO user_authority (authority_id, user_id) VALUES (1, 1);
INSERT INTO user_authority (authority_id, user_id) VALUES (2, 2);
INSERT INTO users (id, username, password, dateCreated) VALUES (1,'ironman','$2a$10$jXlure/BaO7K9WSQ8AMiOu3Ih3Am3kmmnVkWWHZEcQryZ8QPO3FgC','2015-11-15 22:14:54');
INSERT INTO users (id, username, password, dateCreated) VALUES (2,'rabi','$2a$10$0tFJKcOV/Io6I3vWs9/Tju8OySoyMTpGAyO0zaAOCswMbpfma0BSK','2015-10-15 22:14:54');
答案1
得分: 1
我不得不重新排序import.sql文件中的插入操作：
INSERT INTO authority (name, id) VALUES ('ROLE_ADMIN', 1);
INSERT INTO authority (name, id) VALUES ('ROLE_USER', 2);
INSERT INTO users (id, username, password, dateCreated) VALUES (1,'ironman','$2a$10$jXlure/BaO7K9WSQ8AMiOu3Ih3Am3kmmnVkWWHZEcQryZ8QPO3FgC','2015-11-15 22:14:54');
INSERT INTO users (id, username, password, dateCreated) VALUES (2,'rabi','$2a$10$0tFJKcOV/Io6I3vWs9/Tju8OySoyMTpGAyO0zaAOCswMbpfma0BSK','2015-10-15 22:14:54');
INSERT INTO user_authority (authority_id, user_id) VALUES (1, 1);
INSERT INTO user_authority (authority_id, user_id) VALUES (2, 2);
英文:
I had to reorder insertings in import.sql file:
INSERT INTO authority (name, id) VALUES ('ROLE_ADMIN', 1);
INSERT INTO authority (name, id) VALUES ('ROLE_USER', 2);
INSERT INTO users (id, username, password, dateCreated) VALUES (1,'ironman','$2a$10$jXlure/BaO7K9WSQ8AMiOu3Ih3Am3kmmnVkWWHZEcQryZ8QPO3FgC','2015-11-15 22:14:54');
INSERT INTO users (id, username, password, dateCreated) VALUES (2,'rabi','$2a$10$0tFJKcOV/Io6I3vWs9/Tju8OySoyMTpGAyO0zaAOCswMbpfma0BSK','2015-10-15 22:14:54');
INSERT INTO user_authority (authority_id, user_id) VALUES (1, 1);
INSERT INTO user_authority (authority_id, user_id) VALUES (2, 2);
答案2
得分: 0
你可能在 user_authority 表上有指向 users 和 authority 表的 外键，因此您需要首先填充那些表。
英文:
You probably have foreign keys on user_authority table that point to users and authority tables, so you need to populate that tables first.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论