MySQL - 트랜잭션, 테이블관리, 제약조건

트랜잭션 (Transaction)

데이터베이스 트랜잭션은 다음 중 하나가 있어야만 이루어진다

• 데이터를 일관성 있게 변경하는 하나 의상의 DML 문
  • DML 문이 실행될 때 트랙잰션 하나가 시작되고, COMMIT 또는 ROLLBACK 을 만나거나 DDL 또는 DCL 문이 있는 경우 트랜잭션이 끝난다.
  • DDL 또는 DCL 문은 별도의 트랜잭션을 실행시키기 때문에 자동으로 커밋이 된다고 보면 된다.
  • ROLLBACK은 그 직전까지 진행되던 트랜잭션을 취소시킨다
  • SAVEPOINT 는 트랜잭션 내에 저장점을 표시하여 ROLLBACK 위치를 설정할 수 있다
• DDL문 하나
• DCL문 하나

테이블 생성 및 관리

데이터 베이스 객체

• 테이블 - 기본 저장 단위, 열과 행으로 구성된 실제 데이터가 저장되는 공간
• 뷰 - 실제 데이터를 가지고 있지는 않은 가상의 논리 테이블 (테이블 합치거나 조건문에 따른 결과를 미리 보는 경우)
• 시퀀스 - 순차적으로 증가하는 숫자 값을 생성해주는 객체; INCREMENT BY
• 인덱스 - 테이블에 속해있는 객체로 테이블 데이터를 색인화한 별도의 데이터 저장공간. 질의 성능을 향상시켜줌.
• 동의어 - 객체에 다른 이름 제공; employees as ‘e’ 에서의 e

테이블 및 열 이름 지정 규칙

• 문자로 시작해야 된다
• 1~30자까지 가능
• A-Z, a-z, 0-9, _, $, #만 가능
• 중복 객체 이름 사용 불가 (동의어와는 다름)
• 예약어 사용 불가

CREATE TABLE - 테이블 생성

create table [스키마이름.]이름 (
	컬럼명 타입(크기) [제약조건],
	컬럼명 타입(크기) [제약조건],
	컬럼명 타입(크기) default '기본값'
);

• DEFAULT - insert 작업 시 사용할 열의 기본값 지정. 함수 사용 가능 (예, SYSDATE)
• 많이 쓰이는 데이터 타입
  • 정수: int 또는 integer
  • 실수: float
  • 문자: char(크기) - 고정크기 / varchar(크기) - 가변크기
  • 대용량 텍스트: longtext (4GB)
  • 날짜: date - ‘년-월-일’ / datetime - ‘년-월-일 시:분:초’
  • …

ALTER TABLE - 테이블 수정

#열 추가
alter table 테이블명
add (컬럼명 타입(크기));

#열 수정
alter table 테이블명
modify (컬럼명 바꿀타입(크기));

#열 삭제
alter table 테이블명
drop column 컬럼명;

#테이블 절단
truncate table 테이블명;

• 열 추가 시 자동으로 마지막 열로 추가되며 표시 위치를 지정할 수는 없다
• 열 추가 시 기존에 있던 행의 추가된 열 값으로는 자동으로 null이 주어진다
• MODIFY 사용 시 열의 데이터 유형, 크기 및 default를 변경할 수 있다. (default는 변경 이후에 삽입되는 항목에만 적용)
  • 열 이름은 바꿀 수 없다. 열 이름을 바꾸려면 RENAME 또는 CHANGE 사용
• 열 삭제는 한번에 하나의 컬럼씩만 가능하고, 테이블 변경 후 반드시 열이 하나 이상 있어야 한다.

테이블 삭제 - DROP, TRUNCATE

#테이블 삭제
drop table 테이블명;

#테이블 절단
truncate table 테이블명;

• DROP TABLE - 테이블의 모든 데이터 및 구조, 인덱스까지 삭제. 롤백 불가
• TRUNCATE - 테이블에서 모든 행을 제거한다. 롤백 불가
  • DELETE는 행삭제는 가능하되 삭제한 만큼의 저장공간을 해제하지는 않기 때문에 수행 속도가 느리다. 롤백 가능

제약조건

#테이블 생성 시 적용방법
create table [스키마이름.]이름 (
	컬럼명 타입(크기) [CONSTRAINT 열레벨 제약조건],
	CONSTRAINT [제약조건이름] 테이블레벨 제약조건(열이름)
);

#테이블 수정 시 적용방법. not null만 modify로 열을 수정할 때 적용
alter table 테이블명
add [제약조건이름] 제약조건(열이름)

#제약조건 삭제
alter table 테이블명
drop 제약조건이름

제약조건이란 열 또는 테이블 레벨에서 적용하는 규칙이다

• 모든 제약조건은 데이터 딕셔너리에 저장된다.
• 제약 조건에 이름을 지정하지 않으면 자동으로 SYS_Cn 형식의 이름을 생성하여 데이터 딕셔너리에 저장한다.
• 제약 조건 유형
  • NOT NULL - 열이 널 값을 포함하지 못하도록 지정. 테이블 레벨에서는 사용불가
  • UNIQUE - 테이블의 모든 행에서 고유한 값을 갖는 열 또는 열 조합을 지정
  • PRIMARY KEY - 테이블의 각 행을 고유하게 식별하는 열 지정
  • FOREIGN KEY - 한 열과 참조된 테이블의 열 간에 외래 키 관계를 설정하고 시행
  • CHECK - 각 행에서 True 이어야 하는 조건을 지정
• 제약조건은 추가, 삭제, 활성화/비활성화 만 가능하고 수정은 불가능

FOREIGN KEY 제약조건

테이블레벨 또는 열 레벨로 정의

create table [스키마이름.]이름 (
	컬럼명 타입(크기) [CONSTRAINT 열레벨 제약조건],
	CONSTRAINT [제약조건이름] FOREIGN KEY(열이름) REFERENCES 테이블명(열이름) [추가조건]
);

• FOREIGN KEY - 제약조건을 걸 자식 테이블의 열을 정의
• REFERENCES - 제약조건에 참조할 부모테이블과 열을 정의
• ON DELETE CASCADE - 추가조건, 부모 테이블의 행이 삭제되는 경우 자식 테이블의 행도 삭제
• ON DELETE SET NULL - 추가조건, 부모 테이블의 행이 삭제되는 경우 자식 테이블의 외래 키 값을 null로 변환