SQL과 참조무결성.

HJ seo·2022년 11월 29일
0

sql

목록 보기
2/2

잡담.

본래 서버 자체에 큰 관심을 가지고 있지 않았다가 덜컥 취업이 되면서 서버 전환에 대한 업무를 시험삼아 맡게 되었다. 그러면서 SQL developer를 사용하게 되었고, 시범적으로 MYSQL을 깔 때 제공하는 sample 데이터에서 dump 파일로 이루어져 있는 데이터를 서버로 올렸을 때 잠깐 막히게 만들었던 SQL의 속성인 참조무결성에 대한 것을 중점으로 글로 써보게 되었다.

과정을 설명하기에 앞서..

  1. SQL developer를 사용하기 전에 MYSQL을 쓸 수 있게 셋팅을 해야할텐데 간단히 connecter를 SQL developer에 등록하면 된다.

    1. 처음 사용해서 MYSQL을 바로 까는 경우 MYSQL installer에서 connector/J 파일(jar)을 같이 받을 수 있고, 기존에 MYSQL을 사용하고 있어서 installer를 굳이 열기 귀찮은 경우 크롬에 검색해서 파일을 따로 받을 수도 있다.
    2. SQL developer 상단의 도구 > 환경설정 > 데이터베이스(+클릭) > 타사 JDBC 드라이버 에 들어간 후 항목 추가를 통해 다운받은 jar 파일을 등록해주자.
    3. SQL developer를 재실행해준 후 접속의 +를 누르고 id,pw,주소등을 입력해주고 MYSQL에 접속해주자.(나의 경우 단순히 root 계정을 이용했지만, 보안의 문제가 걱정이 된다면 따로 계정을 하나 생성해서 테스트하는 것을 추천한다., 추가로 MYSQL이 아닌 SQL developer와 호환이 되는 다른 DB를 사용할 경우에도 위와 비슷한 방식으로 SQL developer를 통해 해당 DB에 접근이 가능하다.)
  2. 참조무결성에 대한 설명은 sample 데이터에서 사용했던 6개의 테이블을 통해 간접적으로 설명할 예정이다.

  3. 설치 링크들은 맨 아래 table 형식으로 만들어두었다.

경험.

(mysql에 로그인을 한 이후부터 설명을 할 예정이다.)

  1. 처음 MYSQL 서버에 붙은 후 table을 생성하기 위해 employee.sql 파일을 실행했다.

1.999. 처음 dump파일을 실행시켰을 때 오류가 났는데 from 뒤의 위치 확인을 잘 해주자.(내가 설정을 잘못한 것인지, 아니면 root 계정의 특징인지 모르겠지만, 기본적으로 생성되어있는 정보(user,sys등)이 있었고, sql 파일을 실행시켜서 생성한 DB의 경우 employees. 을 위치 앞에 붙여주어야 했다., go and think의 문제점.. )

  1. 그 직후 차례대로 dump 파일에 있는 데이터를 등록하기 위해 load_(~~).dump 파일을 위에서부터 차례대로 실행시켰으나 두 번째부터 오류가 난다..

    • 오류 메시지의 내용은 Cannot add or update a child row: a foreign key constraint fails 로 시작된다.
    • 대강(?) 초월번역을 때려서 설명하자면, foreign key-참조하는 table에 있는 key값-를 확인할 수 없어서 명령이 실행되지 않았다는 소리이다.
  2. 이게 뭔소린지 몰라서 좀 삽질하다가 애러메세지를 검색해봤더니 오늘 이야기의 중점인 참조무결성에 대한 애러라는 설명이 나오더라..

    • 참조무결성이란?(from test_db)

      • DB 내 여러 테이블의 관계를 생각해보자. 해당 table들의 종류는 총 6가지로 departments, dept_emp, dept_manager, employees, salaries, titles가 있다. 여기서 예를 들어 맨 처음 salaries에 데이터를 채워주는 dump 파일을 실행시켰다고 가정해보자. 이 데이터의 경우 employeesemp_no를 참조하는 동일한 이름의 속성을 가지고 있다.
      • 만약 table에 이 데이터들이 그대로 잘 들어갔다고 가정해보자. 이 직후 다른 작업을 하지 않았을 때 DB를 해석해보면 우리는 피고용인이 없는데 emp_no가 있는 사람에게 월급을 지불한 것이다.
      • 따라서 salaries의 데이터를 추가하기 이전에 필요한 작업은 employees의 데이터를 추가해야 한다.(정확하게, 그 안의 primary key인 emp_no가 있어야 한다.)
    • 어느 회사에서 지불한 것인지, emp_no가 있는 사람이 누구인지등 오류가 많지만 정말 설명을 위한 가정이다.(즉, 위의 설명대로 load_salaries.dump를 실행시켜봐야 데이터가 등록되기는 커녕 로그에 2번 설명의 애러메시지를 받을 뿐이다.)

  3. 이에 대한 것을 파악하고, table 내의 모든 데이터를 옮기는데 성공. 따로 sql 파일을 열어서 테스트해본 결과 정상적으로 옮겨진 것을 확인할 수 있었다.

    • 이 글을 쓰기 위해 파일을 다시 살펴보며 알게 된 사실이지만, 코드를 살펴보는 습관은 역시 중요한 것 같다.. 아래 코드는 employees.sql에서 dept_manager table 생성을 명령하는 코드를 가져온 것이다. 코드를 통해 확인할 수 있는 부분은 해당 table에 등록되는 데이터에 필요한 참조값은 employees table의 emp_no, 그리고 departments table의 dept_no이다.
-- 	employees.sql 의 dept_manager table 생성 부분.

CREATE TABLE dept_manager (
   emp_no       INT             NOT NULL,
   dept_no      CHAR(4)         NOT NULL,
   from_date    DATE            NOT NULL,
   to_date      DATE            NOT NULL,
   FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
   FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
   PRIMARY KEY (emp_no,dept_no)
); 

설치 링크(table)

링크 목적다운로드 주소추가로 필요한 것 및 기타
SQL Developer 설치 주소https://www.oracle.com/database/sqldeveloper/technologies/download/oracle 계정(회원가입에 회사등의 정보가 필요한데 뭐 없다면 그냥 . 찍자.)
MYSQLhttps://dev.mysql.com/downloads/installer/400MB짜리를 설치.
MYSQL(Connetor만)https://dev.mysql.com/downloads/connector/j/선택창에서 Platform independent 클릭.
MYSQL(test_db 파일)https://github.com/datacharmer/test_db-
profile
다양한 분야에 관심이 많은 초보 개발자 입니다.

0개의 댓글