[KH정보교육원] 21일차_DDL(CREATE)

2021.11.9(화)

🌿 제약조건(constraints)

• 원하는 데이터값만을 유지/보관하기 위해 특정 컬럼마다 설정하는 제약
• 제약조건이 부여된 컬럼에 들어올 데이터에 문제가 없는지 자동으로 검사 → 데이터 무결성 보장
• 컬럼에 제약 조건을 부여하는 방식
  • 컬럼 레벨: 제약조건을 부여하고자 하는 컬럼 뒤에 기술 → 컬럼명 자료형 (CONSTRAINT 제약조건명) 제약조건
  • 테이블 레벨

CREATE TABLE MEM_CON_NM(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) CONSTRAINT MEM_NAME_NN NOT NULL, -- 컬럼레벨 방식 제약조건
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    CONSTRAINT MEM_ID_UQ UNIQUE (MEM_ID) -- 테이블레벨 방식 제약조건
);

🌱 NOT NULL

• 해당 컬럼에 반드시 값이 존재해야만 할 경우 사용(OTHERWISE 유효하지 않음)
• NULL값이 절대 들어와서는 안 되는 컬럼
• 삽입/수정 시 NULL값을 허용하지 않도록 제한
• 단, NOT NULL 제약조건은 컬럼레벨 방식만 가능

🌱 UNIQUE

• 컬럼에 중복값을 제한하는 제약조건
• 삽입/수정 시 해당 컬럼값 중 중복값이 있을 경우 추가/수정이 되지 않도록 제약
• 컬럼레벨, 테이블레벨 방식 둘 다 가능

🌱 CHECK

• 컬럼에 기록될 수 있는 값에 대한 조건을 (1개 이상~) 설정
• CHECK (컬럼명 조건식)

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, -- 컬럼레벨 방식 제약조건 여러 개 나열 시 특정 순서가 있는 것은 아님
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE NOT NULL
);

🌱 PRIMARY KEY 제약조건

• 기본키
• 테이블에서 각 행들의 정보를 유일하게 식별할 수 있는 컬럼에 부여하는 제약조건
• 각 행들을 구분할 수 있는 식별자 역할
• 중복되지 않고 값이 존재해야만 하는 컬럼에 PRIMARY KEY(UNIQUE + NOT NULL) 부여
  e.g. 회원번호, 주문번호, 사번, 학번, 예약번호 등

CREATE TABLE MEM_PRIMARYKEY2( -- PRIMARY KEYS 2개 만들어봄
-- MEM_NO NUMBER PRIMARY KEY,
-- MEM_ID VARCHAR2(20) PRIMARY KEY,
-- PRIMARY KEY가 한 테이블에 2개가 될 수 없음; ORA-02260: table can have only one primary key -> 두 컬럼을 하나로 묶어서 PRIMRARY KEY 하나로 설정 가능함
    MEM_NO NUMBER,
    MEM_ID VARCHAR2(20),
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    PRIMARY KEY (MEM_NO, MEM_ID) -- 컬럼을 묶어서 -> 단, MEM_NO와 MEM_ID 둘 다 중복될 때만 입력 오류 + 하나라도 NULL값이 입력될 때는 입력 오류
);

🌱 FOREIGN KEY 제약조건

• 다른 테이블(FOREIGN, 외래, 내 테이블이 아닌; 부모테이블)에 존재하는 값만 해당 컬럼에 들어와야 할 때 부여하는 제약조건
• 다른 테이블(부모테이블)을 참조
• 참조된 다른 테이블이 제공하는 값만 들어올 수 있음 → FOREIGN KEY 제약조건으로 다른 테이블 간의 관계 형성 가능
• 표현법: 아래 두 방식 모두 참조할 컬럼명은 생략 가능 → 생략 시 기본적으로 참조할 테이블의 PRIMARY KEY 컬럼으로 참조할 컬럼명이 잡힘
  • 컬럼레벨 방식: 컬럼명 자료형 CONSTRAINT 제약조건명 REFERENCES 참조할 테이블명(참조할 컬럼명)
  • 테이블레벨 방식: CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명)) REFERENCES 참조할 테이블명(참조할 컬럼명)

-- 자식테이블 만들기
CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE), -- 컬럼레벨 방식
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
    -- FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) -- 테이블 레벨 방식
);

• 자식테이블 생성 시/외래키 제약조건 부여 시, 부모테이블의 데이터가 삭제되었을 때 자식테이블에는 어떻게 처리할지 옵션으로 정할 수 있음 = FOREIGN KEY 삭제 옵션
  • 삭제 옵션을 별도로 지정하지 않으면 ON DELETE RESTRICTED(삭제 제한)로 기본 설정
  • ON DELETE SET NULL: 부모데이터 삭제 시 해당 데이터를 사용하고 있는 자식데이터를 NULL로 변경시키는 옵션
  • ON DELETE CASCADE: 부모데이터 삭제 시 해당 데이터를 사용하고 있는 데이터도 같이 삭제해버리는 옵션

🌿 트랜잭션(Transaction)

• 데이터베이스의 하나의 논리적 연산/작업 단위

📗 소감

• 데이터베이스에 테이블 같은 객체 생성하는 것을 배웠다. 신기하다!
• 제약 조건이 관계형 데이터베이스에 어떻게 중요한지 이해하자

📗 homework:

-- 2021.11.9(화) 숙제
-- 1번 문제: 부서별 급여 합계가 전체 급여 총 합의 20%보다 많은 부서의 부서명, 부서별 급여 합계 조회
SELECT DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) > 0.2 * (SELECT SUM(SALARY)
                            FROM EMPLOYEE); -- 서브쿼리의 결과 = 70096240

-- 2번 문제: 보너스 포함한 연봉이 높은 5명의 사번, 이름, 부서명, 직급, 입사일, 순위 조회
SELECT *
FROM (SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME, HIRE_DATE, RANK() OVER(ORDER BY SALARY * (1 + NVL(BONUS, 0)) * 12 DESC) "연봉 순위"
      FROM EMPLOYEE E, DEPARTMENT D, JOB J
      WHERE E.DEPT_CODE = D.DEPT_ID(+)
       AND E.JOB_CODE = J.JOB_CODE(+))
WHERE "연봉 순위" <= 5;

-- 3번 문제: 보너스가 없고 직급 코드가 J4이거나 J7인 사원의 이름, 직급, 급여 조회
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE BONUS IS NULL
 AND JOB_CODE IN ('J4', 'J7');

-- 4번 문제: 계열정보를 저장할 카테고리 테이블을 만들기
CREATE TABLE TB_CATEGORY(
    NAME VARCHAR2(10),
    USE_YN CHAR(1) DEFAULT 'Y' NOT NULL -- DEFAULT 기입한 컬럼은 입력 안 하면 DEFAULT로 지정한 값이 입력됨
);

SELECT * FROM TB_CATEGORY;

-- 5번 문제: 과목 구분을 저장할 테이블 만들기
CREATE TABLE TB_CLASS_TYPE(
    NO VARCHAR2(5) PRIMARY KEY,
    NAME VARCHAR2(10)
);

SELECT * FROM TB_CLASS_TYPE;