제약 조건 정리

-- 무결성 제약조건
-- PRIMARY KEY : 기본키. NULL을 허용하지 않음, 중복을 허용하지 않음
           -- EX) 주민번호, ID
-- UNIQUE KEY : 고유키. NULL을 허용, 중복을 허용하지 않음.
           -- EX) E-mail
-- CHECK
-- FOREIGN KEY : 외래키. 다른 테이블에서 PRIMARY KEY인 경우 자주 있음
                -- NULL 허용
-- NOT NULL
 
-- NOT NULL
CREATE TABLE TB_TEST(
    COL1 VARCHAR2(10) NOT NULL,
    COL2 VARCHAR2(20)
);
 
INSERT INTO TB_TEST( COL1,COL2 ) 
VALUES ('AAA','aaa');
 
INSERT INTO TB_TEST( COL1,COL2 ) 
VALUES ('AAA','');
 
-- COL1은 NULL을 허용하지 않음
INSERT INTO TB_TEST( COL1,COL2 ) 
VALUES ('','aaa');
 
SELECT * FROM TB_TEST;
 
-- PRIMARY KEY = NOT NULL + UNIQUE
-- 유일하게 식별할 수 있는 정의된 규칙
-- 최대 32개 컬럼까지 지정 가능 -- 2개
CREATE TABLE TEST_01(   -- CONSTRAINT (무결성 제약 조건)
    KEY_01 VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY,
    KEY_02 VARCHAR2(10),
    COL_01 VARCHAR2(20),
    COL_02 VARCHAR2(20)
);
 
