DB 연습하기 - 07

오늘·2021년 4월 26일
오라클
0

DB

목록 보기
7/14

데이터 무결성 제약조건

테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서
테이블을 생성할 때 각 컬럼에 대해서 정의하는 여러 가지 규칙

종류

  1. not null (비워져있으면 안된다)
  2. unique (중복 값은 입력되지 않는다)
  3. primary key (unique 제약조건 + not null 제약 조건)
  4. foreign key (외래키. 다른 테이블을 참조하겠다)
  5. check (범위를 지정하고 싶을 때 사용한다)

그 외..

기본키가 여러개 있으면 -> '복합키' 라 한다
외래키 : Refernece integrity 라서 제약조건 검색할때 R로 표시

데이터 딕셔너리 구조 살펴보기

-- 현재 사용자의 DB 제약조건 보기
desc user_constraints;
-- 현재 데이터베이스 내 유저들 중 (owner)에 대한 제약조건만 보겠다
select owner from user_constraints;

not null 제약조건 걸기

-- 만들어져있는 emp01 이 있다면 삭제
drop table emp01;
-- not null 제약조건을 설정하고 값 넣어보기
create table emp01 (
    empno number(4) not null,
    ename varchar2(10) not null,
    job varchar2(9),
    deptno number(2) );
-- 오류 발생. not null 자리에 null을 넣었으니
insert into emp01 values(null, null, '사원', 10);
insert into emp01 values(1001, null, '사원', 10);
-- 이런식으로 작성해야한다. not null 자리에는 값 입력이 필수
-- not null 제약조건이 없다면 null 넣어도 상관없음
insert into emp01 values(1001, '홍길동', '사원', 10);
insert into emp01 values(1002, '김자바', null, null);
select * from emp01;

unique 제약조건 걸기

drop table emp01;
-- unique 제약조건 설정하고 값 넣어보기
create table emp01 (
    empno number(4) unique,
    ename varchar2(10) not null,
    job varchar2(9),
    deptno number(2) );

insert into emp01 values(1001, '홍길동', '사원', 10);
-- unique 제약조건을 줬다면, 값이 중복되면 안된다는 뜻이다
insert into emp01 values(1001, '홍길동', '사원', 10);    -- 그래서 이 경우는 오류

-- 해당 자리에 unique 제약조건을 안걸어줬다면 겹쳐도 상관 없다.
insert into emp01 values(1002, '홍길동', '사원', 10);   
-- null 값은 여러번 넣어도 가능하다
insert into emp01 values(null, '김자바', null, null);
insert into emp01 values(null, '김자바1', null, null);

제약조건 오류 발생시

제약조건에 대한 오류가 발생했다면, 오라클이 오류난 제약조건을 보여준다. 하지만 가독성이 떨어지기 때문에 명시적으로 사용자가 변경하고 싶다면?

drop table emp01;
-- 테이블 생성할 때 constraint로 설정하면 된다
create table emp01(
    empno number(4) constraint emp01_empno_uk unique,
    ename varchar2(10) constraint emp01_ename_nn not null,
    job varchar2(9),
    deptno number(2) );

위와 같이 작성하고 오류를 내보자

-- null 이 들어가면 안되는 곳에 null을 넣었을 때 나오는 문구는 아래와 같다
-- cannot insert NULL into ("SYSTEM"."EMP01"."ENAME")
-- system 의 emp01에 ename 구역에서 에러가 났다
insert into emp01 values(1001, null, '사원', 10);
-- unique 자리에 중복 값이 들어갔을 때
-- unique constraint (SYSTEM.EMP01_EMPNO_UK) violated
-- system 에 emp01의 empno_uk 오류가 났다
insert into emp01 values(1001, '홍길동', '사원', 10);
insert into emp01 values(1001, '홍길동', '사원', 10);

설정한 이름이 출력되면서 좀 더 오류 찾기가 쉬워졌다!

primary key 제약조건

-- primary key : unique 제약조건과 not null 제약 조건을 함께
drop table emp01;
create table emp01(
    empno number(4) constraint emp01_empno_pk primary key,
    ename varchar2(10) constraint emp01_ename_nn not null,
    job varchar2(9),
    deptno number(2) );
insert into emp01 values(1001, '홍길동', '사원', 10);
-- 같은값 두 번 넣으면 unique 처럼 오류 발생
-- unique constraint (SYSTEM.EMP01_EMPNO_PK) violated
insert into emp01 values(1001, '홍길동', '사원', 10);
-- null이 들어가면 안되는데 들어가서 오류
-- cannot insert NULL into ("SYSTEM"."EMP01"."EMPNO")
insert into emp01 values(null, '홍길동', '사원', 10);

references 외래키 연결

-- 직원 테이블 생성
create table employee (
    empno number(4) constraint employee_empno_pk primary key,
    ename varchar2(16),
    salary  number(6),
    birthday  date );
    
-- 기술 테이블 생성
create table skill (
    snumber number(4) constraint skill_sknumber_pk primary key,
    sname varchar2(30) constraint skill_sname_nn not null,
    -- references : 관계 맺기
    -- 부모인 employee의 empno와 연결하겠다  
    empno number(4)constraint skill_empno_fk references employee(empno) );
    

-- 직원 테이블에 자료 3개 넣기
insert into employee values(1001, '김자바', 1, '90/11/11');
insert into employee values(1002, '홍길동', 2, '91/10/10');
insert into employee values(1003, '이초코', 3, '93/12/03');

-- 기술 테이블에 자료 3개 넣기
insert into skill values(100, '프로그램', 1003);
insert into skill values(200, '디자인', 1001);
insert into skill values(300, '회계', 1002);
-- 외래키 에러. 관계맺기한 employee에 empno에는 1005라는 건 없기때문에 오류난다.
insert into skill values(100, '프로그램', 1005);

