25일차(2022.01.26) _ DDL - 테이블 생성, 삭제, 제약조건 설정, KEY

1. DDL

 

- DB의 Table, User, Sequence 등등을 Object라고 함
- DB의 Object를 생성, 수정, 삭제할 때 사용하는 언어를 DDL이라고 함
- DB의 Data를 생성, 수정, 삭제할 때 사용하는 언어를 DML이라고 함

- Create : 생성
- Alter : 수정
- Drop, Truncate : 삭제
- Rename : 이름 수정

 

---

2. 제약 조건 설정 방식

 

1. 제약 조건 이름 : 테이블명_컬럼명_제약조건의약칭
 - 제약 조건 약칭 
  1) Primary Key : PK (P)
  2) Foreign Key : FK (R)
  3) Unique : U
  4) Not NULL : NN (C)
  5) Check : C

- 제약 조건 이름은 중복 불가

 

---

3. DB 모델링 용어 정리

 

1. Table : 릴레이션 (Relation)
2. Column : 속성 (Attribute)
3. Row : 튜플 (Tuple)
4. 테이터들의 집합 : 인스턴스 (Instance)
5. 기본구조, 어떤 Attribute들로 구성되어 있는 지 : 스키마 (Schema)
6. 한 컬럼(Tuple)에 있는 데이터들의 집합 : 도메인 (Domain)

 

---

4. Key의 종류

 

1. 후보키 (Candidate Key)
 - 릴레이션을 구성하는 속성들 중에서 튜플을 유일하게 식별할 수 있도록 하는 속성들의 집합
 - 모든 릴레이션은 하나 이상의 후보키가 있어야 함
 - 중복이 안되는 Data가 있는 속성들(Attribute)
 - Primary Key(기본키)가 될 수있는 속성들을 후보키라고 말함

2. 기본키 (Primary Key)
 - 후보키 중에서 하나를 선택
 - 릴레이션에서 하나의 튜플을 구별할 수 있는 속성

3. 대체키 (Alternate Key)
 - 후보키 중에서 Primary Key를 제외한 나머지 Key들
 - Index 생성이나, 검색에 자주 사용될 확률이 높음

4. 슈퍼키 (Super Key), 복합키 (Composite Key)
 - 유일성(Unique) 만족, 최소성은 만족하지 않은 것
 - 두 개의 속성(Attribute, Column)를 모아서 하나의 키로 만드는 것 

5. 외래키 (Foreign Key)
 - 다른 릴레이션(Table)과 참조관계를 맺을 때 사용
 - 외래키로 지정되면 기본키에 없는 값은 입력할 수 없음
 - 참조 당하는 Table : Parent(부모), 참조하는 Table : Child(자식)
 - 부모의 Tuple(Row)을 삭제 하려면 참조하는 자식 Tuple(Row)이 없어야 함
 - 자식이 있으면 자식을 모두 삭제 하고 부모를 삭제

 - Table 생성 시 부모의 Tuple(Row)가 삭제될 때 자동으로 자식의 Tuple(Row)를 변경하는 옵션
  1) 부모가 삭제될 때 자식의 Tuple도 자동으로 같이 삭제 : ON DELETE CASCADE
  2) 자식의 Attribute(Column)에 NULL을 넣어줌 : ON DELETE SET NULL 

 

설명1. Primary Key, 후보키, 대체키

 

 

만약에 테이블 내에 내가 같은 데이터가 3개가 들어갔다고 해보자.

그런데, 이 중에 하나만 지우고 싶은데, 모든 데이터가 같으니까 DELETE문의조건문에 어떤 조건을 주더라도

하나를 지워버리는 3개의 데이터가 다같이 지워져버린다.

그래서 STU_ID의 경우에는 Primary Key로 지정을 해서 중복된 값이 없게끔 지정해주어야 한다.

그래야 다른 데이터가 중복이더라도, ID값은 중복이 아니기 때문에 조건을 주어서 하나의 데이터만을 지울 수 있다.

물론 Unique도 중복불가능이지만, NULL이 들어갈 수가 있기 때문에 NULL이 들어가버리면 

구분할 수 있는 유일한 값이 없어져버리게된다.

정리하자면, 여러 개의 데이터가 있는데 하나의 데이터만을 고르고 싶을 때

