[04-1] Oracle DDL (CREATE) 제약조건, 서브쿼리(SUBQUERY)

제약조건 CONSTRAINTS

- 종류 : NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY - 원하는 데이터 값만을 유지하기 위해 (보관하기 위해) 특정 컬럼마다 설정하는 제약   (데이터의 무결성 보장 목적) - 제약조건이 부여된 컬럼에 들어올 데이터가 문제가 있는지 없는지 자동으로 검사 - 컬럼에 제약조건을 부여하는 방식 : 컬럼레벨 / 테이블레벨

제약조건 부여 시 제약조건명 지정하는 방법

	-- 컬럼레벨 방식
    CREATE TABLE 테이블명(
        컬럼명 자료형 CONSTRAINT 제약조건명 제약조건,
        컬럼명 자료형,
        컬럼명 자료형,
        ...
    )
    
    -- 테이블레벨 방식
    CREATE TABLE 테이블명(
        컬럼명 자료형,
        컬럼명 자료형,
        컬럼명 자료형,
        ...
        CONSTRAINT 제약조건명 제약조건(컬럼명)
    )

CREATE TABLE MEM_CON_NN(
    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 제약조건  : 해당 컬럼에 반드시 값이 존재해야만 할 경우 사용 (NULL 값이 절대 들어와서는 안되는 컬럼)   삽입 / 수정시 NULL 값을 허용하지 않도록 제한 단, NOT NULL은 제약조건 컬럼레벨 방식만 가능 -- 컬럼 레벨 방식 : 컬럼명 자료형 제약조건 => 제약조건을 부여하고자 하는 컬럼 뒤에 기술

CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

INSERT INTO MEM_NOTNULL
VALUES(1, 'USER01', 'PASS01', '박머루', '여', '010-1234-5678', 'ullebulle-it.tistroy.com');

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

CREATE TABLE MEM_UNIQUE(
    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),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    UNIQUE(MEM_ID) -- 테이블레벨 방식
);

INSERT INTO MEM_UNIQUE VALUES(1, 'USER01', 'PASS01', '박머루', '여', NULL, NULL);

INSERT INTO MEM_UNIQUE VALUES(2, 'USER01', 'PASS02', '홍길동', '여', NULL, NULL);
-- unique constraint ERROR 중복

-- UNIQUE 제약조건에 위배되었으므로 INSERT 실패!
-- 오류구문으로 제약조건명 알려줌 (어떤 컬럼에 문제가 있는지 컬럼명을 알려주진 않음)
-- 제약조건을 모르면 파악하기 어려움(ORA-00001 : unique constraint)
-- 제약조건명을 지정해줄 수 있음

CHECK 제약조건  : 컬럼에 기록될 수 있는 값에 대한 조건을 설정해 둘 수 있다. CHECK(조건식)

CREATE TABLE MEM_CHECK(
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
);

PRIMARY KEY (기본키, PK) 제약조건  : 테이블에서 각 행들의 정보를 유일하게 식별할 수 있는 컬럼에 부여하는 제약조건   => 각 행들을 구분할 수 있는 식별자의 역할   예) 회원번호, 주문번호, 사번, 학번, 예약번호 => 중복되면 안되는 것들   => 중복되지 않고 값이 존재해야만 하는 컬럼에 PRIMARY KEY 부여 (UNIQUE + NOT NULL) 한 테이블 당 한 개의 컬럼만 설정 가능 -- PRIMARY KEY 가 한 테이블에 두 개가 될 수 없다 -- table can have only one primary key -- 두 컬럼을 하나로 묶어서 PRIMARY KEY 하나로 설정 가능하다!

CREATE TABLE MEM_PRIMARYKEY(
    MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY, -- 기본키
    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)
);

CREATE TABLE MEM_PRIMARYKEY2(
    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) -- 컬럼을 묶어서 PRIMARY KEY 하나로 설정 => 복합키
);

