Oracle : 제약 조건(PK, FK, default, unique, check)의 이해 및 실습

▩ 제약 조건(PK, FK, default, unique, check)의 이해 및 실습
   - 정규화에 대한 지식을 갖어야 쉽게 구현 가능합니다.
   - Column Level: 컬럼 정의 시 선언 합니다.
   - Table Level: 컬럼을 다 정의하고 난 후 정의 합니다.
   - NOT NULL은 Column Level만 사용 가능합니다.
   - Constraint name은 보통 테이블명_컬럼명_PK, FK 로 지정 합니다.

   - 하나의 큰 테이블을 2개 이상의 테이블로 분리할시에 그 분리된 테이블들은 하나의 공통된 컬럼을 반드시
     가지고 있어야 합니다. 이 공통된 컬럼은 테이블을 다시 합칠(JOIN)경우 join의 기준값으로 사용하게 됩니다.
     
   - 테이블을 2개로 분리할경우 컬럼에 중복된 값이 들어가서는 안되는 테이블을 부모테이블이라고 하며 그 컬럼을 Primary Key
     Column이라고 합니다.
     일반적으로 코드의 형태를 가지고 있습니다.

   - 공통 컬럼에 중복된 값이 들어가는 테이블을 자식테이블, 데이터 테이블이라고 하며 그 컬럼을 Foreign Key Column 이라고 합니다.

1. 테이블 구조 생성 순서

   ① P.K테이블 구조를 다른 테이블이 참조함으로 P.K 테이블 구조를 먼저 만듭니다. 
      - 코드테이블이 해당이 됩니다.
      - 부모테이블 이라고 합니다.
      - PK가 적용된 컬럼은 중복된 값이 올 수 없습니다.
      - NULL 값이 올 수 없습니다.
      - 사원 대장, 학적부, 각종 코드표, 주민등록부, 수강생 명부

   ② F.K 테이블 구조를 만듭니다.
      - 데이터 테이블이라고 합니다.
      - 자식 테이블 이라고 합니다.
      - FK가 적용된 컬럼은 중복된 값이 올 수 있습니다.
      - NULL 값이 올 수 없습니다.
      - 사원의 월별 급여 대장, 전철의 운행 기록, 수강 기록

2. 레코드 추가시 순서

   ① 코드 테이블인 P.K 테이블에 레코드(코드)를 먼저 등록합니다.

   ② F.K 테이블에 레코드(데이터)를 추가합니다.

   사원을 등록해야 급여 대장에 자료를 등록하는 것과 순서입니다.

3. 레코드 삭제시의 순서

   ① F.K 테이블의 레코드를 삭제합니다., 데이터를 먼저 삭제합니다.

   ② 필요에 따라 P.K 테이블의 코드 레코드를 삭제 합니다.

4. 테이블 삭제시의 순서

   ① F.K 테이블 구조를 삭제 합니다.

   ② P.K 테이블 구조를 삭제 합니다.

▩ 하나의 테이블로 구성된 경우
   
1. 문제점
   - 불필요한 데이터가 중복해서 누적되게 됩니다.
   - null값이 불필요하게 많이 생성될 수 있습니다.
   - 레코드 식별이 안됩니다.
   - 저장 매체 낭비가 심하게 발생합니다.
   - 동시 접속시 심각한 데이터베이스 서버상에 부하를 발생할 수 있습니다.
   - 네트워크 트래픽이 많이 발생합니다.

create table stu(
 no    number(5) NOT NULL,
 stuname  varchar2(10)   NOT NULL,      --반드시 값이 입력되어야합니다.
 phone     varchar2(14),
 jumin      char(14),
 class      char(1),      
 kuk   number(3),
 eng   number(3),
 mat   number(3),
 tot   number(3),
 avg   number(5, 1)
);

CREATE SEQUENCE stu_no_seq
    START WITH   1           --시작번호
    INCREMENT BY 1           --증가값
    MAXVALUE     99999       --최대값
    CACHE 20                 --시쿼스 변경시 자주 update되는 것을 방지하기위한 캐시값
    NOCYCLE;     