중복되지 않는 컬럼(Attribute)중에 조건을 주어야 하는데 그것을 Primary Key로 지정을 한다는 소리이다.

 

이 Primary Key를 지정하는 방법은 후보키(Candidate Key)중에 선택을 하게 된다.

위의 경우에서는, 학번, 전화번호, 이메일이 후보키이다.

후보키의 기준은 일단 중복이 불가능한 데이터이다.

학번, 전화번호, 이메일은 일단 상식적으로 무조건 고유한 값이다. 절대 중복 불가이다.

근데, 문제는 전화번호와 이메일은 추후에 변경이 가능할 수가 있다는 점이 문제이다.

근데 학번은 절대로 변경도 안된다. 그래서 이 세가지 데이터 중에서 학번이 Primary Key로 지정이 된다.

Primary Key로 지정되지 못한 나머지 2가지 키들은 대체키, 보조키라고 부른다.

 

---

설명2. Super Key

 

두 개 이상의 컬럼들을 합쳐서 Primary Key로 만들 수 있다. 

위의 예시를 예로 들면, 전화번호 컬럼이랑 이메일 컬럼 두개를 합쳐서 Primary Key로 지정을 하면 Super Key가 된다.

이는 유일성은 확보가 된다 (중복은 불가능) 근데, 아까 ID를 Primary Key로 썼을 때는

하나의 컬럼만을 Key로 사용하면 됐었는데 지금같은 경우는 2개의 컬럼을 Key로 사용하기 때문에

최소성은 만족하지 못하게 된다는 소리이다.

 

 

 

---

5. 테이블 생성

 

- CREATE 문법

CREATE TABLE(Object명) 테이블명 (
    컬럼명1 데이터타입,
    컬럼명2 데이터타입,
    ...
    컬럼명n 데이터타입
) 
- 테이블명, 컬럼명은 대소문자를 구분하지 않음 ( 모두 대문자로 출력됨 )

- 제약조건 생성 문법

1) 컬럼레벨 방식
CREATE TABLE 테이블명(
    컬럼명1 데이터타입 CONSTRAINT 제약조건이름 제약조건명시,
    컬럼명2 데이터타입 CONSTRAINT 제약조건이름 제약조건명시,
    ...
)

2) 테이블레벨 방식
CREATE TABLE 테이블명(
    컬럼명1 데이터타입 
    컬럼명2 데이터타입
    ...
    CONSTRAINT 제약조건이름 제약조건명시 ( 컬럼명 ),
    CONSTRAINT 제약조건이름 제약조건명시 ( 컬럼명 ),
    ...
)

3) 제약 조건 없이 테이블 생성 후 제약 조건 추가

- NOT NULL 과 DEFAULT는 컬럼레벨 방식에서만 가능
- 컬럼레벨 방식과 테이블레벨 방식은 혼용이 가능

 

설명1. 테이블 만들기

 

CREATE TABLE TEST(
    COL1 VARCHAR2(30),
    COL2 NUMBER(2),
    COL3 DATE
)

 

이런식으로 쿼리를 작성해주면 된다.

 

이후에 이 테이블에 값을 한번 넣어보자 

 

INSERT INTO TEST (COL1, COL2, COL3)
VALUES ('AA', 2, '2022-02-16');

 

 

컬럼별로 내가 설정한 데이터타입으로 값을 넣으면 정상적으로 잘 들어간다.

 

INSERT INTO TEST (COL1, COL2, COL3)
VALUES ('AA', 100, '2022-02-16');

 

이 쿼리는 실행되지 않는다. 왜냐하면 COL2 컬럼은 내가 데이터타입을 NUMBER(2) 로 지정해놓았기 때문이다.

전체자릿수가 2가 넘어가는 숫자데이터는 넣을 수 없다.

 

---

설명2. 제약조건 넣어서 테이블 만들기

 

CREATE TABLE STUDENT (
    NUM NUMBER PRIMARY KEY,
    NAME VARCHAR2(30),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(30)
);

 

NUMBER 라는 컬럼을 기본키로 지정하고 싶다면 테이블명 뒤에 PRIMARY KEY라는 예약어를 넣어주면 된다.

그리고 이 NUMBER 컬럼에 중복된 값을 넣을려고 한다면

 

 

이런 오류가 뜬다. 기본키라서 중복된 값을 넣을 수 없기 때문이다.

