[ ORACLE ] 무결성(Intergrity) │ PRIMARY KEY │ UNIQUE

■■■  무결성(Intergrity)  ■■■
1. 무결성에는 개체 무결성(Entity Intergrity) 
            , 참조무결성(Relational Intergrity)
            , 도메인 무결성(Domain Intergrity) 이 있다.
            
일단 제약조건 전에 제약 조건이 왜 필요한지 납득하는게 먼저라서 무결성을 이해해야 한다
무결성을 들어봤다 하는 사람 들어본 사람 무결성이 뭐에요? 

불량화소 검증이 끝난 모니터를 무결점 모니터를 비싸게 팔았다. 무결점 모니터는인증찍을 때
일반적 가격보다 10~15만원 정도 더 비쌋다. 네에 그랬었다 라는거고요 한솔 모니터 샀는데
빨간 점 나오면 ,,,, 역시 한솔꺼 쓰면 안돼 그 당시 세진컴퓨터 요즘에는 없는데
세진컴퓨터많았나 뉴텍....이런저런 중저가 모니터 컴 주변기기 용산 주변으로 퍼지기 시작했고
그 무렵에 무결점 불량화소 체크꿑난 모니터 팔았다 불량화소 모니터만 불만인게 아니라 브랜드 자체에
불만이 생기는 그런상황...관계형에서 는 테이블하나로 DB운영하는 것이 아니다. 여러 테이블들이 있는데 그 중 하나라도 잘못된 테이블이 있으면 모든 테이블 전체의 신뢰성이 의심받는다. 

SELECT *
FROM EMPLOYEES;
SELECT * 
FROM DEPARTMENT;



2. 개체 무결성
개체 무결성은 릴레이션에서 저장되는 튜플(tuple)의 유일성을 
보장하기 위한 제약조건이다. 

3. 참조 무결성
참조 무결성은 릴레이션 간의 데이터 일관성을 보장하기 위한 제약조건이다. 

4. 도메인 무결성
바캐릭터타입으로 10을 줬다 그러면 그 안에서만 입력가능
도메인 무결성은 허용 가능한 값의 범위를 지정하기 위한 제약조건이다

5. 제약조건의 종류

    - PRIMARY KEY 부모테이블의 참조받는 컬럼 ▶ 기본키.. 식별자
    해당 컬럼의 값은반드시 존재해야 하며, 유일해야 한다.   
    
    
    - FOREIGN KEY(FK , F, R) 자식테이블의 참조하는 컬럼 ▶ 외래키 외부키 참조키
    해당 컬럼의 값은 참조되는 테이블의 컬럼 데이터들 중 하나와 일치하거나 NULL을 가진다.
    
    - UNIQUE(UK:U)
    테이블내에서 해당 컬럼의 값은 항상 유일해야 한다. 
    PRIMARY의 경우 UNIQUE와 NOT NULL이 합쳐진 것
    
    - NOT NULL(NN:CK,C)체크제약조건이다.
    해당 컬럼은 NULL을 포함할 수 없다.(비어있어선 안된다)
    
    - CHECK(CK:C)
    해당 컬럼에서 저장 가능한 데이터의 값의 범위나 조건을 정한다.
    
    ----------------------------------------------------------------------

 

 

 

■■■  PRIMARY KEY ■■■

 

 

  /*  ■■■  PRIMARY KEY ■■■
    
1. 테이블에 대한 기본키를 생성한다.

2. 테이블에서 각 행을 유일하게 식별하는 컬럼 또는 컬럼의 집합이다.
-- 기본 키는 테이블 당 최대 하나만 존재한다.
    그러나 반드시 하나의 컬럼으로만 구성되는 것은 아니다.
    NULL일 수 없고, 이미 테이블에 존재하고 있는 데이터를
    다시 입력할 수 없도록 처리된다,
    UNIQUE INDEX가 자동으로 생성된다. (오라클이 자체적으로 만든다.)

3. 형식 및 구조
① 컬럼 레벨의 형식
컴럼명 데이터타입[CONSTRAINT CONSTRAINT명] PRIMARY KEY[(컬럼명,...)]

② 테이블 레벨의형식
-- 컬럼명 데이터타입
-- 컬럼명 데이터타입,
-- CONSTRAINT CONTSTRAINT명 'PRIMARY KEY(컬럼명[,...])

4. CONSTRAINT 추가 시CONSTRAINT 명을 생략하면
오라클 서버가 자동적으로 CONTSTRAINT 명을 부여하게 된다.
일반적으로 CONSTARINT 명은 테이블명_컬럼명_CONSTRAINT 약어
형식으로 기술한다.


-- ●PK 실습(① 컬럼 레벨의 형식)
-- 테이블 생성*/

