8일차_제약 조건

--------------------------------------------
무결성

1. 무결성에는 개체 무결성(Entity Integrity), 참조 무결성(Relational Integrity), 도메인 무결성(Domain Integrity)가 있다.

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

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

4. 도메인 무결성
도메인 무결성은 속성에서 허용 가능한 값의 범위를 지정하기 위한 제약조건이다.

예를 들어, 학생 정보 저장용 테이블을 만든다면
--테이블 생성
CREATE TABLE member (     --테이블 이름 member
 sid NUMBER        --고유번호 저장용 컬럼
 ,name VARCHAR2(10) --이름, 10글자만 허용
 ,kor NUMBER(3)     --국어, 숫자 3자리만 허용
 ,eng NUMBER(3)     --영어, 숫자 3자리만 허용
 ,mat NUMBER(3)     --수학, 숫자 3자리만 허용
);

5. 제약조건 종류
- PRIMARY KEY(PK) : 해당 컬럼 값은 반드시 존재해야 하며, 유일해야 함(NOT NULL과 UNIQUE 제약조건을 결합한 형태)
- FOREIGN KEY(FK) : 해당 컬럼 값은 참조되는 테이블의 컬럼 값 중의 하나와 일치하거나 NULL을 가짐
- UNIQUE KEY(UK) : 테이블내에서 해당 컬럼 값은 항상 유일해야 함
- NOT NULL : 컬럼은 NULL 값을 포함할 수 없다.
- CHECK(CK) : 해당 컬럼에 저장 가능한 데이터 값의 범위나 조건 지정

------------------------------------------------
***PRIMARY KEY (PK)

1. 테이블에 대한 기본 키를 생성한다.

2. 테이블에서 각 행을 유일하게 식별하는 컬럼 또는 컬럼의 집합이다. 기본 키는 테이블 당 하나만 존재한다. 그러나, 반드시 하나의 컬럼으로 만 구성되는 것은 아니다. NULL 값이 입력될 수 없고, 이미 테이블에 존재하고 있는 데이터를 다시 입력할 수 없다. UNIQUE INDEX가 자동으로 만들어 진다.

3.
컬럼 레벨의 형식
컬럼명 데이터타입 [CONSTRAINT constraint명] PRIMARY KEY(컬럼명,...)

테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
[CONSTRAINT constraint명] PRIMARY KEY(컬럼1명, 컬럼2명,...)

4. constraint를 추가 시에 constraint 명을 생략하면 오라클 서버가 자동적으로 constraint 명을 부여한다. 일반적으로 constraint명은 테이블명_컬럼명_constraint약자'처럼 기술한다.

PK 지정 테스트 - 컬럼 레벨의 형식
CREATE TABLE test1 (
 col1 NUMBER(3) PRIMARY KEY
 ,col2 VARCHAR2(10)
);

INSERT INTO test1 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test1 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test1 (col1, col2)
 VALUES (3, NULL);
INSERT INTO test1 (col1, col2)
 VALUES (2, 'ABCD');  --ORA-00001 (중복된 번호 입력 불가)
INSERT INTO test1 (col1, col2)
 VALUES (NULL, NULL); --ORA-01400 (NULL 입력 불가)
COMMIT;

DESC test1;
이름   널        유형          
---- -------- ------------
COL1 NOT NULL NUMBER(3)      -->PK 제약 확인 불가
COL2          VARCHAR2(10)

--제약 조건 확인
SELECT * FROM user_constraints;

SELECT * FROM user_constraints
 WHERE table_name='TEST1'; --테이블 이름 대문자

--제약이 지정된 컬럼 확인
SELECT * FROM user_cons_columns
  WHERE table_name='TEST1'; --테이블 이름 대문자

--제약이 걸린 소유주, 제약명, 테이블, 제약종류, 컬럼 정보 확인
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='TEST1';  --테이블 이름 대문자

PK 지정 테스트 - 테이블 레벨의 형식
--> 제약명을 사용자가 결정한다
--> 제약명은 '테이블명_컬럼명_PK' 형식으로 작성한다.

CREATE TABLE test2 (
 col1 NUMBER(3)
 ,col2 VARCHAR2(10)
 ,CONSTRAINT TEST2_COL1_PK PRIMARY KEY(col1)
);

INSERT INTO test2 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test2 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test2 (col1, col2)
 VALUES (3, NULL);
