10. 오라클 - 제약조건

. NOT NULL : NULL을 허용안함
. CHECK : 조건에 맞는 값만을 허용
. UNIQUE : 중복된 값 허용안함
. PRIMARY KEY : 각행을 유일하게 식별하는 값
. FOREIGN KEY : 값이 참조하고 있는 테이블의 PRIMARY KEY 내에 존재한다고 보장


예)
CREATE TABLE student(
                                    stuid CHAR(5) CONSTRAINT student_stu_id_pk  PRIMARY KEY
                                    CONSTRAINT student_stu_id_ck CHECK (LENGTH(TRIM(' ' FROM stu_id ))=5),
                                    stu_name VARCHAR(10) CONSTRAINT student_stu_name_nn NOT NULL ;

예)
CREATE TABLE score(
                                 stu_id CHAR(5) CONSTRAINT score_stu_id_nn NOT NULL,
                                 std_id NUMBER(5) CONSTRAINT score_std_id_nn NOT NULL,
                                 score NUMBER(3) CONSTRAINT score_nn NOT NULL,
                                 grade CHAR(1) CONSTRAINT score_grade_ck CHECK(grade IN('A','B','C'));
                                 CONSTRAINT score_pk PRIMARY KEY(stu_id, stb_id),
                                 CONSTRAINT score_fk FOREIGN KEY(stu_id) REFERENCES student(stu_id) ON DELETE CASCADE);
// ON DELETE CASCADE 는 student 의 stu_id 가 삭제되면 같이 삭제되어라. (무결성)


제약조건 비활성화
ALTER TABLE table DISABLE CONSTRAINT constraint_name;
예)
ALTER TABLE score DISABLE CONSTRAINT score_pk;
ALTER TABLE score DISABLE CONSTRAINT PRIMARY KEY;


제약조건 활성화
DISABLE -> ENABLE 로 바꾸면 된다.


EXCEPTION
**ENABLE 시 무결성 조약이 안되어 ENABLE이 안될 때 EXCEPTION 이용
EXCEPTION 테이블 필요
CREATE TABLE exceptions (
                                       row_id ROWID,
                                       owner VARCHAR2(30),
                                       table_name VARCHAR2(30);
                                       constraint VARCHAR(30));
**ALTER TABLE student ENABLE PRIMARY KEY EXCEPTION INTO exceptions;
**SELECT * FROM WHERE rowid IN(SELECT row_id FROM exptions);





제약조건 추가
ALTER TABLE table ADD CONSTRAINT ~~
예) ALTER TABLE score ADD ( CONSTRAINT score_score_ck CHECK (score > -1), CONSTRAINT score_score_ck2 CHECK(score < 10) );

제약조건 삭제
ALTER TABLE table DROP CONSTRAINT constraint_name ;
예) ALTER TABLE score DROP PRIMARY KEY;
     ALTER TABLE socre DROP UNIQUE(std_id) CASCADE


제약조건 관련 DICTIONARY
USER_CONSTRAINTS 테이블에 제약조건 존재