INSERT INTO TEST_01(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('AAA','aaa','111','111');
 
INSERT INTO TEST_01(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('','aaa','111','111'); -- not null 에러(PRIMARY KEY)
 
INSERT INTO TEST_01(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('AAA','aaa','111','111'); -- 중복 에러(PRIMARY KEY)
 
INSERT INTO TEST_01(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('aaa','aaa','111','111');
 
DROP TABLE TEST_01
CASCADE CONSTRAINTS;    -- 무결성까지 삭제
 
-- 만들수 없다 (PK가 두개)
CREATE TABLE TEST_01 (
    KEY_01 VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY,
    KEY_02 VARCHAR2(10) CONSTRAINT PK_TEST_02 PRIMARY KEY,
    COL_01 VARCHAR2(20),
    COL_02 VARCHAR2(20)
);
 
CREATE TABLE TEST_01 (
    KEY_01 VARCHAR2(10),
    KEY_02 VARCHAR2(10),
    COL_01 VARCHAR2(20),
    COL_02 VARCHAR2(20),
    CONSTRAINT PK_TEST_01 PRIMARY KEY(KEY_01,KEY_02)
    USING INDEX TABLESPACE USERS
);
 
INSERT INTO TEST_01(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('AAA','aaa','111','');
 
INSERT INTO TEST_01(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('AAA','','111',''); -- 안됨
 
INSERT INTO TEST_01(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('AAA','AAA','111',''); -- 됨
 
 
DROP TABLE TEST_01
CASCADE CONSTRAINTS;
 
CREATE TABLE TEST_01 (
    KEY_01 VARCHAR2(10),
    KEY_02 VARCHAR2(10),
    COL_01 VARCHAR2(20),
    COL_02 VARCHAR2(20)
);
 
-- PRIMARY KEY 추가
ALTER TABLE TEST_01 
ADD
CONSTRAINT PK_TEST_01
PRIMARY KEY (KEY_01,KEY_02);
 
-- PRIMARY KEY 삭제
ALTER TABLE TEST_01
DROP
CONSTRAINT PK_TEST_01;
 
-- PRIMARTY KEY 확인 QUERY
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'TEST_01'
    AND CONSTRAINT_TYPE = 'P';
    
-- UNIQUE KEY 고유키
-- NULL값을 제외한 유일하게 식별할 수 있는 키, 중복 허용 X
DROP TABLE TEST_01
CASCADE CONSTRAINTS;
 
CREATE TABLE TEST_01 (
    UKEY_01 VARCHAR2(10) CONSTRAINT UK_TEST_01 UNIQUE,
    KEY_01 VARCHAR2(10),
    COL_01 VARCHAR2(20),
    COL_02 VARCHAR2(20)
);
 
INSERT INTO TEST_01(UKEY_01, KEY_01,COL_01,COL_02)
VALUES ('AAA','aaa','111','111');
 
INSERT INTO TEST_01(UKEY_01, KEY_01,COL_01,COL_02)
VALUES ('AAA','aaa','111','111'); -- 에러
 
INSERT INTO TEST_01(UKEY_01, KEY_01,COL_01,COL_02)
VALUES ('','aaa','111','111'); -- NULL값 넣어짐
 
DROP TABLE TEST_01
CASCADE CONSTRAINTS;
 
-- 다중 UNIQUE 
CREATE TABLE TEST_01 (
    KEY_01 VARCHAR2(10),
    KEY_02 VARCHAR2(10),
    COL_01 VARCHAR2(20),
    COL_02 VARCHAR2(20),
    CONSTRAINT PK_TEST_01 UNIQUE(KEY_01,KEY_02)
    USING INDEX TABLESPACE USERS
);
 
-- CHECK
-- 데이터의 값이 범위, 특정한 값의 지정이 가능하다
-- NULL 허용, 중복 허용
DROP TABLE TEST_01
CASCADE CONSTRAINTS;
 
CREATE TABLE TEST_01 (
    KEY_01 VARCHAR2(10),
    KEY_02 VARCHAR2(10),
    COL_01 VARCHAR2(20),
    COL_02 NUMBER,
    CONSTRAINT CHK_TEST_01 CHECK(COL_01 IN ('남자','여자')),
    CONSTRAINT CHK_TEST_02 CHECK(COL_02 >=1 AND COL_02<=99)
);
 
INSERT INTO TEST_01(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('AAA','aaa','남자',52);
 
INSERT INTO TEST_01(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('AAA','aaa','','');
 
INSERT INTO TEST_01(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('AAA','aaa','없음',2); -- 체크안의값이 아니면 INSERT가 안됨
 
-- FOREIGN KEY
-- 다른 테이블의 기본키 이거나 고유키를 참조하는 속성 또는 속성의 집합
 
INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
            PHONE_NUMBER, HIRE_DATE, JOB_ID,SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
VALUES (300,'DOKEUN','KIM','dobbury@naver.com',
        '020.314.4534',SYSDATE,'IT_PROG',90000,0.32,100,60);
 
ROLLBACK; --되돌리기
 
COMMIT; --커밋 후에는 롤백 안됨
 
-- 부모 테이블 PRIMARY KEY
CREATE TABLE TEST_PAR(
    KEY_01 VARCHAR2(10),
    KEY_02 VARCHAR2(10),
    COL_01 VARCHAR2(20),
    COL_02 VARCHAR2(20),
    CONSTRAINT PK_TEST_PAR PRIMARY KEY(KEY_01,KEY_02)    
);
 
-- 자식 테이블 FOREIGN KEY
CREATE TABLE TEST_CHD(
    KEY_01 VARCHAR2(10) CONSTRAINT PK_TEST_CHD PRIMARY KEY,
    KEY_02 VARCHAR2(10),
    FK_KEY_01 VARCHAR2(10),   --기본키와 같은 크기로 잡아줘야 함
    FK_KEY_02 VARCHAR2(10),
    CONSTRAINT FK_TEST_CHD FOREIGN KEY(FK_KEY_01,FK_KEY_02) 
    REFERENCES TEST_PAR(KEY_01,KEY_02)
);
 
INSERT INTO TEST_PAR(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('AAA','aaa','AAA','aaa');
 
INSERT INTO TEST_PAR(KEY_01,KEY_02,COL_01,COL_02)
VALUES ('BBB','bbb','BBB','bbb');
 
INSERT INTO TEST_CHD(KEY_01,KEY_02,FK_KEY_01,FK_KEY_02)
VALUES ('111','111','','');
 
INSERT INTO TEST_CHD(KEY_01,KEY_02,FK_KEY_01,FK_KEY_02)
VALUES ('222','111','BBB','');
 
INSERT INTO TEST_CHD(KEY_01,KEY_02,FK_KEY_01,FK_KEY_02)
VALUES ('333','111','CCC','');  --FOREIGN KEY는 NULL 허용하기 때문에
 
INSERT INTO TEST_CHD(KEY_01,KEY_02,FK_KEY_01,FK_KEY_02)
VALUES ('444','111','CCC','ccc');
 
SELECT *
FROM TEST_CHD;