연관 관계에 있는 데이터 삭제

alter table user_plan add constraint foreign key (plan_idx) references plan(plan_idx) on delete cascade;
alter table attraction_plan add constraint foreign key (plan_idx) references plan(plan_idx) on delete cascade;

부모 테이블의 데이터가 삭제되면 연관된 자식 테이블의 데이터도 같이 삭제되도록 할려고 delete cascade를 줬다.
하지만, 안 된다.

### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`enjoytrip`.`attraction_plan`, CONSTRAINT `attraction_plan_ibfk_1` FOREIGN KEY (`plan_idx`) REFERENCES `plan` (`plan_idx`))
### The error may exist in file [C:\SSAFY\민정\workspace\enjoy-trip\trend_gaza\bin\main\mapper\plan.xml]
### The error may involve com.ssafy.trend_gaza.plan.repository.PlanMapper.deletePlan-Inline
### The error occurred while setting parameters
### SQL: DELETE    FROM plan   WHERE user_id = ? and plan_idx = ?
### Cause: java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`enjoytrip`.`attraction_plan`, CONSTRAINT `attraction_plan_ibfk_1` FOREIGN KEY (`plan_idx`) REFERENCES `plan` (`plan_idx`))
; Cannot delete or update a parent row: a foreign key constraint fails (`enjoytrip`.`attraction_plan`, CONSTRAINT `attraction_plan_ibfk_1` FOREIGN KEY (`plan_idx`) REFERENCES `plan` (`plan_idx`)); nested exception is java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`enjoytrip`.`attraction_plan`, CONSTRAINT `attraction_plan_ibfk_1` FOREIGN KEY (`plan_idx`) REFERENCES `plan` (`plan_idx`)), mergedContextConfiguration = [WebMergedContextConfiguration@4a003cbe testClass = PlanServiceImplTest, locations = '{}', classes = '{class com.ssafy.trend_gaza.plan.service.PlanServiceImpl}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{spring.config.location=classpath:application.properties, org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}', contextCustomizers = set[org.springframework.boot.test.autoconfigure.actuate.metrics.MetricsExportContextCustomizerFactory$DisableMetricExportContextCustomizer@6aba2b86, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@4b3fa0b3, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@4a94ee4, [ImportsContextCustomizer@4082ba93 key = [org.springframework.boot.test.autoconfigure.web.servlet.MockMvcWebDriverAutoConfiguration, org.springframework.boot.test.autoconfigure.web.servlet.MockMvcAutoConfiguration, org.springframework.boot.autoconfigure.security.oauth2.client.servlet.OAuth2ClientAutoConfiguration, @org.springframework.context.annotation.ComponentScan(basePackageClasses={}, basePackages={"com.ssafy"}, excludeFilters={}, includeFilters={}, lazyInit=false, nameGenerator=org.springframework.beans.factory.support.BeanNameGenerator.class, resourcePattern="**/*.class", scopeResolver=org.springframework.context.annotation.AnnotationScopeMetadataResolver.class, scopedProxy=DEFAULT, useDefaultFilters=true, value={"com.ssafy"}), org.springframework.boot.autoconfigure.security.servlet.SecurityAutoConfiguration, org.springframework.boot.autoconfigure.security.servlet.SecurityFilterAutoConfiguration, org.springframework.boot.autoconfigure.security.servlet.UserDetailsServiceAutoConfiguration, org.springframework.boot.autoconfigure.security.oauth2.resource.servlet.OAuth2ResourceServerAutoConfiguration, org.springframework.boot.test.autoconfigure.web.servlet.MockMvcSecurityConfiguration, org.springframework.boot.test.autoconfigure.web.servlet.MockMvcWebClientAutoConfiguration, org.springframework.boot.test.autoconfigure.web.reactive.WebTestClientAutoConfiguration]], org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@747ddf94, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@6cf0e0ba, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer@5c6648b0, org.springframework.boot.test.context.SpringBootTestArgs@1, org.springframework.boot.test.context.SpringBootTestWebEnvironment@153f5a29], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map['org.springframework.test.context.web.ServletTestExecutionListener.activateListener' -> true, 'org.springframework.test.context.web.ServletTestExecutionListener.populatedRequestContextHolder' -> true, 'org.springframework.test.context.web.ServletTestExecutionListener.resetRequestContextHolder' -> true, 'org.springframework.test.context.event.ApplicationEventsTestExecutionListener.recordApplicationEvents' -> false]]

원인은 외래키의 이름을 적어주지 않아서였다. 
저 코드로 실행하면 새로운 외래키가 생성된다.
예를 들어, 현재 외래 키 이름이 plan_ibfk_1라면, 다음과 같이 작성한다.

alter table user_plan add constraint plan_ibfk_1 foreign key (plan_idx) references plan(plan_idx) on delete cascade;

 
 
 
 

---

참고 👇

https://m.blog.naver.com/pjt3591oo/220617636202

[mysql] FOREIGN KEY 연관된 데이터 삭제 ON DELETE CASCADE