CREATE TABLE TBL_TEST1
(  COL1 NUMBER(5)       PRIMARY KEY
,   COL2 VARCHAR(30)
);
--==>>Table TBL_TEST1이(가) 생성되었습니다.

--데이터 입력
INSERT INTO TBL_TEST1(COL1,COL2) VALUES(1,'TEST');
--==>>1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(COL1,COL2) VALUES(2,'ABCD');
--==>>1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(COL1,COL2) VALUES(3,NULL);
--==>>1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(COL1) VALUES(4);
--==>>1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(COL1,COL2) VALUES(2,'ABCD');
--==>> 에러발생 unique constraint (HR.SYS_C007105) violated
INSERT INTO TBL_TEST1(COL1,COL2) VALUES(5,'ABCD');
--==>>1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(COL1,COL2) VALUES(NULL,NULL);--==>>에러발생
INSERT INTO TBL_TEST1(COL2) VALUES('STUDY'); --==>> 에러발생

COMMIT;--==>>커밋 완료.

SELECT *
FROM TBL_TEST1;
/*
1	TEST
2	ABCD
3	(null)
4	(null)
5	ABCD*/
DESC TBL_TEST1;

/*
커밋 완료.
이름   널?       유형           
---- -------- ------------ 
COL1 NOT NULL NUMBER(5)    
COL2          VARCHAR2(30) */
-- 주의! NOT NULL 이라는 게 PRIMARY KEY라는 것은아니다

-- ● 제약조건 확인
SELECT *
FROM USER_CONSTRAINTS;
/*
HR	SYS_C004102	        O	EMP_DETAILS_VIEW					ENABLED	NOT DEFERRABLE	IMMEDIATE	NOT VALIDATED	GENERATED NAME			14/05/29				
HR	JHIST_DATE_INTERVAL	C	JOB_HISTORY	        end_date > start_date				ENABLED	NOT DEFERRABLE	IMMEDIATE	VALIDATED	USER NAME			14/05/29				
HR	JHIST_JOB_NN	    C	JOB_HISTORY	        "JOB_ID" IS NOT NULL				ENABLED	NOT DEFERRABLE	IMMEDIATE	VALIDATED	USER NAME			14/05/29				
HR	JHIST_END_DATE_NN	C	JOB_HISTORY	        "END_DATE" IS NOT NULL				ENABLED	NOT DEFERRABLE	IMMEDIATE	VALIDATED	USER NAME			14/05/29				
            :
            :
            :


*/
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='TBL_TEST1';
/* OWNER    CONSTRAINT_NAME     CONSTRAINT_TYPE   TABLE_NAME    SEARCH_CONDITION R_OWNER
----------------------------------------------------------------------------------
    HR	        SYS_C007105	       P	            TBL_TEST1		(null)(null)(null)	(null)

*/

-- ● 제약 조건이 지정된 컬럼확인(조회)

SELECT *
FROM USER_CONS_COLUMNS;




SELECT *
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME ='TBL_TEST1';
--==>>HR	SYS_C007105	TBL_TEST1	COL1	1

-- ● 제약조건이 설정된 소유주, 제약명, 테이블명, 제약종류, 컬럼명 항목
SELECT UC.OWNER, UC.CONSTRAINT_NAME, UC.TABLE_NAME
    ,  UC.CONSTRAINT_TYPE, UCC.COLUMN_NAME
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'TBL_TEST1';
--==>>>HR	SYS_C007105	TBL_TEST1	P	COL1
           --============  오라클이 자동으로 붙여준 제약조건의 이름

CREATE TABLE TBL_TEST2
(  COL1 NUMBER(5)       
,   COL2 VARCHAR2(30)
,CONSTRAINT TEST2_COL1_PK PRIMARY KEY(COL1)
);
--===>>Table TBL_TEST2이(가) 생성되었습니다.





