MySQL Error 1833 : foreign key constraint 오류

MySQL Workbench를 이용해서 열심히 ER 다이어그램을 만들고 그걸 테이블로 옮기는 작업까지 했습니다.

아 그런데 멍청하게도 사용자 테이블 PK에 Auto Increment(자동 증가)를 안 걸었어요!!! 

 

그래서 insert 쿼리를 던질때에도 자꾸 에러가 났던 것이었습니다.

 

뒤늦게 사용자 테이블 PK에 AI를 걸려고 보니 error 1833이 떴어요ㅠ

error 1833은 다른 테이블과 foreign key라던지의 관계 설정 때문에 해당 컬럼을 변경할 수 없다는 뜻이에요.

 

구글링으로 뒤져보니 이분 블로그가 많이 도움이 되었는데요,

https://hogu-programmer.tistory.com/43

[MySQL] ERROR 1833

 

저는 저 혼자 개발하는 것이어서 Table Lock까지는 안했고요~

1. 그냥 Workbench로 foreign key 해제

2. 속성/테이블/컬럼 변경

3. foreign key 재설정

 

이 순서대로만 했습니다.

 

결과적으로는 무사히 insert 성공!  foreign key 해제/재설정 방법은 아래를 따라하시면 됩니다.

 

 

---

 

** 개발환경

OS : Windows

MySQL Workbench : 8.0 CE

MySQL : 10.4.25

 

** 간단하게 회원-게시글-댓글 테이블만 있어용

게시글(post)과 댓글(reply)에 모두 회원id가 FK관계 설정이 되어 있기 때문에

저는 post와 reply에서 FK를 삭제할 겁니다.

 

 

1. 지금 foreign key로 설정되어 있는 관계 삭제

왼쪽 메뉴에서 해당 테이블에 마우스 커서를 갖다 대면 메뉴 오른쪽에 3가지 모양이 뜨는데, 거기서 뺀찌???? 같은걸 클릭해주세요.

그리고 ①Foreign Keys를 누르면 아래와 같은 화면이 나옵니다.

(저는 똑같이 복구할 자신이 없어서 삭제하기 전에 화면을 캡처해두었습니다 ㅎㅎㅎ)

②빨간색 부분의 변경할 foreign key를 클릭 후 오른쪽 마우스를 클릭하여 [Delete Selected]를 눌러 관계를 삭제해주세요.

post 테이블 FK 삭제

 

 

 

 

 

2. 변경해야 할 속성/테이블/컬럼 변경

그리고 변경해야 될 테이블로 가서(위의 방법처럼 뺀찌 아이콘을 클릭) 변경해줍니다.

저 같은 경우는 user(사용자) 테이블 생성 시 PK인 id에 Auto Increment 설정을 빼먹은 거라, AI 설정을 다시 해줍니다.

id의 AI 체크박스에 체크 후, 오른쪽 하단의 [Apply] 버튼을 클릭해 바꿔주세요.

user 테이블 AI 재설정

 

 

 

그러면 왼쪽과 같이 해당 작업을 수행하기 위한 쿼리문이 뜨고, 이를 확인 후 오른쪽 하단의 [Apply] 버튼을 눌러주면, 오른쪽과 같은 화면이 뜨면서 작업이 완료됩니다. [Finish]로 해당 창을 닫아 주세요.

변경 성공

 

 

 

 

 

 

3. foreign key 재설정

post 테이블부터 FK를 다시 설정해줍니다. 아까처럼 왼쪽 메뉴에서 post 테이블에 마우스를 올리고 나타나는 뺀찌 아이콘을 클릭해서 들어옵니다.

 

아마 처음에는 전부 빈칸일 거에요~ 이제부터 채우시면 됩니다!

① Foreign Key Name : 1번에서 캡처한 사진을 참고해서 입력해주세요.

     Referenced Table : select box로 선택할 수 있게 되어 있어요. 관계를 맺고자 하는 [DB스키마.테이블]을 선택합니다. 

② Column  : 지금 post 테이블에서 관계를 맺고자 하는 [컬럼]을 선택합니다. 저는 user_id라는 컬럼에 해줄거라 user_id를 선택했어요.

③ Referenced Column : ①의 Referenced Table에서 어떤 컬럼을 선택할거니?라고 묻는 거에요. 저는 user 테이블의 id를 묶어줄거라 id를 선택했습니다.

 

완료 후 오른쪽 하단의 [Apply]를 눌러 다음 단계로 넘어갑니다.

 

 

 

3-1. 만약에 이후에 뭔가 안된다??라면

저는 Workbench하고 MySQL하고의 버전이 안맞아서 VISIBLE 명령어를 읽지 못해서 에러가 뜨는 거라고 짐작(??)하고 있어요.

버전을 맞춰보기도 했지만 해결이 안되서 저는 그냥 VISIBLE만 없애서 사용하고 있거든요, 다른 경우도 있을 수 있습니다.

 

저같은 경우라면, 아래처럼 Error가 뜨면서 안되면 노란색으로 표시된 [Executing:] 아래부분부터 [ERROR '숫자':] 사이에 있는 빨간색 박스에 있는 쿼리문을 복사합니다.

복사하고 에러 창은 닫아주세요.

빨간색만 쿼리입니다.

 

 

 

그리고 Workbench에 왼쪽 문서에 +버튼이 있는 아이콘을 클릭해서 쿼리를 붙여넣습니다.

 

쿼리를 붙여넣은 후, 

① INVISIBLE 삭제

② 첫번째 쿼리 실행 : 자세히 보면 세미콜론(;)이 붙은 쿼리가 2개임을 알 수 있습니다. 첫번째 쿼리 마지막에 커서를 클릭하여 두고, 위쪽 번개 모양 아이콘을 클릭하거나 Ctrl+enter를 눌러 쿼리를 실행시켜 줍니다.

③ 두번째 쿼리 실행 : 2번과 똑같은 방식으로 쿼리를 실행시켜 줍니다.

④ 결과 확인 : ②,③번이 실행이 잘 되었다면 초록색 표시의 결과를 얻을 수 있습니다.

 

 

 

 

---

 

재설정까지 완료하니까 Insert가 아주 잘 됩니다.  끝*^^*