SQL 무결성

-- 무결성 제약 조건  
-- PRIMARY KEY : 기본키. NULL을 허용하지 않음, 중복 허용하지않음 
                          ID,주민번호 해당 , not null + 고유키(UNIQUE KEY)

-- UNIQUE KEY : 고유키. NULL 을 허용 , 중복 허용하지않음 
                        EMAIL  , null 허용 + 고유값
-- 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', ''); 

INSERT INTO TB_TEST(COL1,COL2) 
VALUES('', 'aaa'); -- 무결성 에러 -- 컬럼을 NOT NULL 로 만들어서 에러뜸 

SELECT 
    * 
FROM TB_TEST; 


-- PRIMARY KEY = NOT NULL + UNIQUE 
-- 유일하게 식별 할 수 있는 정의된 규칙  
-- 최대 32개 컬럼까지 지정 가능 -- 대부분 2개씀 
CREATE TABLE TEST_01( 
    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;  

-- 만들수 없다  
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) 
); 

-- 여러 PRIMARY KEY 만들기 
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' ,'',' '); 


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; 

-- PRIMARY KEY 확인 * 
SELECT 
    * 
FROM USER_CONSTRAINTS 
WHERE TABLE_NAME = 'TEST_01' 
        AND CONSTRAINT_TYPE = 'P'; 
         

-- UNIQUE KEY 고유키 
-- NULL 값을 제외한 유일하게 식별할 수 잇는키 
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(10) 
); 

INSERT INTO TEST_01(UKEY_01,KEY_01, COL_01) 
VALUES('AAA','aaa','111'); 

INSERT INTO TEST_01(UKEY_01,KEY_01, COL_01) 
VALUES('AAA','','111'); -- 에러 

INSERT INTO TEST_01(UKEY_01,KEY_01, COL_01) 
VALUES('','aaa','111'); 


------------------------------------------------------ 

-- 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', '', 100); 

INSERT INTO TEST_01(KEY_01, KEY_02, COL_01, COL_02) 
VALUES('AAA', 'aaa', '가나', 1); 

-- 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,'길동','홍','HGD@EMAIL','020.123.4567',SYSDATE,'IT_PROG',1000,NULL,100,60); 

SELECT 
    * 
FROM EMPLOYEES; 

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 VARCHAR(20) CONSTRAINT PK_TEST_CHD PRIMARY KEY, 
    KEY_02 VARCHAR(10), 
    FK_KEY_01 VARCHAR(10), 
    FK_KEY_02 VARCHAR(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,KET_02,COL_01,COL_02) 
VALUES('AAA','aaa','AAA','aaa');