근데 이러면, 나중에는 어디서 대체 내가 제약조건을 위배했는지 모르기 쉽상이다.

그래서 제약조건을 넣을 때는 제약조건 생성문법을 따르는 것이 좋다.

 

CREATE TABLE STUDENT (
    NUM NUMBER CONSTRAINT STUDENT_NUM_PK PRIMARY KEY,
    NAME VARCHAR2(30),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(30)
);

 

이렇게 컬럼레벨 방식을 사용하여 제약조건을 넣었다.

제약조건 생성문법에서 제약조건의 이름은 제약조건의 이름 규칙에 따라서 만드는 것이 좋다.

그래야 알아보기 편하다.

중복된 데이터를 넣어서 오류메시지를 봤더니

 

 

제약조건의 이름이 뜬다. STUDENT 테이블의 NUM 컬럼의 PK 제약조건이 위배가 되었구나 하고

한눈에 알아보기 편하다.

 

단, NOT NULL의 경우에는 제약조건 이름 명시하더라도 에러메시지에 제약조건의 이름이 나오지 않으니

그냥 선언해도 무관하다! 알아서 알려준다.

 

CREATE TABLE STUDENT (
    NUM NUMBER CONSTRAINT STUDENT_NUM_PK PRIMARY KEY,
    NAME VARCHAR2(30) NOT NULL,
    PHONE VARCHAR2(20) CONSTRAINT STUDENT_PHONE_U UNIQUE,
    EMAIL VARCHAR2(30) CONSTRAINT STUDENT_EMIAL_U UNIQUE
);

 

최종적으로 이렇게 테이블을 완성시켰다. 

그리고 제약조건을 조회하는 쿼리문을 실행시켜서 스크립트를 한번 보았다.

 

 

이렇게 잘 들어가 있는 것을 확인할 수가 있다. 

NOT NULL의 경우는 이름을 명시하지 않았기 때문에 자동으로 생성된 이름이 들어가 있게 된다.

(에러메시지는 NOT NULL의 경우만 알아서 자동으로 잘 표시해준다.)

 

 

 

---

3. 제약조건 CHECK

 

학생들의 점수를 넣는 테이블을 만들어보자.

 

CREATE TABLE POINT (
    NUM NUMBER(9),
    NAME VARCHAR2(30) CONSTRAINT POINT_NAME_NN NOT NULL ,
    KOR NUMBER(3),
    ENG NUMBER(3),
    MATH NUMBER(3),
    TOTAL NUMBER(3),
    MEAN NUMBER(20,18),
    CONSTRAINT POINT_NUM_PK PRIMARY KEY (NUM)
);

 

NUM 컬럼을 기본키로 지정해놓았고, NAME 컬럼은 NULL값이 들어오지 못하도록 제약조건을 넣었다.

근데 이 테이블에 값을 INSERT할때, 국어점수에 음의 정수가 들어가도 들어가게 된다.

근데 상식적으로 점수가 음의 정수가 가능한가? 불가능하다.

이럴 때 CHECK를 통해 들어갈 데이터를 제한해주면 된다.

 

CREATE TABLE POINT (
    NUM NUMBER(9),
    NAME VARCHAR2(30) CONSTRAINT POINT_NAME_NN NOT NULL ,
    KOR NUMBER(3),
    ENG NUMBER(3),
    MATH NUMBER(3),
    TOTAL NUMBER(3),
    MEAN NUMBER(20,18),
    CONSTRAINT POINT_NUM_PK PRIMARY KEY (NUM),
    CONSTRAINT POINT_KOR_C CHECK (KOR BETWEEN 0 AND 100),
    CONSTRAINT POINT_ENG_C CHECK (ENG BETWEEN 0 AND 100),
    CONSTRAINT POINT_MATH_C CHECK (MATH BETWEEN 0 AND 100)
);

 

이런식으로 CHECK 제약조건을 활용해주면 된다.

 

---

설명4. 제약조건 DEFAULT

 

    MEAN NUMBER(20,18) DEFAULT 0.00

 

DEFAULT의 사용법은 간단하다. 따로 제약조건의 이름을 명시할 필요도 없고,

데이터 타입 뒤에 DEFAULT 기본값 을 쿼리로 작성해주면 끝이다.

 

단, DEFAULT값이 적용될 경우는 딱 한가지이다.

 

