▩ 제약 조건(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 해보세요.