验证shopDaoTest的时候出错，请老师帮看一下，谢谢

Question

org.springframework.dao.DataIntegrityViolationException: 

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`o2o`.`tb_shop`, CONSTRAINT `fk_shop_profile` FOREIGN KEY (`owner_id`) REFERENCES `tb_person_info` (`user_id`))

### The error may involve defaultParameterMap

### The error occurred while setting parameters

### SQL: INSERT INTO   tb_shop(owner_id, area_id, shop_category_id,   shop_name, shop_desc, shop_addr,   phone, shop_img, priority,   create_time, last_edit_time, enable_status,   advice)   VALUES   (?,?,?,?,   ?,?,?,?,?,   ?,?, ?,?)

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`o2o`.`tb_shop`, CONSTRAINT `fk_shop_profile` FOREIGN KEY (`owner_id`) REFERENCES `tb_person_info` (`user_id`))

; SQL []; Cannot add or update a child row: a foreign key constraint fails (`o2o`.`tb_shop`, CONSTRAINT `fk_shop_profile` FOREIGN KEY (`owner_id`) REFERENCES `tb_person_info` (`user_id`)); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`o2o`.`tb_shop`, CONSTRAINT `fk_shop_profile` FOREIGN KEY (`owner_id`) REFERENCES `tb_person_info` (`user_id`))

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:243)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)

at com.sun.proxy.$Proxy17.insert(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:57)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)

at com.sun.proxy.$Proxy21.insertShop(Unknown Source)

at com.imooc.o2o.dao.ShopDaoTest.testInsertShop(ShopDaoTest.java:39)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)

at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)

at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)

at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)

at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)

at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)

at org.junit.runners.ParentRunner.run(ParentRunner.java:363)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)

at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:539)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:761)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:461)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:207)

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`o2o`.`tb_shop`, CONSTRAINT `fk_shop_profile` FOREIGN KEY (`owner_id`) REFERENCES `tb_person_info` (`user_id`))

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

at java.lang.reflect.Constructor.newInstance(Unknown Source)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)

at com.mysql.jdbc.Util.getInstance(Util.java:387)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)

at com.sun.proxy.$Proxy23.execute(Unknown Source)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)

at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)

... 35 more

Answer 1

qq_薛定谔的猫_0 2018-05-24 15:55:43

tb_shop有3个外键,我们在代码中进行插入时

owner.setUserId(1L);
area.setAreaId(2);
shopCategory.setShopCategoryId(1L);

请确认一下,外键所在的三个表中的现有的id和代码中的是否一致.老师在插入记录时没有手动写id,数据库自增产生的id可能并不是1.

• 翔仔 #1

  感谢同学的解释，我当时写入数据的时候是利用自动生成的id，但是后面会删除一些数据，所以看起来id是跳的，但是对这些功能并没有影响，只要遵循主外键约束即可

Answer 2

爆炸小弟 2018-04-07 10:51:05

请问这个是怎么解决的

Answer 3

翔仔 2017-12-07 00:37:07

同学好，光从这看不出错误呢，需要同学看看junit那个stacktrace报什么错，并且设个断点调试下

• 提问者 笑问红尘 #1

  老师，我更新了Failure trace，请帮我看一下，谢谢

• 翔仔 回复 提问者 笑问红尘 #2

  同学好，一般有错误信息了就好解决了，别担心错误信息长，只看自己认识的和业务相关的就好了，这里教同学一下哈
  ### SQL: INSERT INTO   tb_shop(owner_id, area_id, shop_category_id,   shop_name, shop_desc, shop_addr,   phone, shop_img, priority,   create_time, last_edit_time, enable_status,   advice)   VALUES   (?,?,?,?,   ?,?,?,?,?,   ?,?, ?,?)
  ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`o2o`.`tb_shop`, CONSTRAINT `fk_shop_profile` FOREIGN KEY (`owner_id`) REFERENCES `tb_person_info` (`user_id`))
  ; SQL []; Cannot add or update a child row: a foreign key constraint fails (`o2o`.`tb_shop`, CONSTRAINT `fk_shop_profile` FOREIGN KEY (`owner_id`) REFERENCES `tb_person_info` (`user_id`)); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`o2o`.`tb_shop`, CONSTRAINT `fk_shop_profile` FOREIGN KEY (`owner_id`) REFERENCES `tb_person_info` (`user_id`))
  主要是这个错误，一般在caused by附近，这里说的往tb_shop这张表插入数据的时候，owner_id的值在外键关联的表tb_person_info里面没有，违反了外键约束，所以在tb_person_info一定要有这个user_id才可以

• 提问者 笑问红尘 #3

  谢谢老师，已经解决了～

点开查看后面1条评论