Oracle #4 - 제약 조건

📍 제약 조건

CONSTRAINTS : 데이터 무결성 보장이 주 목적이다. 입력 데이터에 문제가 없는지 검사와 데이터의 수정/삭제 가능 여부 검사 등을 위해 사용한다.

NOT NULL

-- NOT NULL 테이블 생성

CREATE TABLE USER_CONSTRAINT (
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) NOT NULL,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30) NOT NULL,
    USER_GENDER VARCHAR2(10),
    USER_PHONE VARCHAR2(30),
    USER_EMAIL VARCHAR2(50)
);

SELECT * FROM USER_CONSTRAINT;
INSERT INTO USER_CONSTRAINT VALUES(1,'user01','pass01', '일용자', '남', '01029023849', 'user01@naver.com');
INSERT INTO USER_CONSTRAINT VALUES(1,'NULL','NULL', 'NULL', '남', '01029023849', 'user01@naver.com');

SELECT * FROM USER_NO_CONSTRAINT;
-- OPA-00942 : table or view does not exist : 테이블명 오타
-- OPA-00904 : "USER_PW" " : invalid identifier : 칼럼명 오타
-- 데이터 추가( DML : insert, update, delete, select )
INSERT INTO USER_NO_CONSTRAINT VALUES (2, 'user02', 'pass02', '이용자', '남', '01049844544', 'user02@nate.com');

UNIQUE

-- UNIQUE 테이블 생성 
-- 중복 금지

CREATE TABLE USER_UNIQUE (
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30) NOT NULL,
    USER_GENDER VARCHAR2(10),
    USER_PHONE VARCHAR2(30),
    USER_EMAIL VARCHAR2(50)
);

INSERT INTO USER_UNIQUE VALUES (1, 'user01', 'pass01', '일용자', '남', '01068546222', 'user01@kakao.com');
INSERT INTO USER_UNIQUE VALUES (1, 'user01', 'pass01', '일용자', '남', '01068546222', 'user01@kakao.com');
-- ORA-00001: unique constraint (USER01.SYS_C007002) violated
-- 동일한 INSERT를 했을 때 > 같은 값을 넣으려고 함!!! 의 오류보고
INSERT INTO USER_UNIQUE VALUES (1, null, 'pass01', '일용자', '남', '01068546222', 'user01@kakao.com');

SELECT * FROM USER_UNIQUE;

PRIMARY KEY : 기본키

-- PK

CREATE TABLE USER_PRIMARYKEY (
    USER_NO NUMBER UNIQUE,
    USER_ID VARCHAR2(20) CONSTRAINT USER_ID_PRIMARY_KEY PRIMARY KEY,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30) NOT NULL,
    USER_GENDER VARCHAR2(10),
    USER_PHONE VARCHAR2(30),
    USER_EMAIL VARCHAR2(50)
);

INSERT INTO USER_PRIMARYKEY VALUES (1, 'user01', 'pass01', '일용자', '남', '01023445678', 'user01@kakao.com');
INSERT INTO USER_PRIMARYKEY VALUES (2, 'user01', 'pass01', '일용자', '남', '01023445678', 'user01@kakao.com');
-- ORA-00001: unique constraint (USER01.SYS_C007007) violated
-- 테이블의 제약조건의 코드를 확인하면 된다. (USER01.SYS_C007007)

SELECT * FROM USER_PRIMARYKEY;

CHECK

CREATE TABLE USER_CHECK (
    USER_NO NUMBER UNIQUE,
    USER_ID VARCHAR2(20) CONSTRAINT U_ID_PRIMARY_KEY PRIMARY KEY, 
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30) NOT NULL,
    USER_GENDER VARCHAR2(10) CHECK(USER_GENDER IN('M', 'F')),
    USER_PHONE VARCHAR2(30),
    USER_EMAIL VARCHAR2(50)
);

INSERT INTO USER_CHECK VALUES (1, 'user01', 'pass01', '일용자', '남', '01023445678', 'user01@kakao.com');
-- ORA-02290: check constraint (USER01.SYS_C007021) violated
-- '남' 안됨!! 테이블 생성 때 IN('M', 'F')를 줬음!!
INSERT INTO USER_CHECK VALUES (1, 'user01', 'pass01', '일용자', 'M', '01023445678', 'user01@kakao.com');

SELECT * FROM USER_CHECK;

DEFAULT

-- DEFAULT 제약조건

