[MySQL] 데이터 정의어 DDL의 CREATE

데이터 정의어 DDL

DDL; Data Definition Language
생성, 변경, 삭제 등 데이터베이스 구조를 정의한다.

 

생성 CREATE

CREATE 명령어를 통해서 DATABASE와 TABLE을 생성할 수 있다.
테이블 생성 방법과 각 제약조건에 대해 알아보자.

 

---

 

CREATE DATABASE

데이터베이스를 생성. 테이블 생성 전에 실행하는 단계로 데이터베이스는 여러 테이블을 포함하는 큰 틀.

 

CREATE TABLE

테이블을 생성. 데이터베이스 위에 올라가는 실제 값이 저장되는 테이블. 릴레이션이라고 부르기도 한다.

 

속성이름 데이터타입 [제약조건]

속성1 INT NOT NULL;

테이블 생성 후 속성 생성은 위처럼 진행된다.
INT타입의 속성1 이라는 이름의 Attribute를 생성하고, NULL값이 오면 안 된다는 제약조건을 걸었다.

속성이름 (공백) 데이터타입 을 기본 베이스로 하며 여러 속성 정의 시 구분은 쉼표(,)로 한다.

 

데이터 타입

• INT, INTEGER: 정수
• SMALLINT: int보다 작은 정수
• CHAR(n), CHARACTER(n): 길이가 n인 고정 길이 문자열
• VARCHAR(n):  최대 길이가 n인 가변 길이 문자열
• NUMERIC(p, s), DECIMAL(p, s: 고정 소수점 실수 (p: 소수점 이상 길이, s: 소수점 이하 길이)
• FLOAT(n): 길이가 n인 부동 소수점 실수
• REAL: 부동 소수점 실수
• DATE: 연, 월, 일
• TIME: 시, 분, 초
• DATETIME: 날짜와 시간

 

---

 

제약조건 CONSTRAINT

데이터 무결성을 지키기 위해 제한되는 조건. 데이터 무결성은 데이터들의 정확성, 일관성, 유효성이 유지되는 것을 의미한다. PK, FK, UNIQUE를 설정하거나 기본값이나 수식을 정해주기도 한다.

※ 기본키, 대체키, 슈퍼키, 후보키, 외래키 등에 관련된 자세한 내용은 다른 글에서 설명.

 

키(Key) 정의

1. 기본키 PRIMARY KEY

CREATE TABLE T1 (
	속성1 INT NOT NULL,
    속성2 VARCHAR(10),
    PRIMARY KEY(속성1);
)

T1이라는 릴레이션을 생성하면서 속성(Attribute) 두개를 만들었다.
그 중 '속성1'은 기본키(PK; PRIMARY KEY)로 설정하여 NULL값이 오지 않도록 NOT NULL제약을 두었다.

 

2. 대체키 UNIQUE KEY

CREATE TABLE T2 (
	속성x INT not null PRIMARY KEY,
	속성y VARCHAR(10) not null,
	UNIQUE(속성y);
)

'속성x' 어트리뷰트 생성하면서 PK로 바로 지정해주었다.
'속성y'는 UNIQUE를 이용해 기본키가 되지 못한 후보키 = 대체키로 지정한다.

 

3. 외래키 FOREING KEY

CREATE TABLE 사원 (
	사원번호 INT NOT NULL,
	사원이름 VARCHAR(10) NOT NULL,
	소속부서 INT DEFAULT(0),
	PRIMARY KEY(사원번호),
	FOREIGN KEY(소속부서) REFERENCES 부서(부서번호) ON DELETE CASCADE ON UPDATE CASCADE;
)

외래키는 서로 다른 테이블을 연결하기 위해 사용한다. 다른 테이블에 있는 데이터가 일관될 수 있도록 도와준다.

Foreing key (참조하는 속성) References 다른테이블(참조되는 속성) [제약조건]

• 외래키가 포함되어 있는 테이블은 참조하는 테이블 = 자식 테이블
• '참조하는 속성'과 '참조되는 속성'은 서로 연결되어 있음
• 가지고 오는 다른테이블은 참조되는 테이블 = 부모 테이블

서로 같은 릴레이션끼리 색상을 묶어 나누어보았다.
외래키를 통해서 다른 테이블의 속성을 참조하고 있는 것을 자식이라고 하고, 참조가 되는 쪽은 부모라고 한다.

 

두 릴레이션 중 하나의 값이 바뀌었을 경우, 두 릴레이션이 연결되어 있다면 다른 릴레이션의 값은 어떻게 해야 할까?
참조 무결성을 위해 처리 방법을 지정해주는게 좋다.

 

참조 무결성 제약조건 (Referential Integrity Constraint)
: 외래키 값은 Null이거나 참조하는 릴레이션의 기본키와 동일해야 한다.
  즉, 참조할 수 없는 외래키 값은 가질 수 없다.

 

 

외래키 옵션

1. NO ACTION: 자동 적용, 변경하지 못하게 함
2. CASCADE: 관련된 튜플을 함께 변경
3. SET NULL: 관련된 튜플의 외래키 값을 NULL로 변경
4. SET DEFAULT: 미리 지정한 default값으로 변경
5. RESTRICT: 자식 테이블이 PK가 아닐 때만 부모테이블에서 수정·삭제

 

위 옵션을 사용하여 외래키로 연결한 릴레이션에서 변경이 이루어질 경우 대처 방법을 미리 설정할 수 있다.
ON [ DELETE | UPDATE ] 옵션으로 작성한다.

 

 

위에서 작성한 SQL코드를 다시 보면 아래와 같은 해석이 나온다.

CREATE TABLE 사원 (
	사원번호 INT NOT NULL,
	사원이름 VARCHAR(10) NOT NULL,
	소속부서 INT DEFAULT(0),
	PRIMARY KEY(사원번호),
	FOREIGN KEY(소속부서) REFERENCES 부서(부서번호) ON DELETE CASCADE ON UPDATE CASCADE;
)

[부서] 테이블의 '부서번호'를 참조하는 [사원]의 '소속부서'는 삭제(Delete)와 수정(Update)가 있을 때 부모를 따라 함께 변경한다. (On ~ CASCADE)

 

---

 

DEFAULT

외래키를 만들었던 코드를 보면 소속부서 INT DEFAULT(0)가 작성되어 있다.
Default는 아무것도 입력하지 않아도 미리 설정해둔 값이 입력된다.

만약 위 코드가 아래와 같이 적혀있었다면,
foreign key(소속부서) references 부서(부서번호) on delete cascade on update set default;
삭제가 될 때는 같이 삭제되지만, 수정이 있을 경우 default로 설정해둔 0으로 변경된다.

외래키 설정 시 SET DEFAULT를 해줄 때는 이처럼 미리 Default값을 지정해두거나 Null값이 허용될 때 사용할 수 있다.

 

 

CEHECK

CREATE TABLE 수강생 (
	학번 INT NOT NULL,
	이름 VARCHAR(10) NOT NULL,
    나이 INT CONSTRAINT 성인 NOT NULL,
	PRIMARY KEY(학번),
	CONSTRAINT 성인 CHECK (나이 >= 19);
)

성인을 대상으로 하는 학원에서 관리하는 수강생 테이블이다.
만 나이 기준으로 성인을 구분하기 위해 '나이'에 19 이상이라는 조건을 걸었다. 이 제약조건의 이름은 '성인'이다.

CONSTRAINT [제약조건 이름] CHECK 조건식;

 

제약조건은 이름을 지정하지 않아도 설정이 가능하지만, 이름을 미리 지정해두면 제약조건을 삭제할 때 용이하다.
우리 학원이 성인에서 미성년자로 범위를 넓힌다면 이 이름을 가지고 코드 한 줄로 수정 할 수 있다.

ALTER TABLE 수강생 FOREIGN KEY 성인;

 

 

또한 다른 제약조건처럼 간결하게 작성할 수 있다.

CREATE TABLE 수강생 (
	학번 INT NOT NULL,
	이름 VARCHAR(10) NOT NULL,
    나이 INT CHECK (나이>=10 and 나이<20)  NOT NULL,
	PRIMARY KEY(학번);
)

제약조건 이름을 설정하지 않고, 칼럼에서 직접 작성하였다.