DDL: 제약조건 & 인덱스 & 스키마 변경/삭제

제약조건

 

<데이터 무결성 제약조건(Data Integrity Constraints)>

- NOT NULL

- UNIQUE
- PRIMARY KEY

- CHECK

 

<참조 무결성 제약조건(Referential Integrity Constraints)>

두 테이블 간 참조 관계가 있을 때

 

- FOREIGN KEY: 외래키 명시

- CASCSDING ACTIONS: 참조당하는 테이블의 행 데이터 삭제/변경 -> 자식 테이블에서의 처리 명시

* CASCADE

* SET NULL

* SET DEFAULT

* NO ACTION

* RESTRICT

---

데이터 무결성 제약조건

 

데이터베이스에 대한 승인된 변경으로 인하여 데이터 일관성 및 무결성이 손실되지 않도록 보장 -> 우발적 손상 예방

단일 릴레이션에 대한 제약조건

 

1. NOT NULL

: 특정 컬럼이 NULL 값 갖는 것 방지

-> 특정 컬럼에 값이 반드시 존재하도록 제한

create table instructor (
	ID varchar(5),
    name varchar(20) not null, // 이름이 null이 되는 것을 막도록 설계 단계에 써줌
    dept_name varchar(20),
    salary numeric(8, 2) check (salary > 29000),
    primary key (ID),
    foreign key (dept_name) references department (dept_name) on delete set null
);

create table student (
	ID varchar(5),
    name varchar(20) not null,
    dept_name varchar(20),
    tot_cred numeric(3, 0) check (tot_cred >= 0),
    primary key (ID),
    foreign key (dept_name) references department (dept_name) on delete set null
);

 

 

2. UNIQUE

: 대체키(유일성 있지만 기본키가 되지 못함) 제약조건

-> 특정 컬럼에 중복된 값이 포함되지 않도록 제한

create table employee (
	employee_id int auto_increment,
    first_name varchar(50),
    last_name varchar(50),
    email varchar(100) unique, // email은 대체키 (테이블의 투플을 유일하게 구별할 수 있도록 제약조건 걸어주기)
    hire_date date,
    salary decimal (10, 2),
    primary key (employee_id)
);

결과

 

3. PRIMARY KEY(=NOT NULL+UNIQUE)

: 기본키 제약조건

-> 각 행을 고유하게 식별할 수 있는 속성 집합(기본키) 명시

: NOT NULL(NULL값 가질 수 없음) + UNIQUE(유일하게 구별할 수 있어야)

create table section (
	course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6) check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
    year numeric(4, 0) check (year >1701 and year <2100),
    building varchar(15),
    room_number varchar(7),
    time_slot_id varchar(4),
    primary key (course_id, sec_id, semester, year), // 기본키 지정(복합 기본키 가능)
    foreign key (course_id) references course (course_id) on delete cascade,
    foreign key (building, room_number) references classroom (building, room_number) on delete set null
);

 

4. CHECK

: 허용되는 값에 대한 조건 명시

-> 올바르고 정확한 값이 들어갔는지

 

check로 컬럼의 제약조건 걸어두면 추후에 데이터베이스가 그 조건에 맞지 않는 값은 저장하지 않음

create table section (
	course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6) check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), //컬럼 값 조건 명시
    year numeric(4, 0) check (year >1701 and year <2100), //컬럼 값 조건 명시
    building varchar(15),
    room_number varchar(7),
    time_slot_id varchar(4),
    primary key (course_id, sec_id, semester, year),
    foreign key (course_id) references course (course_id) on delete cascade,
    foreign key (building, room_number) references classroom (building, room_number) on delete set null
);

 

check 보다 더 포괄적인 제약 조건을 정의할 수 있는 ASSERTION 구문도 존재하지만

check 제약 조건으로 대부분 해결 가능하기 때문에 MySQL과 Oracle에서는 지원하지 않는다.

 

참조 무결성 제약조건

 

: 한 릴레이션에서 나타나는 속성 집합이 다른 릴레이션에도 일관되게 나타날 수 있도록 보장

 

1. FOREIGN KEY

: 외래키 명시

* MUL: 인덱스가 걸려 있으나, 중복값을 가질 수 있는 컬럼 표시

foreign key는 인덱스를 내부적으로 자동 생성해준다. 

 

<예제1>

create table section (
	course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6) check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
    year numeric(4, 0) check (year >1701 and year <2100),
    building varchar(15),
    room_number varchar(7),
    time_slot_id varchar(4),
    primary key (course_id, sec_id, semester, year),
    foreign key (course_id) references course (course_id) on delete cascade, //외래키 명시
    foreign key (building, room_number) references classroom (building, room_number) on delete set null
);

foreign key: building, room_number, course_id

primary key: course_id, sec_id, semester, year

=> course_id는 foreign key이기도, primary key이기도 하므로  MUL을 따로 써주지 않음

MUL은 하나에만 써줌 -> building, room_number 모두 외래키이지만 building에만 대표적으로 적어줌

<예제2>

create table teaches (
	ID varchar(5),
    course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6),
    year numeric(4, 0),
    primary key (ID, course_id, sec_id, semester, year),
    foreign key (course_id, sec_id, semester, year) references section (course_id, sec_id, semester, year) on delete cascade,
    foreign key (ID) references instructor (ID) on delete cascade
);

 

기본키이면서 외래키일 수 있음 -> 하나만 적어줌

 

2. CASCADING ACTIONS

 

Q) 테이블의 행 데이터에 변경/삭제가 발생했을 때 -> 자식 테이블 데이터와의 일관성 유지하기 위해 어떻게 해야 하는가?

A) 변경/삭제할 부모테이블의 데이터와 연관된 자식 테이블 데이터를 먼저 변경/삭제(null로 바꿈)
-> 원래 의도했던 부모 테이블의 데이터 변경/삭제

