21.2.9(화) DB/DDL(CREATE)(2)

(6) 제약조건

(2) UNIQUE (중복 제약)

: 컬럼에 중복값을 제한하는 제약조건
삽입 / 수정시 기존에 해당 컬럼값 중에 중복값이 있을 경우 추가 또는 수정이 안되게끔

[표현법]

• 컬럼레벨
  : CREATE 테이블 테이블명(
  컬럼 1 UNIQUE,
  컬럼 2,
  컬럼 3,....
  )

<예시>
CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,  => 컬럼에다가 UNIQUE 제약조건 부여 
    MEM_PWD VARCHAR2(20) NOT NULL,

• 테이블레벨
  : CREATE 테이블 테이블명(
  컬럼 1,
  컬럼 2,
  컬럼 3,....
  UNIQUE (제약조건 추가할 컬럼1, 제약조건 추가할 컬럼2)
  )

<예시>
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) -- 테이블레벨 방식
);

(3) 제약조건명 부여하기

: 제약조건에 위배되었을 때 제약조건명이 뜨게됨!

• <예시>
  (1) UNIQUE 제약조건에 위배되었으므로 INSERT 실패!
  -> (2) 오류 구문을 제약조건명으로 알려줌
  (DDL.SYS_C007139으로 알려주고 컬럼명으로 알려주지 않음)
  => 뭐가 문제인지 쉽게 파악하기 어려움
  -> (3) 제약조건 부여시 직접 제약조건명(DDL.SYS_C007139)을 지정해주지 않으면 시스템에서 알아서 임의의 제약조건명 부여해줌 SYS_C~~~

[표현법]
제약조건 부여시 제약조건명도 지정하는 표현식

• 컬럼레벨 방식(일일이 하나씩)
  => 제약조건을 컬럼레벨방식으로 지정 CREATE TABLE 테이블명 (
  컬럼명1 자료형1 (CONSTRAINT 제약조건명) 제약조건(NOT NULL, UNIQUE...),
  컬럼명2 자료형2 (CONSTRAINT 제약조건명) 제약조건(NOT NULL, UNIQUE...),
  ....
  );

• 테이블레벨 방식(나중에 한방에)
  CREATE TABLE 테이블명(
  컬럼명1 자료형1,
  컬럼명2 자료형2,
  ....
  (CONSTRAINT 제약조건명) 제약조건 (컬럼명)
  )

  => 제약조건시, CONSTRAINT 제약조건명(예시 > DDL.SYS_C007139) 을 붙여줘야함!

<예시>
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) --<테이블레벨 방식>
);

(4) CHECK

: 컬럼에 기록될 수 있는 값에 대한 조건을 설정 해 둘 수 있음!

[표현법]
CHECK 조건식

<예시>
CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR(20) NOT NULL,
    MEM_NAME VARCHAR(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')), --> <남 또는 여 만 입력해야함>
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE NOT NULL
);

(5) DEFAULT (제약조건 아님!)

: 특정 컬럼에 들어올 값에 대한 설정 가능

[표현법]
컬럼명 자료형 DEFAULT 기본값

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR(20) NOT NULL,
    MEM_NAME VARCHAR(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL --> <SYSDATE로 기본값 설정하겠다>
);

INSERT INTO MEM_CHECK(MEM_NO, MEM_ID, MEM_PWD, MEM_NAME)
VALUES(1, 'user01', 'pass01', '강개똥'); 

--> 여기에 지정되지 않은 컬럼에는 기본적으로 null 값이 들어가지만,
<만일 default 값이 부여되어 있다면 null값이 아닌 default값으로 들어감!!>

(6) PRIMARY KEY

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

단, 한 테이블당 한 개만 설정 가능

※ 하지만 컬럼을 하나로 묶어서 PRIMARY KEY 하나로 설정하는건 가능 -> 복합키)

예시> PRIMARY KEY (MEM_NO, MEM_ID)