SELECT * FROM stu;

INSERT INTO stu(no, stuname, phone, jumin, class, kuk, eng, mat) 
VALUES(stu_no_seq.NextVal, '왕눈이','111-1111','100000-1000000',1, 100, 100, 50);

INSERT INTO stu(no, stuname, phone, jumin, class, kuk, eng, mat) 
VALUES(stu_no_seq.NextVal, '아로미','111-1111','100000-1000000',1, 90, 95, 85);

INSERT INTO stu(no, stuname, phone, jumin, class, kuk, eng, mat) 
VALUES(stu_no_seq.NextVal, '아로마','111-1111','100000-1000000',2, 100, 90, 85);

SELECT * FROM stu;

INSERT INTO stu(no, stuname, phone, jumin, class, kuk, eng, mat) 
VALUES(stu_no_seq.NextVal, '왕눈이','111-1111','100000-1000000',2, 100, 100, 80);

INSERT INTO stu(no, stuname, phone, jumin, class, kuk, eng, mat) 
VALUES(stu_no_seq.NextVal, '아로미','111-1111','100000-1000000',1, 85, 70, 85);

INSERT INTO stu(no, stuname, phone, jumin, class, kuk, eng, mat) 
VALUES(stu_no_seq.NextVal, '아로마','111-1111','100000-1000000',2, 60, 50, 70);

SELECT * FROM stu;

INSERT INTO stu(no, stuname, phone, jumin, class, kuk, eng, mat) 
VALUES(stu_no_seq.NextVal, '왕눈이','6258-8000','123456-7777777',4, 100, 100, 80);

INSERT INTO stu(no, stuname, phone, jumin, class, kuk, eng, mat) 
VALUES(stu_no_seq.NextVal, '아로미','111-1111','100000-1000000',5, 85, 70, 85);

INSERT INTO stu(no, stuname, phone, jumin, class, kuk, eng, mat) 
VALUES(stu_no_seq.NextVal, '아로마','123-1004','100000-1000000',3, 60, 50, 70);

SELECT * FROM stu;

COMMIT;

▩ 키 제약 조건을 이용해 2개 이상의 테이블로 분할한 경우

1. Primary Key Table
DROP TABLE sungjuk;
DROP TABLE student;
DROP SEQUENCE student_stuno_seq;

create table student(
 no          number(5)   NOT NULL   ,   --반드시 값이 입력되어야하며, 중복이 발생하면 안됩니다., null입 될수 없습니다.
 stuno       char(3)     NOT NULL   ,   --반드시 값이 입력되어야하며, 중복이 발생하면 안됩니다., null입 될수 없습니다.
 stuname  varchar2(10)   NOT NULL,      --반드시 값이 입력되어야합니다.
 phone     varchar2(14)  DEFAULT '-',   --값을 입력하지 않으면 특정문자 '-'이 들어갑니다.
 jumin      char(14)     UNIQUE,        --중복된 값을 넣을 수 없습니다.
 class      char(1)      CHECK(class in('1', '2', '3')), --class컬럼의 값이 1,2,3중 하나이어야 합니다.
 CONSTRAINT student_stuno_pk PRIMARY KEY(stuno)
);

CREATE SEQUENCE student_no_seq
    START WITH   1           --시작번호
    INCREMENT BY 1           --증가값
    MAXVALUE     99999       --최대값
    CACHE 20                 --시쿼스 변경시 자주 update되는 것을 방지하기위한 캐시값
    NOCYCLE;     

SELECT * FROM student ORDER BY stuno;

INSERT INTO student 
VALUES(student_no_seq.NextVal, 'A01', '왕눈이','111-1111','100000-1000000',1);
SELECT * FROM student ORDER BY stuno;

--check 이용(제약 조건 Error) 
INSERT INTO student(no, stuno, stuname, jumin, class)  
VALUES(student_no_seq.NextVal, 'A02', '투투','100000-1000002',5);
SELECT * FROM student ORDER BY stuno;