* 부모테이블: 참조당하는 테이블

* 자식테이블: 참조하는 테이블

 

하지만 이렇게 하나하나 처리해주는 것은 현실적으로 불가능하다.

부모 테이블을 먼저 변경/삭제할 수 있게 해줄 수 있도록 하기 위해 cascading action이 사용된다.

 

Q) 부모테이블을 먼저 변경/삭제하면서 일관성 유지하려면 어떻게 해야 하는가?

A) Cascading Actions

 

<cascading action>

: 부모테이블의 행 데이터 변경/삭제 -> 자식 테이블에서의 처리 명시

명령어		역할
CASCADE	부모 테이블 행 변경/삭제 되었을 때 행동 명시	부모 테이블 행 변경/삭제 -> 자식 테이블에서도 동일한 작업
SET NULL		부모 테이블 행 변경/삭제 -> 자식 테이블의 관련 값을 NULL로 * 자식 테이블 컬럼 NOT NULL이면 에러 !
SET DEFAULT		부모 테이블 행 변경/삭제 -> 자식 테이블의 관련 값을 DEFAULT값으로 * 자식 테이블 컬럼 DEFAULT 필요 !
NO ACTION	불가 조건	트랜잭션 내부에서는 부모 먼저 삭제/변경 허용 -> 트랜잭션 종료 시점에 평가해 일관성 깨졌다면 적용x * 트랜잭션 단위로 평가 !!
RESTRICT		자식 테이블의 데이터가 있으면 -> 부모 테이블의 행 삭제/변경 불가 * 무결성 중요할 때만 !

 

create table takes (
	ID varchar(5),
    course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6),
    year numeric(4, 0),
    grade varchar(2),
    primary key (ID, course_id, sec_id, semester, year),
    //on delete cascade: section이라는 부모에서 변경/삭제 발생하면 takes에서 해당 인수 delete 해주라는 뜻
    foreign key (course_id, sec_id, semester, year) references section (source_id, sec_id, semester, year) on delete cascade
    //student라는 부모에서 변경/삭제 발생하면 delete 해주라는 뜻
    foreign key (ID) references student (ID) on delete cascade
);

create table section (
	course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6) check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
    year numeric(4, 0) check (year > 1701 and year <2100)
    building varchar(15),
    room_number varchar(7),
    time_slot_id varchar(4),
    primary key (course_id, sec_id, semester, year),
    foreign key (course_id) references course (course_id) on delete cascade
    //classroom이라는 부모 테이블에서 변경/삭제 발생하면 section에서 해당 인수 null로 바꿔달라는 뜻
    foreign key (building, room_number) references classroom (building, room_number) on delete set null
);

 

사용자 정의 도메인

 

도메인: 애트리뷰트가 가질 수 있는 값의 범위와 제약조건

MySQL에서는 지원하지 않음

 

- CREATE DOMAIN: 기존 데이터 타입에 제약조건 추가

- CREATE TYPE: 새로운 데이터 타입 생성(구문 내에 직접적으로 제약조건 추가 불가능)

 

create domain person_name char(20) not null;

create domain degree_level varchar(10)
	constraint degree_level_test
    check (value in ('Bachelors', 'Masters', 'Doctorate'));

 

인덱스(Index)

 

목적: 검색 속도 향상을 위해 특정 컬럼에 걸어주는 자료구조

 

데이터베이스는 내용 기반 검색 -> 테이블에 투플이 많으면 검색 속도 느림 -> 인덱스라는 값을 주어 빠른 검색 도움

* (키, 값) = (col_1의 값, 해당 row의 주소)인 자료구조 (트리 등) 생성

-> 트리 기반 탐색법으로 col_1 값 기준으로 빠른 검색 가능

-> 원하는 col_1 값을 가진 row의 주소에 접근해 데이터 바로 가져오기 가능

 

하나의 테이블에 여러 컬럼에 인덱스를 걸기 가능

-> 각 컬럼에 대해 각각 별도의 자료구조가 생성됨

-> 인덱스 너무 많이 걸면 성능 저하

-> 자주 쓰이는 컬럼에만 인덱스 생성하자 !

 

자동으로 생성되는 인덱스

 

- PRIMARY KEY

- FOREIGN KEY

- UNIQUE 

 

세 가지 제약조건을 가진 키에는 내부적으로 인덱스 자동생성

세 가지 중 하나의 제약조건을 가진 키라면 자동으로 BTREE라는 인덱스가 생성된 것을 볼 수 있다.

show index from 테이블명;

 

명시적인 인덱스 생성(CREATE INDEX)

 

<구문>

create index INDEX_NAME on TABLE_NAME(TARGET_COLUMN);

 

<예시>

"특정 테이블 특정 컬럼에 인덱스 걸어줘"

create index hiredate_index on employee(hire_date);

show index from employee;

스키마 변경/삭제

 

- 변경: ALTER

- 삭제: DROP/TRUNCATE

 

변경: ALTER

 

<컬럼 추가>

 

- 구문: 

alter table r add ATTRIBUTE DATATYPE;

 

- 예시:

alter table student add address varchar(100);

 

<컬럼 삭제>

 

- 구문:

alter table r drop ATTRIBUTE

 

- 예시:

alter table student drop address;

 

 

삭제: DROP/TRUNCATE

 

- TRUNCATE(DELETE): 데이터만 삭제, 하지만 스키마는 남아 있음 -> 빈 스키마

- DROP: 데이터, 스키마 아예 모두 삭제

truncate table time_slot;
delete from time_slot; //두 문장 동일 기능

drop table time_slot;

	DROP	TRUNCATE(DELETE)
데이터 삭제	o	o
스키마 삭제	o	x