INSERT INTO POINT (NUM, NAME, KOR, ENG, MATH, TOTAL)
VALUES (4, 2, 99, 99, 99, 99);

 

위처럼, INSERT문에 컬럼을 나열해서 INSERT문을 사용했을 때,

근데 MEAN 컬럼은 빠져있는 것을 볼 수 있다. 

이럴 때에만 DEFAULT 값인 0.0이 삽입된다.

 

INSERT문에 컬럼명을 나열하지 않고 VALUES만 나열해서 사용할 경우나, 

아니면 MEAN의 값에 NULL을 넣는 경우에는 DEFAULT값이 들어가지 않는다. 

오로지 위의 경우에만 들어간다.

 

---

설명5. 테이블 연결하기 (FOREIGN KEY)

 

지금까지 예제로 들었던 STUDENT 테이블과 POINT 테이블을 연결해보자.

우선 연결하기 위해 POINT 테이블에서 해야 할 작업이 있다.

 

 

현재 테이블에 이런식으로 데이터가 들어있다고 해보자.

근데 만약에 1번 학생이 기말고사를 봐서 점수를 추가해야 한다고 해보자.

근데 POINT 테이블의 NUM은 기본키라 중복불가인데다가, NAME은 Unique라 중복불가라 값을 넣을 수가 없다.

설령 기본키와 Unique 제약조건을 해제한다고 한들 다른 문제가 생긴다.

어떤 문제냐 하면, 아까 설명했던 문제점인데

 

 

설령 이렇게 데이터를 넣었다고 한들

만약에 마지막에 넣었던 데이터가 잘못되어서 업데이트를 해야한다던가, 지워야 되는 상황이라면

고유한 값이 없기 때문에 첫번째 데이터도 같이지워져버리는 불상사가 생겨버린다.

DELETE POINT WHERE NUM = 1; 과 같은 쿼리를 시키면, 나의 의도는 마지막 데이터만 지워지겠지 겠지만,

첫번째 데이터도 지워진다는 점이 문제점이다. 그래서 꼭 데이터는 고유의 번호를 지니는 기본키가 있어야 한다.

그래서 기본키를 새로 다른 컬럼에 만들어 주었다.

 

CREATE TABLE POINT (
    NO NUMBER CONSTRAINT POINT_NO_PK PRIMARY KEY,
    NUM NUMBER,
    KOR NUMBER(3),
    ENG NUMBER(3),
    MATH NUMBER(3),
    TOTAL NUMBER(3),
    MEAN NUMBER(20,18) DEFAULT 0.00
);

 

이렇게 NO 컬럼을 추가해서 이 컬럼을 기본키로 지정해주었다.

그리고 NAME 컬럼은 어차피 STUDENT 테이블에 값이 있기 때문에 삭제해주었다.

그럼 NUM컬럼은 왜 삭제 안했느냐? 이는 STUDENT테이블과 연결시키기 위해서이다.

여기서 NUM이라는 컬럼은 결국 STUDENT 테이블의 NUM컬럼과 동일한 값이다.

만약에 위에 1, 2번 학생만이 있는 상황에서 POINT 테이블에 아래와 같은 INSERT문을 실행시킨다고 해보자

 

INSERT INTO POINT (NO, NUM, KOR, ENG, MATH, TOTAL, MEAN)
VALUES (3, 3, 30, 30, 30, 90, 30);

 

이러면, POINT 테이블의 NUM 값에 3번 학생이 들어가게 되는데, 문제는 3번학생이 없다.

이런 데이터들은 이상한 데이터들이다. 

이 데이터가 들어가기 위해서는, 이미 STUDENTS 테이블에 학생번호가 3번인 학생이 있어야 가능하다.

이런 데이터를 삽입하지 못하게 하는 제약조건이 FOREIGN KEY이다. 

POINT 테이블의 NUM 컬럼이 STUDENT테이블의 NUM을 참조하게 만듬으로써, 

STUDENT 테이블의 NUM 컬럼에 없는 값이 들어올 경우 못들어오게 막아버리는 것이다.

FOREIGN KEY를 지정하는 방법은 아래와 같다.

 

    NUM NUMBER CONSTRAINT POINT_NUM_FK REFERENCES STUDENT (NUM)

 

REFERENCES 참조할컬럼이있는테이블명 (참조할컬럼명) 을 통해