FOREIGN KEY 외래키 제약조건  : 해당 컬럼에 다른 테이블에 존재하는 값만 들어와야 될 때 부여하는 제약조건  => 다른 테이블(부모테이블)을 참조한다고 표현 즉, 참조된 다른 테이블이 제공하고 있는 값만 들어올 수 있다  => FOREIGN KEY 제약조건으로 다른 테이블간의 관계를 형성할 수 있음 [표현법]  - 컬럼레벨방식     컬럼명 자료형 CONSTRAINT 제약조건명 REFERENCES 참조할 테이블명(참조할 컬럼명)       - 테이블레벨 방식     CONSTRAIONT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 참조할 테이블명(참조할 컬럼명)          두 방식 모두 참조할 컬럼명은 생략이 가능하다!     생략 시 기본적으로 참조할 테이블의 PRIMARY KEY 컬럼으로 참조할 컬럼명이 잡힌다. -- 외래키 제약조건 부여 시 삭제옵션을 부여하지 않았음 -- 자식테이블에서 사용하고 있는 값이 있을 경우 삭제가 안되는 "삭제제한옵션"이 걸려있다

-- 자식테이블
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 삭제 옵션  : 삭제옵션을 별도로 지정하지 않으면 기본적으로 ON DELETE RESTRICTED(삭제제한)으로 설정     * 자식 테이블 생성 시(외래키 제약조건 부여 시)     부모테이블의 데이터가 삭제 되었을 때 자식테이블에서는 어떻게 처리할지 옵션으로 정할 수 있음

-- 1) ON DELETE SET NULL : 부모데이터 삭제 시 해당 데이터를 사용하고 있는 자식데이터를 NULL로 변경시키겠다.

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),
    GENDER CHAR(3) CHECK(GENDER IN('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL
);


-- 2) ON DELETE CASCADE : 부모 데이터 삭제 시 해당 데이터를 사용하고 있는 데이터도 같이 삭제해버리는 옵션

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR(2) NOT NULL,
    GRADE_ID CHAR(2),
    GENDER CHAR(3) CHECK(GENDER IN('남','여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE
);

특정 컬럼에 들어올 값에 대한 기본값 설정 => 제약조건은 아님 -- 지정 안 된 컬럼에는 기본적으로 NULL 값이 들어가지만 -- 만일 DEFAULT 값이 부여되어 있다면 NULL 값이 아닌 DEFAULT 값이 들어가게 된다.

    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
    -- 회원가입일을 항상 SYSDATE 값으로 받고 싶다!! 테이블에서 지정 가능하다

SUBQUERY 서브쿼리

메인 SQL문을 보조하는 쿼리문     [표현법]     CREATE TABLE 테이블명     AS 서브쿼리;      서브쿼리 수행결과(RESULT SET)로 테이블을 생성하겠다.

-- EMPLOYEE 테이블을 복제한 새로운 테이블 생성(EMPLOYEE_COPY)

CREATE TABLE EMPLOYEE_COPY
    AS SELECT * FROM EMPLOYEE;

SELECT * FROM EMPLOYEE_COPY; -- NOT NULL 제약조건만 복사됨


-- EMPLOYEE 테이블에 있는 컬럼의 구조만 복사하고 싶은 경우, 데이터값은 필요X
CREATE TABLE EMPLOYEE_COPY2
    AS SELECT * FROM EMPLOYEE WHERE 1 = 0; -- 조건식을 FALSE로 만들어준다


-- 전체 사원들 중 급여가 300만원 이상인 사원들의 사번, 이름, 부서코드, 급여 컬럼 복제
CREATE TABLE EMPLOYEE_COPY3
    AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
    FROM EMPLOYEE
    WHERE SALARY >= 3000000;
     

-- 전체 사원의 사번, 사번명, 급여, 연봉조회 결과 테이블 생성
CREATE TABLE EMPLOYEE_COPY04
    AS SELECT EMP_ID, EMP_NAME, SALARY, SALARY * 12 연봉
    FROM EMPLOYEE;
-- 서브쿼리의 SELECT절 산술연산 또는 함수식이 기술된 경우 별칭을 부여해야 함

테이블이 생성된 후 제약조건 추가 (ALTER TABLE 테이블명 OOO)

-- PRIMARY : ADD PRIMARY KEY(컬럼명);     -- FOREIGN : ADD FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조할컬럼명);     -- UNIQUE : ADD UNIQUE(컬럼명);     -- CHECK : ADD CHECK(컬럼에 대한 조건);     -- NOT NULL : MODIFY 컬럼명 NOT NULL;