Oracle 제약조건

제약조건(Constrain 조건) 종류

 

Not null / Unique / primary key / foreign key / check / default / index

 

 

 

---

1. Unique 와 Not null

 

 

member2라는 테이블을 생성 시 unique 를 적용해 보자
SQL> create table member2(
  2  name varchar(20)
  3  , phone varchar(13) unique
  4  , age number
  5  );

 

 

 

 

데이터를 생성해준다.

SQL> insert into member2(name, phone, age) values('batman','010',38);
1 row created.

 

 

 

 

같은 데이터를 넣게되면 오류가 발생한다.
SQL> insert into member2(name, phone, age) values('batman','010',38);
insert into member2(name, phone, age) values('batman','010',38)
*
ERROR at line 1:
ORA-00001: unique constraint (ADAM.SYS_C007004) violated 

 

 

 

 

그러나 unique 항목을 빼고 데이터를 입력하게되면 생성이 잘된다.

SQL> insert into member2(name, age) values('batman',38);
1 row created.

 

 

 

 

unique 제약조건은 null 을 허용해버린다.
따라서 member3를 생성하면서 unique 하면서 null을 허용하지 않는 제약조건을 걸자

SQL> create table member3(
  2    name varchar(20)
  3   , phone varchar(13) unique not null
  4   , age number
  5  );

 

---

 

 

2. primary key (Unique + Not null)

 

두개의 제약조건 (unique +not null = primary key)을 합쳐놓은 주키 제약조건을 많이 사용

 

 

 

SQL>create table member4(
  2    member4_id  number  primary key
  3   , name varchar(20)
  4   , age number 
  5   , phone varchar(13)
  6   );

 

 

 

 

※주키 제약조건이 설정된 컬럼에 값을 넣기

 

주키에 들어갈 숫자값 데이터는 개발자가 정하는게 아니라, 시스템에 의해 결정되어야 한다.
오라클에는 테이블에 숫자값을 관리해주는 전담 테이블을 지원하는데 이 객체를 가리켜 시퀀스라 한다. (sequence)

시퀀스 생성 (DDL)

 

 

 

 

시스템으로 접속해 시퀀스 권한을 생성한다.

SQL> conn system/1234
Connected.

SQL> show user
USER is "SYSTEM"

SQL> grant create sequence to adam;
Grant succeeded.

 

 

 

 

다시 사용자에 접속해 시퀀스 생성하기

SQL> conn adam/1234
Connected.

SQL> show user
USER is "ADAM"

SQL> create sequence  seq_member4
  2  increment by 1
  3  start with 1;

 

 

 

 

시퀀스는 입력된 순서대로 부여된다.

SQL> insert into member4(member4_id, name, age, phone)
  2  values(seq_member4.nextval,'batman',38,'010');

 

 

 

---

 

3. default

 

default 제약조건은 칼럼의 기본값을 부여할수 있는 제약조건

값을 넣지 않아도 기본으로 생성이된다.

 

 

 

SQL> create table member5(
  2  member5_id number primary key
  3  , name varchar(20)
  4  , sal number default 0
  5  , hiredate date default sysdate - 오늘날짜를 의미함
  6  );

 

 

 

 

기존 member5 테이블에 회원 포인트 컬럼을 추가하고, 회원가입과 동시에 5000 포인트를 기본적으로 주고 싶다면?
SQL> alter table member5
  2  add point number default 5000;

 

 

 

---

3. check

 

check 제약조건은 어떤 값을 받아들일지 조건을 부여하는 제약조건
즉 지정한 조건만 받아들임

 

 

 

SQL> create table member6(
  2  member6_id number primary key
  3  , name varchar(20)
  4  , sal number default 0
  5  , age number
  6  , constraint chk_member6 check (age>=18) - 18세 미만일 경우 오류 발생
  7  );

 

 

 

member6 테이블에 성별 컬럼을 추가하되, 반드시 '남 or '여'
SQL> alter table member6
  2  add gender varchar(10) check(gender='남' or gender='여');

 

 

---

4. foreign key

 

테이블 생성후 시간이 지나면 레코드가 점점 쌓인다. (데이터량 증가)
데이터 중복이 발생하여 결점이 생긴다.
데이터의 무결성을 위해 물리적으로 분리켜야 한다. (정규화)
정규화시키면 테이블들이 여러개가 출현이된다.
이때 물리적으로 분리된 테이블들을 서로 관련성을 부여하기 위해 사용되는 키가 제약조건 중 foreign key 이다.
외래키를 통해 물리적으로 분리된 테이블을 마치 하나 처럼 보여줄 수 있는데, 이러한 쿼리문을 가리켜 조인문이라고 한다

 

 

 

 

* dept 는 부모 테이블

* emp 는 자식 테이블

 

직접 테이블을 생성하여 실습해보자.

 

 

부모 테이블을 생성한다.

SQL> create table category(
  2  category_id  number primary key
  3  , name varchar(20)
  4  );

Table created.

 

 

 

 

부모 테이블에 시퀀스를 부여해준다.
SQL> create sequence  seq_category
  2  increment by 1
  3  start with 1
  4  /

Sequence created.

 

 

 

자식 테이블을 생성하고 외래키를 부여한다.
SQL> create  table product(
  2    product_id  number primary key
  3  , category_id number
  4  , name varchar(20)
  5  , price number default 0
  6  , constraint fk_category  foreign key(category_id) references category(category_id)
  7  )
  8  /

Table created.

 

 

 

 

 

자식 테이블에 시퀀스를 부여해준다.
SQL> create sequence seq_product
  2  increment by 1
  3  start with 1;

Sequence created.

 

 

 

 