<예시>
CREATE TABLE MEM_PRIMARYKEY1(
    MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY, -- 컬럼레벨 방식
    MEM_ID VARCHAR(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR(20) NOT NULL,
    MEM_NAME VARCHAR(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    -- CONSTRAINT MEM_PK PRIMARY KEY (MEM_NO) -- 테이블레벨 방식
);

PRIMARY KEY 제약조건은 NULL or 중복값(UNIQUE) 들어가면 안 됨!!

<컬럼 묶어서 PRIMARY KEY 지정>
CREATE TABLE MEM_PRIMARYKEY2(
    MEM_NO NUMBER PRIMARY KEY, -- 컬럼레벨 방식
    MEM_ID VARCHAR(20) NOT NULL,
    MEM_PWD VARCHAR(20) NOT NULL,
    MEM_NAME VARCHAR(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 컬럼의 중복값이 각각 하나만 중복될때는 중복으로 인식하지 않게됨!! 
(AND 개념임, OR 개념이 아님)
예를들면, MEM_NO 컬럼은 중복이지만 MEM_ID가 중복이 아닐경우에는 제약조건에 걸리지 않아 정상적으로 출력됨.

그러므로 잘 쓰이지는 않음!

=> 대신에 NULL값은 바로 안 됨!

(7) FOREIGN KEY(외래제약키)

: 해당 컬럼에 다른 테이블에 존재하는 값만 들어와야되는 컬럼에 부여하는 제약조건
--> 다른 테이블을 참조한다고 표현
즉, 참조된 다른 테이블(부모테이블)이 제공하고 있는 값만 들어올 수 있다.
--> FOREIGN KEY 제약조건으로 다른 테이블 간의 관계를 형성할 수 있음

• 컬럼레벨방식
  컬럼명 자료형 (CONSTRAINT 제약조건명) REFERENCES 참조할 테이블명(참조할 컬럼명)

• 테이블레벨방식
  (CONSTRAINT 제약조건명) 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), -- 컬럼레벨방식 => (GRADE_CODE) 생략도 가능함
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
    -- FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) -- 테이블레벨방식
);

- 외래키 제약조건 오류발생 예시 -
<NULL: 외래키 제약조건이 걸려있는 컬럼에는 기본적으로 NULL값이 불가>
<부모컬럼존재X: 외래키 제약조건이 있는 컬럼에서 제공하는 값이 없을경우 생성불가 -parent key not found>

※ 응용
다른 테이블(부모테이블)로부터 하나의 컬럼 데이터를 삭제한다면?
자식테이블 생성시 (외래키 제약조건 부여시)
부모테이블의 데이터가 삭제됐을 때 자식테이블에는 어떻게 처리할 지를 옵션으로 정해놓을 수 있음

<예시>
CREATE TABLE MEM(
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE), -- 컬럼레벨방식 => (GRADE_CODE) 생략도 가능함
=> MEM(자식) 테이블에 MEM_GRADE(부모) 테이블을 참조함  

MEM_GRADE (부모테이블) 테이블의 GRADE_CODE가 G1인 데이터 지우기
DELETE FROM MEM_GRADE 
WHERE GRADE_CODE = 'G1'; 삭제 안 됨
--> 자식테이블 중에 한 컬럼을 사용하고 있기 때문에 삭제할 수 없음!
    (만약 참조하고 있는 값이 없다면 위 같은 방법으로 삭제는 가능함.)
--> 현재 외래키 제약조건 부여시 삭제옵션을 부여 안 했음!
--> 자식테이블에서 사용하고 있는 값이 있을 경우 삭제가 안되는 <"삭제제한옵션"> 걸려있음!

=> 이 때 사용하는 제약조건옵션

(8) FOREIGN KEY 삭제옵션

: 외래키 테이블에 컬럼 삭제옵션
=> 삭제 옵션을 별도로 제시하지 않으면 ON DELETE RESTRICTED(삭제제한)으로 기본적으로 지정

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

[표현법]
FOREIGN KEY(컬럼) REFERENCES 참조할 테이블(참조할 컬럼) ON DELETE SET 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
  : 부모데이터 삭제시 해당데이터를 사용하고 있는 자식데이터도 같이 삭제해버리는

[표현법]
FOREIGN KEY(컬럼) REFERENCES 참조할 테이블(참조할 컬럼) 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 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 CASCADE
);