[MYSQL] SQL CREATE/DROP/ALTER

테이블의 생성: CREATE

CREATE TABLE 테이블이름(
  열이름1 타입 [키 타입] [기본값] [on update 값],
  열이름2 타입 [키 타입] [기본값] [on update 값],
  ...
  [constraint 제약사항 이름 check(조건식)],
  ...
);

[]로 된 부분은 선택사항이다.

 

위 문법을 이용해 간단한 테이블을 생성해보자

 

CREATE TABLE student(
  student_id integer PRIMARY KEY,
  student_name varchar(3) NOT NULL,
  semester int integer DEFAULT 1,
  enter_date datetime DEFAULT NOW() ON UPDATE NOW(),
  
  constraint SEMESTER_CONST check(semester>=1 AND semester<=8)
);

student테이블을 만들 것인데,

열은 student_id(정수 타입, 기본키), student_name(최대 길이 3 문자열, 널 허용 안함), semester(정수 타입, 기본값 1), enter_date(datetime;날짜+시간 타입, 기본값 NOW();현재날짜+시간, 해당 열이 업데이트될 경우 enter_date를 NOW()로 자동 업데이트) 이고,

제약사항은 grade가 1 이상, 4 이하여야 한다.

 

제약사항에는 SEMESTER_CONST라는 이름을 붙였는데, 이는 나중에 이 테이블의 제약사항을 수정할 때 사용된다.

 

이 방법은 열을 선언할 때 너무 길어질 수 있다는 단점이 있는데,

실제 프로덕션 레벨에서는 열에 키나 기본값, 옵션이 혼재되어 매우 복잡해질 수 있다.

 

열의 키 타입을 아래에 몰아서 선언할 수도 있다.

CREATE TABLE 테이블이름(
  열이름1 타입 [기본값] [on update 값],
  열이름2 타입 [기본값] [on update 값],
  ...
  [PRIMARY KEY (열 이름1, ...),]
  [UNIQUE (열 이름2, ...),]
  [FOREIGN KEY (열 이름 3, ...)
    REFERENCE 테이블명[(해당 테이블의 열 이름, ...)]
    [ON DELETE 옵션]
    [ON UPDATE 옵션]
  ,]
  ...
  [constraint 제약사항 이름 check(조건식)],
  ...
);

이 방법을 사용해 또 하나의 테이블을 만들어 보자

CREATE TABLE exam_result(
    course_id integer,
    student_id integer,
    grade char(1),
    
    PRIMARY KEY(course_id, student_id),
    FOREIGN KEY(stuent_id) REFERENCES student(student_id)
      ON UPDATE CASCADE
      ON DELETE CASCADE,
      
    CONSTRAINTS GRADE_CONST CHECK(grade>='A' AND grade<='F')
)

PRIMARY KEY 타입 선언부를 보면 

course_id와 student_id가 하나로 묶여 기본키를 이루고 있다.

(course_id와 student_id가 모두 같은 튜플은 존재할 수 없다)

 

FOREIGN KEY를 보면

현재 테이블의 student_id 컬럼이 student테이블의 student_id를 참조한다는 것을 알 수 있다.

 

ON UPDATE와 ON DELETE는 참조하는 키가 변경되거나 삭제되었을 떄의 동작을 한다.

 

만약 student테이블의 student_id가 123인 튜플이 변경되거나 삭제되었을 때는

ON UPDATE CASCADE ON DELETE CASCADE이므로 exam_result테이블의 student_id도 같은 값으로 바뀐다.

 

옵션으로 CASCADE, SET DEFAULT, SET NULL이 있다.

CASCADE	참조 값이 삭제/변경되었을 때 동일하게 삭제/변경한다.
SET DEFAULT	참조 값이 삭제/변경되었을 때 현재 테이블의 해당 열을 DEFAULT 값으로 바꾼다
SET NULL	참조 값이 삭제/변경되었을 때 현재 테이블의 해당 열을 NULL 값으로 바꾼다

테이블의 수정: ALTER

ALTER TABLE 테이블이름(
  [ADD 추가할열이름 타입 [DEFAULT 기본값], ...)
  [DROP 제거할열이름 [CASCADE|RESTRICT], ...]
  [MODIFY 수정할열이름 타입 [DEFAULT 값], ...]
);

DROP과 MODIFY에서 사용하는 열 이름은 반드시 현재 테이블에 존재하는 열 이름을 입력해야 한다.

 

DROP시 CASCADE옵션을 줄 경우, 해당 키를 참조하는 외래 테이블을 열까지 전부 삭제하고,

RESTRICT 옵션을 줄 경우, 해당 키를 참조하는 외래 테이블의 열이 있을 경우 오류를 발생시킨다.

 

MODIFY시 만약 현재 행이 존재한다면,

값이 절삭되거나(소수였는데 정수로 변환 시 소수점 값 손실)

변환에 실패(문자열이였는데 정수로 변환 시 실패)할 수도 있다.

 

ADD에서 사용하는 열 이름은 반드시 현재 테이블에 존재하지 않는 열 이름을 입력해야 한다.

 

아까 만든 student 테이블을 다시 보자.

CREATE TABLE student(
  student_id integer PRIMARY KEY,
  student_name varchar(3) NOT NULL,
  semester int integer DEFAULT 1,
  enter_date datetime DEFAULT NOW() ON UPDATE NOW(),
  
  constraint SEMESTER_CONST check(semester>=1 AND semester<=8)
);

여기서 student_name은 최대 3글자인데, 만약 이름이 4글자인 '홍길동동'이라는  학생이 새로 왔다고 해보자.

 

그러면 이름 최대 길이를 늘려주어야 하므로,

ALTER TABLE student(
  MODIFY student_name varchar(4) NOT NULL
);

로 4글자까지 받을 수 있도록 변경한다.

 

물론 4글자도 부족하겠지만 일단은 4글자가 최대길이라고 해두자.

테이블의 삭제: DROP

DROP TABLE 삭제할테이블이름 [{RESTRICT | CASCADE}];

테이블 삭제 시, RESTRICT나 CASCADE 옵션을 줄 수 있다.

 

만약 RESTRICT 옵션을 사용하면, 삭제할 테이블을 참조하는 다른 테이블이 있다면 실행에 실패한다.

 

만약 CASCADE 옵션을 사용하면, 해당 테이블을 참조하는 테이블도 전부 삭제한다.

=> CASCADE는 주의해서 사용해야 한다.