오라클 수업 7일째 (18.04.06) - 3 무결성 제약 조건의 개념과 종류

-- 01. 무결성 제약 조건의 개념과 종류

-- 03. 필수 입력을 위한 NOT NULL 제약 조건

/*

    따라서 사원의 정보를 입력할 때 

    반드시 입력해야하는 선택이 아닌 

    필수 입력을 요구하는 컬럼이 있다면 

    위와 같이 NULL 값이 저장되지 못하도록 

    제약 조건을 설정해야 합니다. 

    

    NOT NULL 제한 조건은 

    해당 컬럼에 데이터를 추가하거나 수정할 때 

    NULL 값이 저장되지 않게 제약을 걸어주는 것으로서 

    사원번호와 사원명과 같이 

    자료가 꼭 입력되게 하고 싶을 때 사용합니다.

    

    CREATE TABLE EMP02 (  

        EMPNO NUMBER(4) NOT NULL, 

        ENAME VARCHAR2(10) NOT NULL, 

        JOB VARCHAR2(9),

        DEPTNO NUMBER(2)

    ); 

*/

    DROP TABLE EMP02;

    DESC EMP02;

    SELECT * FROM EMP02;

    CREATE TABLE EMP02 (  

        EMPNO NUMBER(4) NOT NULL , -- 제약조건 설정 ( NOT NULL )

        ENAME VARCHAR2(10) NOT NULL , -- 제약조건 설정 ( NOT NULL )

        JOB VARCHAR2(9) ,

        DEPTNO NUMBER(2)

    ); 

    -- 아래 코드는 오류( 위에서 NOT NULL 이라 했는데 NULL을 삽입해서 )

    INSERT INTO EMP02 VALUES ( NULL, NULL, 'SALESMAN', 10 );

    

    

    -- 04. 유일한 값만 허용하는 UNIQUE 제약 조건

    /*

    UNIQUE 제약 조건이란 특정 칼럼에 대해 자료가 중복되지 않게 하는 것입니다. 

    

    즉, 지정된 칼럼에는 유일한 값이 수록되게 하는 것입니다.

    

    새로운 사원이 입사하여 이 사원의 정보를 입력했는데, 

    이미 존재하는 사원의 번호와 동일한 사원번호로 입력하였더니 

    성공적으로 추가된다면 어떻게 될까요? 

    */

    DROP TABLE EMP02;

    DESC EMP02;

    SELECT * FROM EMP02;0

    CREATE TABLE EMP02 (  

        EMPNO NUMBER(4) UNIQUE, -- 제약조건 설정 ( UNIQUE : 고유의 )

        ENAME VARCHAR2(10) NOT NULL ,

        JOB VARCHAR2(9) ,

        DEPTNO NUMBER(2)

    ); 

    

    INSERT INTO EMP02 VALUES ( NULL, 'AAA', 'SALESMAN', 10 );

    INSERT INTO EMP02 VALUES ( 7499, 'ALLEN', 'SALESMAN', 30 );

    INSERT INTO EMP02 VALUES ( 7499, 'ALLEN', 'SALESMAN', 30 );

    INSERT INTO EMP02 VALUES ( 1111, 'AAAA', 'AAAAAAAA', 10 );

    INSERT INTO EMP02 VALUES ( 1112, 'AAAA', 'AAAAAAAA', 10 );

    INSERT INTO EMP02 VALUES ( 1113, 'AAAA', 'AAAAAAAA', 10 );

    

    -- 06. 데이터 구분을 위한 PRIMARY((순서, 단계상으로) 최초의) KEY 제약 조건

    /*

    테이블 내의 해당 행을 다른 행과 구분할 수 있도록 하는 

    칼럼은 반드시 존재해야 합니다. 

    

    식별 기능을 갖는 칼럼은 유일하면서도 

    NULL 값을 허용하지 말아야 합니다. 

    

    즉, UNIQUE 제약 조건과 NOT NULL 제약 조건을 모두 갖고 있어야 하는데 

    이러한 두 가지 제약 조건을 모두 갖는 것이 

    기본 키(PRIMARY KEY) 또는, 주키 제약 조건입니다.

    */

    

DROP TABLE EMP05;

DESC EMP05;

SELECT * FROM EMP05;

-- PRIMARY KEY (식별무결성) : 다른 행과 구분할 수 있도록 하는 칼럼

CREATE TABLE EMP05

( 

    EMPNO NUMBER(4) PRIMARY KEY ,

    ENAME VARCHAR2(10) NOT NULL, 

    JOB VARCHAR2(9),

    DEPTNO NUMBER(2)

); 

    

INSERT INTO EMP05 VALUES ( 7499, 'ALLEN', 'SALESMAN', 30 );

-- unique constraint

INSERT INTO EMP05 VALUES ( 7499, 'JONES', 'MANAGER', 20 );

-- cannot insert NULL into

INSERT INTO EMP05 VALUES ( NULL, 'JONES', 'MANAGER', 20 );

/*

bdesc - 기본설명

mem - ddl 자동편집 회원테이블

*/

-- 문제풀이

DESC BOOK;

DROP TABLE BOOK;

SELECT  * FROM BOOK;

-- 회원 도서 주문관리 SYSTEM