INSERT INTO TBL_TEST2(COL1,COL2) VALUES(1,'TEST');
--==>>1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST2(COL1,COL2) VALUES(2,'ABCD');
--==>>1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST2(COL1,COL2) VALUES(3,NULL);
--==>>1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST2(COL1) VALUES(4);
--==>>1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST2(COL1,COL2) VALUES(2,'ABCD');
--==>> 에러발생 unique constraint (HR.SYS_C007105) violated
INSERT INTO TBL_TEST2(COL1,COL2) VALUES(2,'KKKK');
INSERT INTO TBL_TEST2(COL1,COL2) VALUES(5,'ABCD');
--==>>1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST2(COL1,COL2) VALUES(NULL,NULL);--==>>에러발생
INSERT INTO TBL_TEST2(COL2) VALUES(NULL,'STUDY');--==>> 에러발생
INSERT INTO TBL_TEST2(COL2) VALUES('STUDY'); --==>> 에러발생

COMMIT;--==>>커밋 완료.

SELECT *
FROM TBL_TEST2;
/*
1	TEST
2	ABCD
3	
4	
5	ABCD
*/

-- ● 제약조건이 설정된 소유주, 제약명, 테이블명, 제약종류, 컬럼명 항목
SELECT UC.OWNER, UC.CONSTRAINT_NAME, UC.TABLE_NAME
    ,  UC.CONSTRAINT_TYPE, UCC.COLUMN_NAME
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'TBL_TEST2';
--==>>HR	TEST2_COL1_PK	TBL_TEST2	P	COL1






-- ● PK 지정 실습(③ 다중 컬럼 PK 지정 → 복합 프라이머리 키)
CREATE TABLE TBL_TEST3
(  COL1 NUMBER(5)       
,   COL2 VARCHAR2(30)
,   CONSTRAINT TEST3_COL1_PK PRIMARY KEY(COL1)
,   CONSTRAINT TEST3_COL1_PK PRIMARY KEY(COL2)
);
--==>> 이렇게하면안된다


CREATE TABLE TBL_TEST3
(  COL1 NUMBER(5)       
,   COL2 VARCHAR2(30)
,   CONSTRAINT TEST3_COL1_COL2_PK PRIMARY KEY(COL1,COL2)

);
--==>>Table TBL_TEST3이(가) 생성되었습니다.




INSERT INTO TBL_TEST3(COL1,COL2) VALUES(1,'TEST');
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(2,'ABCD');
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(3,NULL);--==>>에러발생
INSERT INTO TBL_TEST3(COL1) VALUES(4);--==>>에러발생

INSERT INTO TBL_TEST3(COL1,COL2) VALUES(2,'ABCD');--==>>에러발생

INSERT INTO TBL_TEST3(COL1,COL2) VALUES(3,'ABCD');
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(1,'ABCD');
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(2,'KKKK');
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(5,'ABCD');
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(NULL,NULL);--==>>에러발생
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(NULL,'STUDY');--==>>에러발생
INSERT INTO TBL_TEST3(COL2) VALUES('STUDY'); --==>> 에러발생

COMMIT;

SELECT *
FROM TBL_TEST3;
/*
1	ABCD
1	TEST
2	ABCD
2	KKKK
3	ABCD
5	ABCD
*/



--● PK 지정 실습 (④ 테이블 생성 이후 제약조건만 추가 → PK 지정)

CREATE TABLE TBL_TEST4
(COL1   NUMBER(5)
, COL2  VARCHAR2(30)
);
--==>>Table TBL_TEST4이(가) 생성되었습니다.

-- 제약 조건을 추가하려고 하는데 주의해야 할 점 
-- ※ 이미 만들어져 있는 테이블에 부여하려는 제약조건을 위반한 데이터가 포함되어 있을 경우
-- 해당 테이블에 제약 조건을 추가하는 것은 불가능하다.

-- 제약 조건 추가
ALTER TABLE TBL_TEST4 
ADD CONSTRAINT TEST4_COL1_PK PRIMARY KEY(COL1);
--===>>>Table TBL_TEST4이(가) 변경되었습니다.

-- 다만 테이블 레벨에서 제약 조건 추가하기가 권장됨.

-- 제약 조건을 확인하기 위한 전용 뷰(VIEW) 생성
CREATE OR REPLACE VIEW VIEW_CONSTCHECK
AS
SELECT UC.OWNER "OWNER"
    ,  UC.CONSTRAINT_NAME "CONSTRAINT_NAME"
    ,  UC.TABLE_NAME "TABLE_NAME"  --여기서 " TABLE_NAME" ← 이렇게 공백주면 안되는이유는?
    ,  UC.CONSTRAINT_TYPE "CONSTRAINT TYPE"
    ,  UCC.COLUMN_NAME "COLUMN_NAME"
    ,  UC.SEARCH_CONDITION "SEARCH_CONDITION"
    ,  UC.DELETE_RULE "DELETE_RULE"
