Oracle 10일차 - 제약조건(CONSTRAINT)

2020.08.18(화)

 

* 복습

DML (데이터 조작언어)

= SELECT

   => 형식)

        SELECT * | column1, column2...

        FROM table_name

        [

           WHERE 조건문

           GROUP BY 그룹컬럼

           HAVING 그룹조건

           ORDER BY 컬럼명 ASC|DESC

        ]

===================================

 = INSERT (데이터 추가)

   => 형식)

   1) INSERT INTO table_name VALUES(전체 데이터가 첨부)

       => 전체 데이터 추가

   2) INSERT INTO table_name(컬럼명, 컬럼명...)

       VALUES(값, 값...)

       => 필요한 데이터만 추가

= UPDATE (데이터 수정)

   UPDATE table_name SET

   WHERE 조건문

= DELETE (데이터 삭제)

   DELETE FROM table_name

   WHERE 조건문

========================== 반드시 COMMIT을 사용

*** 자바에서 DML은 AutoCommit

 

 테이블 생성

 1) 기존의 테이블 복사 (CATS)

    형식)

      CREATE TABLE table_name

      AS SELECT ~~

 2) 새롭게 생성

     형식)

         CREATE TABLE table_name(

               컬럼명 데이터형 [제약조건(여러개 사용이 가능)],

               컬럼명 데이터형 [제약조건(여러개 사용이 가능)],

               컬럼명 데이터형 [제약조건(여러개 사용이 가능)]

               [제약조건..],

               [제약조건..]

         );

        = 컬럼명 뒤에 첨부 (컬럼레벨) => NOT NULL, DEFAULT

           => 테이블과 동시에 생성

        = 나중에 첨부 (테이블레벨) (권장)

           => 테이블 생성후에 나중에 생성

 

1) 테이블 생성 반드시 필요한 요소

   = 데이터형

   = 제약조건의 종류

   = 같은 데이터베이스(XE)에서는 테이블은 유일값이다

   = 시작은 문자(영문, 한글)로 시작

   = 숫자를 사용이 가능 (앞에 사용금지)

   = 테이블명은 30byte(한글 15자)

      *** 영문으로 사용시 테이블명은 대문자로 저장

   = 특수문자 단어가 두 개 이상일 경우에 주로 (_)

   = 키워드는 사용할 수 없다

 

   1. 데이터형

      문자저장 ====> 자바(String으로 매칭)

         CHAR(1~2000byte) : 고정바이트

             => 글자수가 동일할 때 주로 설정

                  성별 (남자, 여자)

             sex CHAR(4) : 한글 2byte사용

         VARCHAR2(1~4000byte) : 가변바이트

             => 일반적으로 사용하는 문자저장

             name VARCHAR2(34)

         CLOB(4G) : 가변바이트

             => 줄거리, 내용...

             story CLOB

      숫자저장

         NUMMBER(정수값) : 실제 정수 ==> NUMBER(4)

                                                         1~4(1~9999)

         NUMBER(정수값, 정수값) : 정수, 실수

         NUMBER => 자리수 14자리

      날짜저장

         DATE : 시스템의 시간

         TIMESTAMP : DATE확장 (기록경주)

      기타저장

         BLOB : 동영상, 그림, 사진 => 바이너리로 저장

         BFILE : 동영상, 그림, 사진 => 파일형태로 저장

 

 

   2. 오라클 (데이터베이스) => 정형화된 데이터

      => 사이트에 필요한 데이터만 저장 (제약조건)

      ***** 제약조건

      = NOT NULL => 데이터의 NULL값을 허용하지 않는다

         name VARCHAR2(34) NOT NULL

         값이 없으면 오라클에 저장이 안됨

      = UNIQUE => 유일값 (중복이 없는 값 => NULL값 허용)

         => 후보키 (이메일, 전화번호, 주민번호)

         email VARCHAR2(200) UNIQUE

      = PRIMARY KEY : NOT NULL + UNIQUE

         => ID, 게시물번호, 영화번호

         => 권장 : 모든 테이블은 PRIMARY KEY를 한개 이상 가지고 있다

         id VARCHAR2(20) PRIMARY KEY

      = FOREIGN KEY : 외래키 => 참조 (다른 테이블의 컬럼값

         => 정규화 => 테이블을 여러개 만들기 때문에

         => emp:deptno, dpet:deptno : JOIN

              deptno NUMBER(2) FOREIGN KEY

              REFERENCES dept(deptno)

      = CHECK : 지정된 데이터만 첨부

         => 라디오버튼, 콤보박스 사용

         sex VARCHAR2(4) CHECK(sex IN('남자', '여자'));

         => 부서명, 직위

      = DEFAULT : 저장값이 없는 경우에 자동으로

                       설정된값을 첨부

         regdate DATE DEFAULT SYSDATE

 

연습용 테이블

emp / dept

 

emp

 = empno : PK

 = ename : NN

 = job : CHECK

 = mgr : empno 참조 FK

 = hiredate : DATE => DEFAULT

 = sal : NOT NULL

 = comm

 = deptno : FK

 

dpet

 = deptno : PK

 = dname : CHECK

 = loc : CHECK

SQL> SELECT DISTINCT job FROM emp;

JOB
------------------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

SQL> SELECT dname, loc FROM dept;

DNAME                        LOC
---------------------------- --------------------------
ACCOUNTING                   NEW YORK
RESEARCH                     DALLAS
SALES                        CHICAGO
OPERATIONS                   BOSTON

 

제약조건 NOT NULL => DEFAULT => PRIMARY KEY => FOREIGN => CHECK

이름 (제약조건) 부여

테이블명_컬럼명_제약조건의 약자

약자

  PRIMARY KEY => PK

  NOT NULL => NN

  CHECK => CK

  FOREIGN KEY => FK

  UNIQUE => UK

 

CREATE TABLE dept_test(
    deptno NUMBER(2),
    dname VARCHAR2(20),
    loc VARCHAR2(20),
    CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno),
    CONSTRAINT dept_dname_ck CHECK(dname IN('ACCOUNTING', 'RESEARCH', 'SALES', 'OPERATIONS')),
    CONSTRAINT dept_loc_ck CHECK(loc IN('NEW YORK', 'DALLAS', 'CHICAGO', 'BOSTON'))
);
    