STUDENT 테이블의 NUM컬럼을 이제 POINT테이블의 NUM컬럼이 참조하게 되었다.

이제부터는 STUDENT 테이블의 NUM 컬럼에 없는 값이라면, POINT 테이블의 NUM 컬럼에 들어갈 수 없다.

만약 참조할컬럼명의 경우에는 지금같은경우 이름이 같기 때문에 생략도 가능하다.

STUDENT 테이블에도 컬럼명이 NUM이고, POINT테이블도 컬럼명이 NUM이기 때문에

맨 뒤에 (NUM)을 안 써도 된다는 소리이다.

 

만약에 테이블레벨 방식으로 외래키를 지정한다면, 조금 문법이 다르다

 

CONSTRAINT POINT_NUM_FK FOREIGN KEY (NUM) REFERENCES STUDENT (NUM)

 

컬럼레벨의 경우에는 컬럼 바로 옆에 작성하기 때문에 어느 컬럼이 참조하는지를 이미 명시하고 있는데,

테이블레벨의 경우는 어느 컬럼이 참조할 것인지를 모르기때문에 FOREIGN KEY를 써주고 

FOREIGN KEY로 쓸려는 컬럼명을 ( ) 안에 작성해주고 난 뒤에 REFERENCES 문을 사용해주어야 한다.

 

여기서 참조하는 쪽(POINT)은 자식테이블, 참조를 주는쪽(STUDENT)은 부모테이블이라고 한다.

 

단, 참조키로 설정할 때에는 부모테이블로 설정하는 컬럼이 Unique거나, Primary Key여야 한다.

즉, 데이터가 중복불가인 컬럼만 외래키로 지정할 수 있다.

 

---

설명6. FOREIGN KEY 삭제

 

STUDENT 테이블의 NUM 컬럼을 POINT테이블의 NUM컬럼이 참조하게 만들어 놓은 다음,

STUDENT 테이블을 삭제하려고 보니 에러메시지가 뜬다.

 

 

POINT 테이블의 NUM 컬럼이 STUDENT 테이블의 NUM 컬럼을 참조하고 있으니 

삭제가 안되는 것이다. STUDENT 테이블을 삭제해버리면, 

POINT 테이블의 NUM 컬럼의 값들은 이상한 데이터가 되어버리기 때문이다. 

학생이 없는데 그러면 대체 누구의 점수냐는 말이다.

 

그래서 항상 자식을 먼저 지우고 부모를 지워야 가능하다. 

근데 자식을 먼저 지울 수 있는 조건도 있다.

 

제약조건을 명시할 때 뒤에 ON DELETE CASCADE 를 사용한 후에

나중에 부모테이블에서 컬럼의 ROW값을 삭제한다면, 알아서 자동으로 자식테이블의 ROW값들이 삭제된다.

ON DELETE SET NULL 를 사용하면 자동으로 NULL값을 넣어준다.

 

---

6. 테이블 삭제

 

1. DROP
- 테이블(Object) 자체를 삭제

- 문법
DROP TABLE(Object명) 테이블명

 

---

7. TRUNCATE

 

- Table을 삭제하는 것이 아니라 모든 ROW(Tuple)을 삭제
- Table의 구조는 유지
- TRUNCATE TABLE 테이블명

 

설명1. 데이터를 삭제하는 3가지 방법의 차이

 

1. DROP : 구조와 데이터 모두 삭제

2. TRUNCATE : 구조 유지, 데이터 삭제

3. DELETE : 구조 유지, 데이터 삭제, 데이터가 있던 칸도 유지 (ex. 3개의 ROW가 있었다고 한다면 3칸을 유지함)

 

---

설명2. RecycleBin

 

DB에서 삭제된 Table은 RecycleBin으로 이동된다.

혹시라도 잘못지웠을 경우에 복구할 수 있게끔하는 안전장치이다.

RecycleBin을 사용하는 명령어는 다음과 같다.

 

SELECT * FROM RECYCLEBIN -- 휴지통 조회
PURGE RECYCLEBIN -- 휴지통 비우기(삭제), 복원X
DROP TABLE 테이블명 PURGE -- 휴지통에 저장하지 않고 바로 삭제
FLASHBACK TABLE 테이블명 TO BEFORE DROP -- 휴지통에서 복원