FROM USER_CONSTRAINTS UC JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME;
--==>>View VIEW_CONSTCHECK이(가) 생성되었습니다.

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_TEST4';

--==>> HR	TEST4_COL1_PK	TBL_TEST4	P	COL1



---------------------------------------------------------------------------
*/

■■■ UNIQUE ■■■ 

 

--■■■ UNIQUE ■■■ 

/*
1. 테이블에서 지정한 컬럼의 데이터가 중복되지 않고
테이블 내에서 유일할 수 있도록 설정하는 제약조건.
PRIMARY KEY 와 유사한 제약조건이지만, NULL을 허용한다는 차이가 있다
내부적으로 PRIMARY KEY와 마찬가지로 UNIQUE INDEX가 자동 생성된다.
하나의 테이블 내에서 UNIQUE 제약조건은 여러 번 설정하는 것이 가능하다.
즉, 하나의 테이블에 UNIQUE 제약조건을 여러개 만드는 것이 가능하다는 것이다. 

 2. 형식 및 구조
① 컬럼 레벨의 형식
 컬럼명 데이터 타입[CONSTRAINT CONSTRAINT명] UNIQUE

② 테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
CONSTRAINT CONSTRAINT명 UNIQUE(컬럼명[, ...])

● UK 지정 실습(① 컬럼 레벨의 형식)

*/

CREATE TABLE TBL_TEST5
( COL1   NUMBER(5)   PRIMARY KEY
, COL2  VARCHAR2(30)UNIQUE
);

--==>>Table TBL_TEST5이(가) 생성되었습니다.

-- 제약 조건 조회


SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST5';
/*

HR	SYS_C007109	TBL_TEST5	P	COL1
HR	SYS_C007110	TBL_TEST5	U	COL2

*/



-- 데이터입력
INSERT INTO TBL_TEST5(COL1,COL2) VALUES(1,'TEST');
INSERT INTO TBL_TEST5(COL1,COL2) VALUES(2,'ABCD');
INSERT INTO TBL_TEST5(COL1,COL2) VALUES(3,NULL);
INSERT INTO TBL_TEST5(COL1) VALUES(4);
INSERT INTO TBL_TEST5(COL1,COL2) VALUES(2,'ABCD');--==>>에러발생
INSERT INTO TBL_TEST5(COL1,COL2) VALUES(5,'ABCD');--==>>에러발생
INSERT INTO TBL_TEST5(COL1,COL2) VALUES(5,NULL);
INSERT INTO TBL_TEST5(COL1,COL2) VALUES(6,NULL);

COMMIT;

SELECT *

FROM TBL_TEST5;


-- ● UK 지정 실습(② 테이블 레벨의 형식)
--테이블 생성
CREATE TABLE TBL_TEST6
( COL1 NUMBER(5)
,  COL2 VARCHAR2(30)
, CONSTRAINT TEST6_COL1_PK PRIMARY KEY(COL1)
, CONSTRAINT TEST6_COL2_UK UNIQUE(COL2)
);
--==>>Table TBL_TEST6이(가) 생성되었습니다.

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST6';
/*
HR	TEST6_COL1_PK	TBL_TEST6	P	COL1
HR	TEST6_COL2_UK	TBL_TEST6	U	COL2
*/


-- UK 지정 실습(③ 테이블 생성 이후 제약조건 추가 → UK 제약조건 추가)
-- 테이블 생성

CREATE TABLE TBL_TEST7
( COL1 NUMBER(5)
,  COL2 VARCHAR2(30)

);

--==>>Table TBL_TEST7이(가) 생성되었습니다.

-- 제약 조건 확인(조회)
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_TEST7';
--==>> 조회 결과 없음

-- 제약 조건 추가

ALTER TABLE TBL_TEST7
ADD (CONSTRAINT TEST7_COL1_PK PRIMARY KEY(COL1)
    ,CONSTRAINT TEST7_COL2_UK UNIQUE(COL2)
);
--==>> Table TBL_TEST7이(가) 변경되었습니다.


-- 제약 조건 확인(조회)
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_TEST7';
/*
HR	TEST7_COL1_PK	TBL_TEST7	P	COL1
HR	TEST7_COL2_UK	TBL_TEST7	U	COL2
*/