위와 같이 references로 참조했을 경우, 부모에서 미리 만들어놓지 않은 값으로 연결하려 하면 오류난다.

-- 프로젝트 테이블 생성
create table project(
    pno number(4) constraint project_pno_pk primary key,
    pcontent varchar2(20),
    endDate date,
    startDate date);
    
-- 담당 테이블
create table work(
    worknumber number(4),
    -- employee테이블의 empno와 외래키 관계맺기
    empno number(4) constraint work_empno_fk references employee(empno),  
    pno number(4) constraint work_pno_fk references project(pno) );
    
-- 프로젝트 테이블에 자료 3개
insert into project values (5000, '서울시', '21/12/30', '21/05/01');
insert into project values (4000, '삼성', '21/11/20', '21/03/05');
insert into project values (6000, 'IBM', '22/02/13', '21/04/11');
-- 담당 테이블에 자료 3개
insert into work values (1, 1001, 4000); 
insert into work values (2, 1002, 5000);
insert into work values(3, 1003, 6000);
insert into work values (1, 1003, 4000); 

select * from work;
select * from project;
select * from skill;
select * from employee;

-- work 테이블의 제약조건 보기
-- =이때 테이블명은 대문자로 적어야 원하는 대로 내용이 나온다
select * from user_constraints where table_name in ('WORK');

만약 외래키 관계맺은 내용을 지우고 싶다면 자식 부분에서 모두 지우고,
그 후에 부모에서 지워야 제대로 사라진다.

check 제약조건

입력되는 값을 체크하여, 설정된 값 이외의 값이 들어오면 오류 메시지와 함께 명령이 수행되지 못하게 하는것
: 데이터의 값 범위나 특정패턴의 숫자나 문자 값을 설정 할때 사용한다

create table emp02 (
    sal number(7, 2) constraint emp02_sal_ch check(sal between 500 and 50000),
    gender varchar2(1) constraint emp02_gender_ck check(gender in ('M', 'F') ) );
-- 올바른 데이터 3개 삽입
insert into emp02 values(500, 'M');
insert into emp02 values(50000, 'F');
insert into emp02 values (30000, 'M');
-- 잘못된 데이터 3개 삽입
insert into emp02 values(400, 'F'); -- 500 이상 50000 이하로 설정한 check 에서 걸림
insert into emp02 values(60000, 'M'); -- 500 이상 50000 이하로 설정한 check 에서 걸림
insert into emp02 values(50000, 'A'); -- M or F 중 하나여야 하는데 A가 들어와서 걸림


default

: 말 그래로 기본 값
: 아무런 값을 입력 하지 않았을 때 null 이 아니라 기본으로 설정 된 값이 들어간다

create table dept (
    deptno number(2) primary key,
    dname varchar2(14),
    loc varchar2(13) default '서울');
-- 값을 넣으면 그 값으로 들어가고
insert into dept values(10, '경리부', '인천');
-- 값이 비워져있으면 기본값이 들어간다
insert into dept(deptno) values (40);
insert into dept (deptno, dname) values(20, '총무부');
-- null을 넣으면 null 이 들어간다
insert into dept values(30, null, null);

select * from dept;


제약조건을 한꺼번에 지정하고 싶다면?

-- 지금까지처럼 하나씩 제약조건 걸어주기
create table dept (
    empno number(4) primary key,
    ename varchar2(10) not null,
    job varchar2(9) unique,
    deptno number(2) references project(pno) );
    
-- 한꺼번에 걸어주기
create table dept (
    empno number(4),
    ename varchar2(10) not null,    -- not null은 밑에서 따로 설정할 수가 없음
    job varchar2(9),
    deptno number(2),
    
    primary key(empno),
    unique (job),
    foreign key(deptno) REFERENCES project(pno) );
    
-- dept 의 제약 조건을 확인해보자
select * from user_constraints where table_name in ('DEPT');

복합키

복합키로 지정하는데 제약조건 만들기
복합키 : 두 개 이상의 키를 합쳐서 기본키로 삽는것

drop table member01;
create table member01 (
    name varchar2(10),
    address varchar2(30),
    hphone varchar2(16),
    primary key(name, hphone) );
    
-- 올바른 데이터 3개
insert into member01 values('a1', 'seoul', '3334' );
insert into member01 values('a2', 'seoul', '3335' );
insert into member01 values('a3', 'seoul', '3336' );
-- 의외로 지정한 값 중 하나만 같다면 올바르게 삽입된다
insert into member01 values('a1', 'seoul', '2225' );

-- 오류 데이터
-- 기본키 두 개가 모두 중복되어 에러
insert into member01 values('a1', 'seoul', '3334' );

테이블 생성 후 제약조건 + 조건 삭제

-- 제약조건 안주고 기본으로만 테이블 생성하기
create table emp01 (
    empno number(4),
    ename varchar2(10),
    job varchar2(9),
    deptno number(2) );
    
-- 생성된 테이블에 제약 조건 추가하기
alter table emp01 add constraint emp01_empno_pk primary key(empno) ;
alter table emp01 modify ename constraint emp01_ename_nn not null;
-- constraint emp01_empno_pk 이건 안적어도 작성되긴 한다
alter table emp01 add check(job in('사원', '대리', '과장') );

-- 조건 제거하기
-- 제약조건을 제거하기 위해서는 이름이 꼭 필요하다
alter table emp01 drop constraint emp01_ename_nn;
profile
오늘
이전 포스트

DB 연습하기 - 06

다음 포스트

DB 연습하기 - 08

0개의 댓글