-- 도서테이블

 CREATE TABLE BOOK 

 (

    BNO NUMBER(3) PRIMARY KEY,

    BNAME VARCHAR2(20) NOT NULL,

    BDESC VARCHAR2(4000),

    BPRICE NUMBER

 );

 

 -- 컬럼주석( 테이블 내부(컬럼)에 주석이 달림

COMMENT ON COLUMN BOOK.BNO IS '도서번호';

COMMENT ON COLUMN BOOK.BNAME IS '도서이름';

COMMENT ON COLUMN BOOK.BDESC IS '도서설명';

COMMENT ON COLUMN BOOK.BPRICE IS '도서가격';

COMMIT;

INSERT INTO BOOK VALUES ( 101, '오라클1', '기본', 1000 );

INSERT INTO BOOK VALUES ( 102, '오라클2', '기본', 2000 );

INSERT INTO BOOK VALUES ( 201, '자바1', '기본', 2000 );

COMMIT;

SAVEPOINT BOOKVALUEINSERT; --책추가

    

-- 회원테이블

CREATE TABLE MEM

(

    MEMID VARCHAR2(20) PRIMARY KEY,

    BNAME VARCHAR2(20),

    BEMADDR VARCHAR2(20)

);

COMMENT ON COLUMN MEM.MEMID IS '회원ID';

COMMENT ON COLUMN MEM.BNAME IS '회원이름';

COMMENT ON COLUMN MEM.BEMADDR IS '회원주소';

SAVEPOINT MEMTABLECREATE; --회원 테이블 생성

COMMIT;

INSERT INTO MEM ( MEMID, BNAME, BEMADDR ) VALUES ( 'hong', '홍자바', '서울' );

SAVEPOINT MEMINSERT; --회원 추가

COMMIT;

DESC MEM;

SELECT * FROM MEM;

-- 주문테이블

CREATE TABLE BORDER

(

    BON NUMBER PRIMARY KEY, -- 주문번호(1,2,...일련번호)

    BNO NUMBER, -- 주문도서번호

    MEMID VARCHAR2(20), -- 주문자 ID

    OQTY NUMBER -- 주문도서수량

);

COMMENT ON COLUMN BORDER.BON IS '주문번호';

COMMENT ON COLUMN BORDER.BNO IS '주문도서번호';

COMMENT ON COLUMN BORDER.MEMID IS '주문자ID';

COMMENT ON COLUMN BORDER.OQTY IS '주문도서수량';

SAVEPOINT BORDERTABLECREATE; --주문 테이블 생성

COMMIT;

DESC BORDER;

SELECT * FROM BORDER;

/*

홍자바가 도서번호 101인 도서 2권 주문(INSERT)

주문한 회원명 및 도서명을 주문수량과 함계 출력(조인)

주문한 총도서수량을 도서명(혹은 도서번호)별로 그룹지어 출력(조인)

주문한 총도서수량을 도서명(혹은 도서번호)/회원별로 그룹지어 출력(조인)

*/

--문제1 : 홍자바가 도서번호 101인 도서 2권 주문(INSERT)

INSERT INTO BORDER VALUES (1, 101, 'hong', 2);

INSERT INTO BORDER VALUES (2, 101, 'hong', 3);

INSERT INTO BORDER VALUES (3, 201, 'hong', 4);

INSERT INTO BORDER VALUES (4, 301, 'hong', 10);

INSERT INTO BORDER VALUES (5, 401, 'hong', 10);

SELECT nvl(MAX(ono), 0) + 1 FROM border;

SELECT * FROM BORDER;

/* 특정행 삭제

DELETE FROM BORDER

WHERE BON=2;

*/

--문제2 : 주문한 회원명 및 도서명을 주문수량과 함께 출력(조인)

--주문테이블(공통컬럼 모두 존재)

SELECT M.BNAME, B.BNAME, O.OQTY 

FROM BOOK B, MEM M, BORDER O

WHERE O.MEMID=M.MEMID

AND O.BNO=B.BNO;

SELECT M.BNAME, B.BNAME, O.OQTY 

FROM BOOK B, MEM M, BORDER O

WHERE O.MEMID=M.MEMID;

select * from book, mem m, border;

--문제3 : 주문한 총도서수량을 도서명(혹은 도서번호)별로 그룹지어 출력(조인)

--그륩바이에 도서명이 있기 때문에 select 절에 b.bname이 올수 있음

SELECT B.BNAME, sum(O.OQTY)

FROM BOOK B, BORDER O

WHERE O.BNO=B.BNO

GROUP BY ROLLUP(B.BNAME);

commit;

SELECT * FROM BOOK; -- 도서 테이블        

SELECT * FROM MEM; -- 회원 테이블    

SELECT * FROM BORDER; -- 주문 테이블

--주문한 총도서수량을 도서명(혹은 도서번호)/회원별로 그룹지어 출력(조인)

--회원명으로 하위그룹으로 소분류

--SELECT 컬럼순서와  GROUP BY 컬럼순서를 일치

SELECT B.BNAME, M.BNAME,  sum(O.OQTY) 총도서수량

FROM MEM M, BOOK B, BORDER O

WHERE O.BNO=B.BNO

GROUP BY B.BNAME, M.BNAME

ORDER BY B.BNAME, M.BNAME;

SELECT B.BNAME, M.BNAME,  sum(O.OQTY) 총도서수량

FROM MEM M, BOOK B, BORDER O

GROUP BY B.BNAME, M.BNAME

ORDER BY B.BNAME, M.BNAME;

commit;

SELECT * FROM BOOK; -- 도서 테이블        

SELECT * FROM MEM; -- 회원 테이블    

SELECT * FROM BORDER; -- 주문 테이블