CREATE TABLE USER_DEFAULT (
    USER_NO NUMBER UNIQUE,
    USER_ID VARCHAR2(20) PRIMARY KEY, -- 한글(3Byte)로 6글자
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30) NOT NULL,
    USER_GENDER VARCHAR2(10) CHECK(USER_GENDER IN('M', 'F')),
    USER_PHONE VARCHAR2(30),
    USER_EMAIL VARCHAR2(50),
    USER_DATE DATE DEFAULT SYSDATE -- SYSDATE : 오늘 날짜
);

INSERT INTO USER_DEFAULT VALUES (1, 'user01', 'pass01', '일용자', 'M', '01023445678', 'user01@nate.com', NULL);
-- DEFAULT : DEFAULT로 정해둔 값을 쓰겠다
INSERT INTO USER_DEFAULT VALUES (2, 'user02', 'pass02', '일용자', 'M', '01023445678', 'user01@nate.com', DEFAULT);
INSERT INTO USER_DEFAULT VALUES (3, 'user03', 'pass03', '일용자', 'M', '01023445678', 'user01@nate.com', SYSDATE);

SELECT * FROM USER_DEFAULT;

FOREIGN KEY : 외래키

-- FOREIGN KEY 외래키 제약조건
-- 참조되는 테이블 / 부모 테이블

CREATE TABLE USER_GRADE (
    GRADE_CODE NUMBER PRIMARY KEY,
    GARDE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO USER_GRADE VALUES (10, '일반회원');
INSERT INTO USER_GRADE VALUES (20, '우수회원');
INSERT INTO USER_GRADE VALUES (30, '특별회원');

SELECT * FROM USER_GRADE;

-- 참조하는 자식 테이블

CREATE TABLE USER_FOREIGNKEY (
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30) NOT NULL,
    USER_GENDER VARCHAR2(10),
    USER_PHONE VARCHAR2(30),
    USER_EMAIL VARCHAR2(50),
    GRADE_CODE NUMBER REFERENCES USER_GRADE(GRADE_CODE)
    --FOREIGN KEY (GRADE_CODE) REFERENCES USER_GRADE(GRADE_CODE)
);

INSERT INTO USER_FOREIGNKEY VALUES (1, 'user01', 'pass01', '일용자', '남', '01023445678', 'user01@nate.com', 10);
INSERT INTO USER_FOREIGNKEY VALUES (2, 'user02', 'pass02', '일용자', '남', '01023445678', 'user01@nate.com', 20);
INSERT INTO USER_FOREIGNKEY VALUES (3, 'user03', 'pass03', '일용자', '남', '01023445678', 'user01@nate.com', 30);

INSERT INTO USER_FOREIGNKEY VALUES (4, 'user04', 'pass04', '일용자', '남', '01023445678', 'user01@nate.com', 40);
-- FOREIGNKEY 오류 : 부모키 없음! (부모키의 값만 넣을 수 있음. 만약 넣고싶다면 부모키부터 INSERT 해줘야함)
-- ORA-02291: integrity constraint (USER01.SYS_C007053) violated - parent key not found

SELECT * FROM USER_FOREIGNKEY;

-- 부모 테이블(USER_GRADE)

INSERT INTO USER_GRADE VALUES (40, 'VIP회원'); -- 아래에 FOREIGNKEY 쓰려면 필요함

FOREIGN KEY - 삭제 옵션

기본 옵션 : ON DELETE RESTRICTED

-- 부모 테이블(USER_GRADE)의 데이터를 삭제하려고 할 때

DELETE FROM USER_GRADE WHERE GRADE_CODE = 40;
-- ORA-02292: integrity constraint (USER01.SYS_C007053) violated - child record found
-- 자식 있어서 못지움 (자식을 지우고 부모를 지우면 지워진다.)

CREATE TABLE USER_FOREIGNKEY(
    ...
    GRADE_CODE NUMBER REFERENCES USER_GRADE(GRADE_CODE) ON DELETE RESTRICTED
);

ON DELETE RESTRICTED는 기본 옵션으로, 참조된 자식이 있을 경우, 부모 테이블이 삭제되지 않는다.

CASCADE

CREATE TABLE USER_FOREIGNKEY(
    ...
    GRADE_CODE NUMBER REFERENCES USER_GRADE(GRADE_CODE) ON DELETE CASCADE
);

ON DELETE CASCADE는 참조된 자식이 있어도, 부모와 자식 모두에게서 연관된 모든 것을 삭제하는 옵션이다.

SET NULL

CREATE TABLE USER_FOREIGNKEY(
    ...
    GRADE_CODE NUMBER REFERENCES USER_GRADE(GRADE_CODE) ON DELETE SET NULL
);

ON DELETE SET NULL은 부모가 삭제될 시, 자식의 해당 값은 NULL로 만들어주는 옵션이다.