INSERT INTO student(no, stuno, stuname, phone, jumin, class)  
VALUES(student_no_seq.NextVal, 'B01', '아로미','111-1111','100000-1000001',2);
SELECT * FROM student ORDER BY stuno;

--default 이용
INSERT INTO student(no, stuno, stuname, jumin, class)  
VALUES(student_no_seq.NextVal, 'B02', '아로마','100000-1000002',2);
SELECT * FROM student ORDER BY stuno;

2. Foreign Key Table

create table sungjuk(
 no    number(5) NOT NULL,
 kuk   number(3),
 eng   number(3),
 mat   number(3),
 tot   number(3),
 avg   number(5, 1),
 stuno char(3) Not Null,
 CONSTRAINT sungjuk_no_pk PRIMARY KEY(no),
 CONSTRAINT sungjuk_stuno_fk FOREIGN KEY(stuno) REFERENCES student(stuno)
);

CREATE SEQUENCE sungjuk_no_seq
    START WITH   1           --시작번호
    INCREMENT BY 1           --증가값
    MAXVALUE     99999       --최대값
    CACHE 20                 --시쿼스 변경시 자주 update되는 것을 방지하기위한 캐시값
    NOCYCLE;                

--ⓐ
INSERT INTO sungjuk(no, kuk, eng, mat, stuno)
VALUES(sungjuk_no_seq.NextVal, 100, 90, 90, 'A01');
SELECT * FROM sungjuk ORDER BY stuno;

INSERT INTO sungjuk(no, kuk, eng, mat, stuno)
VALUES(sungjuk_no_seq.NextVal, 100, 90, 90, 'B01');
SELECT * FROM sungjuk ORDER BY stuno;

INSERT INTO sungjuk(no, kuk, eng, mat, stuno)
VALUES(sungjuk_no_seq.NextVal, 90, 80, 80, 'B02');
SELECT * FROM sungjuk ORDER BY stuno;

INSERT INTO sungjuk(no, kuk, eng, mat, stuno)
VALUES(sungjuk_no_seq.NextVal, 100, 90, 90, 'A01');
SELECT * FROM sungjuk ORDER BY stuno;

--ⓑ PK, FK제약조건의 설정으로 stuno컬럼의 값은 student테이블에
--등록된 값만 추가할 수 있습니다.
INSERT INTO sungjuk(no, kuk, eng, mat, stuno)
VALUES(sungjuk_no_seq.NextVal, 100, 90, 90, 'C01');
SELECT * FROM student ORDER BY stuno;

--ⓒ PK테이블에 있는 stuno컬럼의 값'1'은 F.K테이블에서 참조하고 있음으로
--지울 수 없습니다.
DELETE FROM student WHERE stuno = 'A01';

SELECT * FROM sungjuk;
SELECT * FROM student;

--등록되지 않은 학생의 성적이 성적테이블에 등록되는 것을
--구조적으로 막으려고 하는 것이 제약조건입니다.

3. 제약조건의 확인
   - SELECT constraint_name, constraint_type FROM user_constraints;

select * from student;
select * from sungjuk;
delete from sungjuk where stuno='A01';   --FK테이블에서 레코드를 삭제합니다.
delete from student where stuno='A01';   --PK테이블에서 레코드를 삭제합니다.

4. 제약 조건의 삭제
   ALTER TABLE sungjuk
   DROP CONSTRAINT sungjuk_stuno_fk;
  

▩ 제약조건의 실습[과제]

1. PK테이블명: 사원
   - 사원번호: PK 지정  
   - 사원명
   - 사원 전화번호
   - 사원 메모
 

2. FK테이블명: 급여
   - 일련번호: PK 지정
   - 본봉
   - 세금
   - 실수령액
   - 사원테이블의 사원번호: FK 지정 

- 테이블마다 sequence 생성하세요.

테이블당 레코드를 최소 3개이상 INSERT 해보세요.

[출처] Oracle: 제약 조건(PK, FK, default, unique, check)의 이해 및 실습|작성자 땡중이