CREATE TABLE emp_test(
    empno NUMBER(4),
    ename VARCHAR2(34) CONSTRAINT emp_ename_nn NOT NULL,
    job VARCHAR2(20),
    mgr NUMBER(4),
    hiredate DATE DEFAULT SYSDATE,
    sal NUMBER(7,2) CONSTRAINT emp_sal_nn NOT NULL,
    comm NUMBER(7,2),
    deptno NUMBER(2),
    CONSTRAINT emp_empno_pk PRIMARY KEY(empno),
    CONSTRAINT emp_mgr_fk FOREIGN KEY(mgr)
    REFERENCES emp_test(empno),
    CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno)
    REFERENCES dept_test(deptno),
    CONSTRAINT emp_job_ck CHECK(job IN('CLERK', 'SALESMAN', 'PRESIDENT', 'MANAGER', 'ANALYST'))
    );

 

DROP TABLE freeboard;

테이블 freeboard 만들기 연습

컬럼명	no	name	email	subject	content	pwd	regdate	hit
PK/FK/NN	PK	NN	UK	NN	NN	NN	DEFAULT SYSDATE	DEFAULT 0
데이터형	NUMBER	VAR(34)	VAR(200)	VAR(4000)	CLOB	VAR(10)	DATE	NUMBER

CONSTRAINT fb_email_uk UNIQUE(email)

 

* NOT NULL과 DEFAULT는 컬럼 뒤에 붙는다

DROP TABLE freeboard;
CREATE TABLE freeboard(
    no NUMBER,
    name VARCHAR2(34) CONSTRAINT fb_name_nn NOT NULL,
    email VARCHAR2(200),
    subject VARCHAR2(4000) CONSTRAINT fb_subject_nn NOT NULL,
    content CLOB CONSTRAINT fb_content_nn NOT NULL,
    pwd VARCHAR2(10) CONSTRAINT fb_pwd_nn NOT NULL,
    regdate DATE DEFAULT SYSDATE,
    hit NUMBER DEFAULT 0,
    CONSTRAINT fb_no_pk PRIMARY KEY(no),
    CONSTRAINT fb_email_uk UNIQUE(email)
);