부모 테이블에 데이터를 삽입하여 준다.
SQL> insert into category(category_id, name) values(seq_category.nextval,'top');
1 row created.
SQL> insert into category(category_id, name) values(seq_category.nextval,'down');
1 row created.
SQL> insert into category(category_id, name) values(seq_category.nextval,'accessory');
1 row created.

 

 

 

 

자식 테이블에 데이터를 삽입하여 준다.

SQL> insert into product(product_id, category_id, name, price)
  2   values(seq_product.nextval,1,'가디건',3000);

1 row created.

 

 

※이때 이미 자식테이블이 category_id=1 외래키를 가지고 있기 때문에 부모테이블에서 category_id=1 번을 삭제할 수 없다.

 

 

 

---

 

 

조인문이란?

 

정규화에 의해 물리적으로 분리된 테이블을 마치 하나의 테이블처럼
SQL> --조회하는 select 쿼리문을 join 이라 한다.

 

 

 

 

 

 

 

join 의 사용 예제

 

* 조건문 이용한 검색

 

 

dept, empt를 합쳐보자
형식 : select 가져올 컬럼
        from 조인대상테이블 A, 조인대상테이블B...
        where 합칠때 조건

 

 

 

 

emp 와 dept 에서 사원이름, 업무, 부서이름, 위치를 조회해보자

SQL> select ename, job, dname, loc
  2  from emp, dept;

  3  where emp.deptno=dept.deptno

 

<결과>

 

 

 

 

 

최대임금을 받는 사원의 입사날짜, 금여, 사원이름, 부서명을 조회해보자

SQL> select hiredate, sal, ename, dname

  2  from emp , dept 

  3  where emp.deptno = dept.deptno

  4  and sal=(select max(sal) from emp)

 

<결과>

 

 

 

 

최소급여를 받는 사원과 같은 부서에서 근무하는 모든 사원명, 부서명을 출력하세요

SQL> select ename, dname

  2  from emp e , dept d

  3  where e.deptno = d.deptno

  4  and e.deptno=(select deptno from emp where sal=(select min(sal) from emp));

 

<결과>

 

 

 

 

 

이름에 "K"자가 들어가는 사원들 중 가장 급여가 적은 사원의 부서명,사원명,급여를 출력하세요.

SQL> select ename, dname, sal

  2  from emp e , dept d

  3  where e.deptno = d.deptno

  4  and sal=(select min(sal) from emp where ename like '%K%');

 

<결과>

 

 

 

 

 

커미션 계약조건이 없는(커미션이 비어있음) 사원중  입사일이 가장 빠른 사원의 부서명, 사원명,입사일을 출력하세요.

SQL> select ename, dname, sal

  2  from emp e , dept d

  3  where e.deptno = d.deptno

  4  and hiredate=(select min(hiredate) from emp where comm is null);

 

<결과>

 

 

 

 

 

위치가 시카고인 부서에 속한 사원들의 이름과 부서명을 출력하세요.

SQL> select ename, dname, sal

  2  from emp e , dept d

  3  where e.deptno = d.deptno

  4  and e.deptno=(select deptno from dept where loc='CHICAGO');

 

<결과>

 

 

ALLNEN 의 부서명, 급여, 부서번호를 출력하세요

SQL> select dname, sal, e.deptno

  2  from emp e , dept d

  3  where e.deptno = d.deptno

  4  and ename='ALLEN';

 

<결과>

 

 

* 그룹별로 묶기(inner join)

 

원칙 : group by 에 명시한 컬럼만이 select 대상이 될 수 있다.

예외 : 단 집계한수는 그러하지 않다.(이미 그룹화 되어있기 때문에)

 

 

부서의 종류를 출력하시오

SQL> select deptno from emp

  2  group by deptno

 

<결과>

 

 

부서번호 별 총 사원수 을 출력하시오

SQL> select deptno, count(*) as empcount

  2  group by deptno;

 

<결과>

 

 

 

부서별 합, 총수, 최대값, 최소값, 평균값을 출력하시오

SQL> select deptno, sum(sal), count(*), max(sal), min(sal), avg(sal) 

  2  from emp
  3  group by deptno;

 

<결과>

 

부서명, 소속사원수를 출력하시오

SQL> select dname, count(ename)
  2  from emp e, dept d

  3  where e.deptno=d.deptno

  4  group by dname

 

<결과>

※여기서 잠깐!!!! 무언가 빠져있다.

※ select * from dept; 를 조회해보면 위의 검색에서 deptno 40이 빠져있는것을 알수 있다.

※ 이를 해결하기 위해 outer 조인을 사용하자

dept 테이블을 전부 조회한 결과

 

** 그룹별로 묶기(outer join)

 

부서명, 소속사원수를 출력하시오

SQL> select dname, count(ename)
  2  from dept d left outer join emp e

  3  on d.deptno=e.deptno

  4  group by dname;

 

<결과>

 

중복을 제거한 부서번호를 가져오자

SQL> select distinct deptno from emp;

 

<결과>

※ distinct는 그룹화한것이 아니기 때문에 집계함수와 함께 사용이 불가능하다

 

 

 

 

---

 

5. Index

오프라인 상의 색인을 DMBS에서도 도입 하였다.
색인인 즉,  index를 이용하면 검색이 빠르다.
성능을 높이기 위한 방법이다.

 

....(MYSQL index 제약조건 게시물에서 계속..)

 

 

 

 

 

 

 

---

 

※ 기존 데이터파일 용량 늘리는 법

 

SQL> alter database datafile 'C:\oraclexe\app\oracle\oradata\XE\javaspace.dbf'

  2  resize 10m;

 

※ 페이지 사이즈를 늘려서 하나의 페이지에 보이게 하는법

 

SQL> set pagesize 30