INSERT INTO test2 (col1, col2)
 VALUES (2, 'ABCD');  --ORA-00001
INSERT INTO test2 (col1, col2)
 VALUES (NULL, NULL); --ORA-01400
COMMIT;

--제약이 걸린 소유주, 제약명, 테이블, 제약종류, 컬럼 정보 확인
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='TEST2';  --테이블 이름 대문자

PK 지정 테스트 - 다중 컬럼 PK 지정 (복합키)
CREATE TABLE test3 (
 col1 NUMBER(3)
 ,col2 VARCHAR2(10)
 ,CONSTRAINT TEST3_COL1_COL2_PK PRIMARY KEY(col1, col2)
);

INSERT INTO test3 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test3 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test3 (col1, col2)
 VALUES (3, NULL); --ORA-01400
INSERT INTO test3 (col1, col2)
 VALUES (3, 'ABCD'); --col1은 다른 데이터, col2는 같은 데이터
INSERT INTO test3 (col1, col2)
 VALUES (2, 'KOREA'); --col1은 같은 데이터, col2는 다른 데이터
INSERT INTO test3 (col1, col2)
 VALUES (NULL, NULL); --ORA-01400
INSERT INTO test3 (col1, col2)
 VALUES (2, 'ABCD'); --col1, col2 모두 같은 데이터. ORA-00001

COMMIT;

***PK 지정 테스트 - 테이블 생성 후 제약 추가
CREATE TABLE test4 (
 col1 NUMBER(3)
 ,col2 VARCHAR2(10)
);

ALTER TABLE test4
 ADD CONSTRAINT TEST4_COL1_PK PRIMARY KEY(col1);

INSERT INTO test4 (col1, col2)
 VALUES (1, 'TEST');
INSERT INTO test4 (col1, col2)
 VALUES (2, 'ABCD');
INSERT INTO test4 (col1, col2)
 VALUES (3, NULL);
INSERT INTO test4 (col1, col2)
 VALUES (2, 'ABCD');  --ORA-00001
INSERT INTO test4 (col1, col2)
 VALUES (NULL, NULL); --ORA-01400
COMMIT;

-----------------------------------------
HR 스키마 ERD에서 PK 분석

DESC employees; -->PK 분석 불가

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='EMPLOYEES';  --테이블 이름 대문자
-->EMPLOYEE_ID 컬럼에 PK 지정됨.

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='DEPARTMENTS';  --테이블 이름 대문자
-->DEPARTMENT_ID 컬럼에 PK 지정됨.

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='JOB_HISTORY';  --테이블 이름 대문자
-->EMPLOYEE_ID, START_DATE 컬럼에 PK 지정됨.

--------------------------------------
제약조건 확인용 뷰 생성

CREATE VIEW 뷰이름
AS
서브쿼리;

SELECT * FROM 뷰이름;
SELECT * FROM 뷰이름 WHERE 조건식;

CREATE VIEW constcheck
AS
SELECT uc.owner AS owner, uc.constraint_name AS constraint_name
 , uc.table_name AS table_name
 , uc.constraint_type AS constraint_type
 , ucc.column_name AS column_name
 , uc.search_condition AS search_condition
 , uc.delete_rule AS delete_rule
  FROM user_constraints uc, user_cons_columns ucc
  WHERE uc.constraint_name = ucc.constraint_name;

SELECT * FROM constcheck;
SELECT * FROM constcheck WHERE table_name='TEST1';

* hr 계정은 CREATE VIEW 권한이 있지만, scott 계정은 CREATE VIEW 권한이 없습니다. 추가로 권한 부여가 필요함. 관리자만 가능.
GRANT CREATE VIEW TO scott;

--------------------------------------------------
***FOREIGN KEY (FK)

1. 참조 키 또는 외래 키(FK)는 두 테이블의 데이터 간 연결을 설정하고 강제 적용하는 데 사용되는 열이다. 한 테이블의 기본 키 값이 있는 열을 다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다. 이 때 두 번째 테이블에 추가되는 열이 외래 키가 된다.

외래 키가 적용된 컬럼에는 데이터 입력시 기본 키 값 범위 내에서만 입력 가능.

2. 부모 테이블이 먼저 생성된 후 자식 테이블(foreign key를 포함하는 테이블)이 생성되어야 한다.