INSERT INTO freeboard(no, name, email, subject, content, pwd)
VALUES(1, '홍길동', 'hong1@sist.co.kr', '오라클 제약조건', '제약조건=> 게시판', '1234');
INSERT INTO freeboard(no, name, email, subject, content, pwd)
VALUES(2, '홍길동', 'hong2@sist.co.kr', '오라클 제약조건', '제약조건=> 게시판', '1234');
INSERT INTO freeboard(no, name, email, subject, content, pwd)
VALUES(3, '홍길동', 'hong3@sist.co.kr', '오라클 제약조건', '제약조건=> 게시판', '1234');
INSERT INTO freeboard(no, name, email, subject, content, pwd)
VALUES(4, '홍길동', 'hong4@sist.co.kr', '오라클 제약조건', '제약조건=> 게시판', '1234');
INSERT INTO freeboard(no, name, email, subject, content, pwd)
VALUES(5, '홍길동', 'hong5@sist.co.kr', '오라클 제약조건', '제약조건=> 게시판', '1234');

INSERT INTO freeboard VALUES(6, '홍길동', 'hong6@sist.co.kr',
'자유 게시판만들기', '게시판 내용 설정', '1234', SYSDATE, 0);

 

 

*연습

CREATE TABLE 제품(
제품번호 VARCHAR2(12),
제품명 VARCHAR2(100),
제품단가 NUMBER CONSTRAINT 제품_단가_nn NOT NULL,
CONSTRAINT 제품_번호_pk PRIMARY KEY(제품번호),
CONSTRAINT 제품_제품명_uk UNIQUE(제품명),
CONSTRAINT 제품_단가_ck CHECK(제품단가>0)
);

CREATE TABLE 전표상세(
    전표번호 VARCHAR2(12),
    제품번호 VARCHAR2(12),
    수량 NUMBER CONSTRAINT 전표_수량_nn NOT NULL,
    단가 NUMBER CONSTRAINT 전표_단가_nn NOT NULL,
    금액 NUMBER CONSTRAINT 전표_금액_nn NOT NULL,
    CONSTRAINT 전표_번호_pk PRIMARY KEY(전표번호),
    CONSTRAINT 전표_번호_fk FOREIGN KEY(제품번호)
    REFERENCES 제품(제품번호),
    CONSTRAINT 전표_총액_ck CHECK(금액>0)
);

CREATE TABLE 판매전표(
    전표번호 VARCHAR2(12),
    판매일자 DATE CONSTRAINT 판매_일자_nn NOT NULL,
    고객명 VARCHAR2(34) CONSTRAINT 판매_고객_nn NOT NULL,
    총액 NUMBER,
    CONSTRAINT 판매_번호_pk PRIMARY KEY(전표번호),
    CONSTRAINT 판매_번호_fk FOREIGN KEY(전표번호)
    REFERENCES 전표상세(전표번호),
    CONSTRAINT 판매_총액_ck CHECK(총액>0)
);

 

NULL값 허용 -> UNIQUE, CHECK

SQL> SELECT owner, constraint_name, constraint_type, status FROM user_constraints
  2  WHERE table_name='전표상세';

OWNER CONSTRAINT_NAME CONSTRAINT_TYPE   STATUS
HR    전표_수량_NN             C       ENABLED
HR    전표_단가_NN             C       ENABLED
HR    전표_금액_NN             C       ENABLED
HR    전표_총액_CK             C       ENABLED
HR    전표_번호_PK             P       ENABLED
HR    전표_번호_FK             R       ENABLED

C => CHECK, NOT NULL

P => PRIMARY KEY

R => FOREIGN KEY

 

DROP TABLE 판매전표;
DROP TABLE 전표상세;
DROP TABLE 제품;

CREATE TABLE 제품(
    제품번호 VARCHAR2(12),
    제품명 VARCHAR2(100),
    제품단가 NUMBER
);

CREATE TABLE 전표상세(
    전표번호 VARCHAR2(12),
    제품번호 VARCHAR2(12),
    수량 NUMBER,
    단가 NUMBER,
    금액 NUMBER
);

CREATE TABLE 판매전표(
    전표번호 VARCHAR2(12),
    판매일자 DATE,
    고객명 VARCHAR2(34),
    총액 NUMBER
);

