Day 45. [Oracle SQL] 데이터 무결성 제약조건

데이터 무결성 제약조건의 개념

-- 데이터의 정확성과 일관성을 보장

-- 데이터의 정확성을 유지하여 다양한 종류의 업무규칙 고려한 예

 

* student 테이블에서 학년 데이터는 1, 2, 3, 4 중의 하나의 값만 입력

* 모든 학번은 유일

* student 테이블의 지도교수 번호는 professor 테이블의 교수 번호중의 하나와 반드시 일치

 

데이터 무결성 제약조건의 장점

-- 테이블 생성시 무결성 제약조건을 정의 가능

-- 테이블에 대해 정의, 데이터 딕셔너리에 저장되므로 응용 프로그램에서 입력된 모든 데이터에 대해 동일하게 적용

-- 제약조건을 활성화, 비활성화 할 수 있는 융통성

 

무결성 제약조건 종류

무결성 제약조건	설명
NOT NULL	열이 NULL을 포함할 수 없음
고유키(unique key)	테이블의 모든행에서 고유한 값을 갖는 열 또는 열조합을 지정합니다.
기본키(primary key)	해당 칼럼 값은 반드시 존재해야 하며 유일해야함 UNIQUE, NOT NULL 제약 조건을 결합한 형태
참조키(foreign key)	한열과 참조된 테이블의 열간에 외래 키 관계를 설정하고 시행합니다.
CHECK	해당 칼럼에 저장 가능한 데이터 값의 범위나 조건 지정

 

 

 

 

-- DELETE CASCADE
-- 부모 삭제 되면 자식 테이블도 삭제

CREATE TABLE SUBJECT (
    SUBNO NUMBER(5) CONSTRAINT PK_SUBJECJT
    PRIMARY KEY DEFERRABLE INITIALLY DEFERRED,
--    USING INDEX TABLESPACE INDX,
    SUBNAME VARCHAR2(20)
    CONSTRAINT NN_NNAME NOT NULL,
    TERM VARCHAR2(1)
    CONSTRAINT CK_TERM CHECK (TERM IN ('1', '2')),
    TYPE VARCHAR2(1)
);

SELECT * FROM SUBJECT;

ALTER TABLE STUDENT
    ADD CONSTRAINT PK_STUDENT PRIMARY KEY(STUDNO);
    
CREATE TABLE SUGANG (
    STUDNO CONSTRAINT FK_SUGANG_STUDNO REFERENCES STUDENT(STUDNO),
    SUBNO CONSTRAINT FK_SUGANG_SUBNO REFERENCES SUBJECT(SUBNO),
    REGDATE DATE DEFAULT SYSDATE,
    RESULT NUMBER(3),
    CONSTRAINT PK_SUGANG PRIMARY KEY(STUDNO, SUBNO)
);

SELECT * FROM SUGANG;

SELECT * FROM USER_CONSTRAINTS;

-- 학생 테이블 인스턴스를 참조하여 studno에 기본 키, idnum에 고유 키,name에 NOT NULL 무결성 제약조건을 추가하여라
ALTER TABLE STUDENT ADD CONSTRAINT UK_IDNUM UNIQUE(IDNUM);
ALTER TABLE STUDENT ADD CONSTRAINT UK_USERID UNIQUE(USERID);
ALTER TABLE STUDENT MODIFY (NAME CONSTRAINT NN_NAME NOT NULL);

-- 학과 테이블에 PK
ALTER TABLE DEPARTMENT MODIFY(DEPTNO CONSTRAINT PK_DEPARTMENT PRIMARY KEY);
-- DEPTNO 외래키 추가
ALTER TABLE STUDENT MODIFY(DEPTNO CONSTRAINT FK_DEPTNO REFERENCES DEPARTMENT(DEPTNO));



-- 학과 테이블 COLLEGE에 FK추가 FK_COLLEGE
ALTER TABLE DEPARTMENT MODIFY(COLLEGE CONSTRAINT FK_DEPT_COLLEGE REFERENCES DEPARTMENT(DEPTNO));
-- 학과 테이블 NAME NN 추가 NN_DNAME
ALTER TABLE DEPARTMENT MODIFY(DNAME CONSTRAINT NN_DEPT_DNAME NOT NULL);

-- 교수 테이블 PROFNO PK 추가 PK_PROFESSOR
ALTER TABLE PROFESSOR MODIFY(PROFNO CONSTRAINT PK_PROF_PROFNO PRIMARY KEY);
-- 교수 테이블 NAME NN 추가
ALTER TABLE PROFESSOR MODIFY(NAME CONSTRAINT NN_PROF_NAME NOT NULL);

-- 학생 테이블 PROFNO FK 추가
ALTER TABLE STUDENT MODIFY(PROFNO CONSTRAINT FK_STUD_PROFNO REFERENCES STUDENT(PROFNO));
-- 학생 테이블 GRADE CK 추가 (1~4)
ALTER TABLE STUDENT MODIFY(GRADE CONSTRAINT CK_STUD_GRADE CHECK(GRADE BETWEEN '1' AND '4'));

SELECT * FROM USER_CONS_COLUMNS;
SELECT * FROM USER_CONSTRAINTS;

DESC SUBJECT;
INSERT INTO SUBJECT VALUES(1001, '자바', '1', NULL);
INSERT INTO SUBJECT VALUES(1002, '오라클', '2', NULL);

-- 부서테이블에서 NAME 칼럼을 고유 인덱스로 생성하여라
-- 단, 고유인덱스의 이름을 IDX_DEPT_NAME으로 정의한
CREATE UNIQUE INDEX IDX_DEPT_NAME ON DEPARTMENT(DNAME);

