Delete and insert instead update

Question

I have a user who have have many address and many phone. I use mysql and spring. When i update a user, i delete its adress and phone, and insert data.

update User delete address delete phone

insert address insert phone

create table user(
  user_id INT NOT NULL AUTO_INCREMENT,
  usertitle varchar(100),
  hasphones boolean,
  PRIMARY KEY (user_id)
);

create table address_user(
  address_user_id INT NOT NULL AUTO_INCREMENT,
  addresstitle varchar(100),
  addressstr varchar(5000),
  showcolumn boolean,
  usephones boolean,
  user_id int not null,
  PRIMARY KEY (address_user_id),
  KEY `user_id` (`user_id`),
  CONSTRAINT `address_user_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
);


create table phone_user(
  phone_user_id INT NOT NULL AUTO_INCREMENT,
  typeParameter int,
  phonelabel varchar(25),
  user_id int not null,
  PRIMARY KEY (phone_user_id),
  KEY `user_id` (`user_id`),
  CONSTRAINT `phone_user_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
);

I get this error

ex = (org.springframework.web.util.NestedServletException) org.springframework.web.util.NestedServletException:

Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [ insert into address_user ( addresstitle, addressstr, showcolumn, usephones, user_id ) values (?, ?, ?, ?, ?) ];

Cannot add or update a child row: a foreign key constraint fails (base.address_user, CONSTRAINT address_user_ibfk_1 FOREIGN KEY (user_id) REFERENCES user (user_id)); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:

Delete java code

StringBuffer sb = new StringBuffer();
sb.append(" delete from address_user where user_id = ? ");
jdbcTemplate.update(sb.toString(), id);

StringBuffer sb = new StringBuffer();
sb.append(" delete from phone_user where user_id = ? ");
jdbcTemplate.update(sb.toString(), id);

Insert code

StringBuffer sb = new StringBuffer();
sb.append(" insert into phone_user (");
sb.append(" typeparameter, phonelabel, user_id ) ");
sb.append(" values (?, ?, ?) ");
jdbcTemplate.update(sb.toString(), phone.getTypeParameter().getValue(), phone.phonelabel(),    phone.getUser_id());

StringBuffer sb = new StringBuffer();
sb.append(" insert into address_user (");
sb.append(" addresstitle, addressstr, showcolumn, usephones, user_id ) ");
sb.append(" values (?, ?, ?, ?, ?) ");

j dbcTemplate.update(sb.toString(), address.getAddressTitle(), address.getAddressStr(), address.getShowColumnName(), address.getUsePhones(), phone.getUser_id());

I don't really understand, i don't do an update, i delete and insert.

Yadrif Yadrif · Accepted Answer · 2013-11-20T22:31:30

I'd make sure you have the correct address_user.user_id in the insert query for address_user table. Also, try the query from MySql admin console (or any other DB tool you can use to directly access your DB) just to verify it is okay.

The error says "Cannot add or update ..." so I think it is failing on the insert.

Delete and insert instead update

2 Answers