-- 테이블을 생성후에 제약조건을 설정 (NN만 MODIFY고 나머지는 ADD)

ALTER TABLE 제품 MODIFY 제품단가 CONSTRAINT 제품_단가_nn NOT NULL;
ALTER TABLE 제품 ADD CONSTRAINT 제품_번호_pk PRIMARY KEY(제품번호);
ALTER TABLE 제품 ADD CONSTRAINT 제품_제품명_uk UNIQUE(제품명);
ALTER TABLE 제품 ADD CONSTRAINT 제품_단가_ck CHECK(제품단가>0);
ALTER TABLE 전표상세 ADD CONSTRAINT 전표_제품번호_fk FOREIGN KEY(제품번호)
REFERENCES 제품(제품번호);

 

* 제약조건

1) NOT NULL : NULL값을 허용하지 않는다 => 반드시 입력값을 추가

  = 컬럼명 데이터형 NOT NULL

     (권장 => 제약조건에 대해 수정, 삭제가 용이)

  = 컬럼명 데이터형 CONSTRAINT 제약조건명 NOT NULL

  = ALTER TABLE 테이블명 MODIFY 컬럼명 CONSTRAINT 제약조건명 NOT NULL

 

2) UNIQUE : 중복없는 데이터 첨부 (NULL값 허용)

  = 후보키 (주민번호(X), 전화번호, 이메일)

  = 컬럼명 데이터형 UNIQUE

  = 컬럼명 데이터형 CONSTRAINT 제약조건명 UNIQUE(컬럼명)

                                           테이블명_컬럼명_uk

  = 컬럼명 데이터형,

    CONSTRAINT 제약조건명 UNIQUE(컬럼명)

  = 테이블 제작후에 제약조건 설정

    ALTER TABLE 테이블명 ADD 컬럼명 CONSTRAINT 제약조건명 UNIQUE(컬럼명)

 

3) PRIMARY KEY : UNIQUE + NOT NULL

= 모든 테이블에 반드시 한개이상의 PRIMARY KEY가 존재

   => 이상 현상 방지 (무결성)

   => 수정, 삭제 => 원하지 않는 데이터가 적용

= 컬럼명 데이터형 PRIMARY KEY

 컬럼명 데이터형 CONSTRAINT 제약조건명 PRIMARY KEY(컬럼명)

 ALTER TABLE 테이블명 ADD 컬럼명 CONSTRAINT 제약조건명 PRIMARY KEY(컬럼명)

*** 컬럼 뒤에 생성 => NOT NULL, DEFAULT

*** 컬럼을 다 생성후에 테이블 마지막에 첨부 => PRIMARY, FOREIGN, CHECK, UNIQUE

CREATE TABLE 제품(         컬럼뒤에 붙여야함
    제품번호 VARCHAR2(12) [제약조건], ==> NOT NULL, DEFAULT
    제품명 VARCHAR2(100),
    제품단가 NUMBER
    [제약조건] ==> PRIMARY, FOREIGN, CHECK, UNIQUE
);

*** PRIMARY 사용하는 곳 => 일반적으로 숫자 (MAX+1, SEQUENCE(자동증가번호))

     특별한 경우 (ID)

 

4) CHECK : 지정된 데이터만 첨부

   = 장르, 성별, 부서명, 메뉴

   = 입력시에 => 라디오버튼, 콤보박스

   = 컬럼명 데이터형 CHECK(컬럼명 IN(10,20,30))

   = 컬럼명 데이터형 CONSTRAINT 제약조건명 CHECK(컬럼명 IN(10,20,30))

      = 권장사항 (이름부여 => 찾아서 수정, 삭제 가능)

 

5) FOREIGN KEY (참조키)

   = 참조하는 테이블의 값을 벗어나면 안된다

   = 게시판, 댓글

   ==============

   게시판     댓글

      번호       게시물 번호

        1               3

        2               3

        3               3 
   = 컬럼명 데이터형 CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 참조항 테이블명(컬럼명)

 

6) DEFAULT : 추가가 없는 경우에 자동으로 추가되는 값

  = regdate DATE DEFUALT SYSDATE

  = hit NUMBER DEFAULT 0

 

* select, insert, update, delete, join, subquery, view, sequence, pl/sql