SELECT * FROM USER_INDEXES;

-- 기본키 생성 : 해당 칼럼으로 NN, UK, UNIQUE INDEX 추가

SELECT /*+ INDEX_DESC(D PK_DEPARTMENT) */ * FROM DEPARTMENT D;

DESC TBL_BOARD;

-- PK_BOARD 기본키를 추가 BNO;
-- ALTER TABLE TBL_BOARD MODIFY(;
ALTER TABLE TBL_BOARD MODIFY(BNO CONSTRAINT PK_BOARD PRIMARY KEY);

SELECT * FROM (
    SELECT A.*, ROWNUM RN FROM (
       SELECT B.* FROM TBL_BOARD B ORDER BY BNO DESC
    ) A WHERE ROWNUM <= 30
)
WHERE RN > 20;

-- 인덱스 추가하면 코스트 비용이 줄어듬
SELECT * FROM (
    SELECT /*+ INDEX_DESC(A PK_BOARD) */ A.*, ROWNUM RN FROM TBL_BOARD A WHERE ROWNUM <= 30
) WHERE RN > 20;

-- 학생테이블의 deptno, grade 칼럼을 결합 인덱스로 생성하여라.
-- 결합인덱스의 이름은 idx_stud_dno_grade로 정의한다.


-- 학생테이블의 DEPTNO 와 NAME 칼럼으로 결합 인덱스를 생성하여라
-- 단, DEPTNO 칼럼을 내림차순으로 NAME 칼럼은 오름차순으로 생성하여라

CREATE INDEX IDX_STUD_DNO_NAME ON STUDENT(DEPTNO DESC, NAME);

SELECT * FROM STUDENT WHERE DEPTNO > 0 AND NAME > '가';

SELECT /*+ INDEX_DESC(STUDENT IDX_STUD_DNO_NAME) */ * FROM STUDENT;
SELECT * FROM USER_INDEXES;
SELECT * FROM USER_IND_COLUMNS;

SELECT *
FROM USER_INDEXES
JOIN USER_IND_COLUMNS USING(INDEX_NAME);

SELECT * FROM DICT WHERE TABLE_NAME LIKE 'USER%IND%';
SELECT * FROM USER_OBJECTS;

-- 학번, 이름, 학과번호, 교수번호를 가지는 STUD_TMP라는 테이블을 생성하여라
DROP TABLE STUD_TMP;
CREATE OR REPLACE VIEW STUD_TMP AS
SELECT STUDNO, NAME, DEPTNO, PROFNO
FROM STUDENT;

INSERT INTO STUD_TMP VALUES(12345, '김김김', 101, NULL);

SELECT * FROM STUD_TMP;
SELECT * FROM USER_VIEWS;

-- 학생 테이블과 부서 테이블을 조인하여 102번 학과 학생들의 학번, 이름, 학년, 학과 이름으로 정의되는
-- 복합뷰를 생성하여라.
CREATE OR REPLACE VIEW VIEW_COMP_TEST AS
SELECT STUDNO, NAME, DEPTNO, PROFNO
FROM STUDENT
NATURAL JOIN DEPARTMENT; -- 한개 이상의 테이블이 조인했을 경우 INSERT 불가 
--WHERE DEPTNO = '102';

SELECT * FROM VIEW_COMP_TEST;
DELETE FROM VIEW_COMP_TEST WHERE NAME = '김김김';
ROLLBACK;

-- 학생의 탄생연도별 인원수를 구하여라. 그리고 이 데이터를 사용해 VIEW_COMP_TEST2라는 VIEW를 생성하여라.
CREATE OR REPLACE VIEW VIEW_COMP_TEST2 AS
SELECT TO_CHAR(BIRTHDATE, 'YYYY') BIRTH, COUNT(*) CNT
FROM STUDENT
WHERE TO_CHAR(BIRTHDATE, 'YYYY') IS NOT NULL
GROUP BY TO_CHAR(BIRTHDATE, 'YYYY')
ORDER BY 1;

SELECT * FROM VIEW_COMP_TEST2;

DELETE VIEW_COMP_TEST2 WHERE BIRTH = '1940'; -- GROUP BY 쓰면 대부분 DML 못 써요

-- 인라인 뷰를 사용하여 학과별로 학생들의 평균키와 평균몸무게, 학과 이름을 출력하여라
SELECT DEPTNO, AVG(HEIGHT), AVG(WEIGHT), (SELECT DNAME FROM DEPARTMENT D WHERE D.DEPTNO = S.DEPTNO) AS DNAME
FROM STUDENT S
GROUP BY DEPTNO;

SELECT DNAME, D.DEPTNO, AVG_HEIGHT, AVG_WEIGHT
FROM DEPARTMENT D, (
    SELECT DEPTNO, AVG(HEIGHT) AS AVG_HEIGHT, AVG(WEIGHT) AS AVG_WEIGHT
    FROM STUDENT
    GROUP BY DEPTNO) S
WHERE S.DEPTNO = D.DEPTNO;

-- SCOTT 접속 오류
-- 권한을 줄때 몇일 이내에 들어가지 않으면 패스워드 만료됨

SELECT -- DEPTNO, DNAME, COLLEGE, LEVEL
LPAD(' ', LEVEL * 2) || DNAME D
FROM DEPARTMENT
START WITH DEPTNO = 101
CONNECT BY PRIOR COLLEGE = DEPTNO;