3.
컬럼 레벨의 형식
컬럼명 데이터타입 CONSTRAINT constraint명
 REFERENCES 참조_테이블명 (참조_컬럼명)
 [ON DELETE CASCADE | ON DELETE SET NULL]

테이블레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
...
CONSTRAINT constraint명 FOREIGN KEY(컬럼)
 REFERENCES 참조_테이블명 (참조_컬럼명)
 [ON DELETE CASCADE | ON DELETE SET NULL]

 

부모 테이블 생성 작업을 먼저 해야합니다. 부모 테이블에는 반드시 PK 또는 UK 제약이 있는 컬럼이 존재해야 합니다.
CREATE TABLE jobs (
 jikwi_id NUMBER
 ,jikwi_name VARCHAR2(10)
);

ALTER TABLE jobs
  ADD CONSTRAINT JOBS_JIKWI_ID_PK PRIMARY KEY(jikwi_id);
 

INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (1, '과장');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (2, '대리');
INSERT INTO jobs (jikwi_id, jikwi_name) VALUES (3, '사원');
COMMIT;

FK 제약 지정 - 컬럼 레벨의 형식
CREATE TABLE employees (
 sid NUMBER PRIMARY KEY
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER REFERENCES jobs(jikwi_id)
);

INSERT INTO employees (sid, name, jikwi_id)
 VALUES (1, '홍길동', 1);
INSERT INTO employees (sid, name, jikwi_id)
 VALUES (2, '김길동', 2);
INSERT INTO employees (sid, name, jikwi_id)
 VALUES (3, '박길동', 4);  --ORA-02291
COMMIT;

SELECT sid, name, jikwi_name
  FROM employees e, jobs j
  WHERE e.jikwi_id = j.jikwi_id;

 

FK 제약 지정 - 테이블 레벨의 형식
CREATE TABLE employees (
 sid NUMBER
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER
 ,CONSTRAINT EMPLOYEES_SID_PK PRIMARY KEY(sid)
 ,CONSTRAINT EMPLOYEES_JIKWI_ID_FK
   FOREIGN KEY(jikwi_id)
   REFERENCES jobs(jikwi_id)
);

FK 제약 지정 - 테이블 생성 후 제약 추가
CREATE TABLE employees (
 sid NUMBER
 ,name VARCHAR2(10)
 ,jikwi_id NUMBER
);

ALTER TABLE employees
 ADD (CONSTRAINT EMPLOYEES_SID_PK PRIMARY KEY(sid)
  ,CONSTRAINT EMPLOYEES_JIKWI_ID_FK
   FOREIGN KEY(jikwi_id)
   REFERENCES jobs(jikwi_id));

4. FOREIGN KEY 생성 시 주의사항
- 참조하고자 하는 부모 테이블을 먼저 생성해야 한다.
- 참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 있어야 한다.
- 테이블 사이에 PRIMARY KEY와 FOREIGN KEY가 정의 되어 있으면, primary key 삭제 시 foreign key 컬럼에 그 값이 입력되어 있으면 삭제가 안 된다. (단, FK 선언 때 ON DELETE CASCADE나 ON DELETE SET NULL옵션을 사용한 경우에는 삭제된다.)
- 부모 테이블을 삭제하기 위해서는 자식 테이블을 먼저 삭제해야 한다.
- 서로 제약에 의해 참조하는 경우는 서로 삭제되지 않는다. 제약조건을 먼저 삭제해야 한다.

ALTER TABLE 테이블명
 DROP CONSTRAINT (참조)제약명;

DROP TABLE jobs; --ORA-02449

DROP TABLE employees; --자식 테이블 먼저 삭제

DROP TABLE jobs; --부모 테이블 삭제

-----------------------------------------------------
과제)  HR 스키마의 7개 테이블에서 PK, FK 제약을 확인하고 쿼리로 작성.

예를 들어,
ALTER TABLE 테이블명
 ADD (CONSTRAINT 제약명 PRIMARY KEY(컬럼명)
  ,CONSTRAINT 제약명
   FOREIGN KEY(컬럼명)
   REFERENCES 상대방테이블명(컬럼명)
  ,CONSTRAINT 제약명
   FOREIGN KEY(컬럼명)
   REFERENCES 상대방테이블명(컬럼명));

 

 

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