初め丸

[제약조건]: Constraint

1.데이터의 무결성을 위해서 사용(Data Integrity)

2.제약조건은 테이블정의시 필드에 설정

3.종류

  1) not null  : 필수입력

  2) unique    : 고유해야된다.(도메인내에 동일한 값을 저장할 수 없다)

  3) check     : 유효성(범위,값...) <=테이블내에서 검색조건을 부여

  4) foreign key   : 다른테이블에서 검색조건 검사

  5) primary key   : not null + unique + index 결합형태 

  6) default   : 기본값이 입력

--1] not null 연습

--drop table tb1; --테이블 삭제

create table TB1

(

   no int not null ,

   name varchar2(20) not null

)

insert into tb1 values(1,'일길동');

--insert into tb1(no) values(2);

--insert into tb1(name) values('이길동');

select * from tb1;

--2] unique 연습

create table tb2

(

  no int not null unique,

  name varchar2(20) not null

)

insert into tb2 values(1,'일길동');

--insert into tb2 values(1,'이길동'); -- X

insert into tb2 values(2,'일길동');

select * from tb2;

--테이블 구조확인

desc tb2;

desc user_constraints;

--제약조건 확인

select constraint_type,constraint_name from user_constraints

where table_name='TB2';

select * from user_constraints where table_name='TB2';

--3[check제약]

-- primary key : 테이블당 1개만 존재(기본키)

create table tb3

(

   no int primary key, -- (not null + unique + index)

   name varchar2(20) not null,

   kor  number(3) check( kor>=0 and kor<=100),

   eng  number(3),

   mat  number(3)

)

--필드 수정                                eng>=0 and eng<=100동일 

alter table tb3 modify eng number(3);

--제약조건 추가

alter table tb3 

      add constraint ck_eng  check(eng between 0 and 100);

alter table tb3 

      add constraint ck_mat  check(mat between 0 and 100);

insert into tb3 values(1,'일길동',55,77,88);

--insert into tb3 values(2,'이길동',200,55,77); --check제약 위반

insert into tb3 values(2,'이길동',88,30,500);

delete from tb3 where no=2;

select * from tb3;

create table tb4

(

   no int primary key,

   name varchar2(20) not null,

   sex  varchar2(20) check(sex='남자' or sex='여자'),

   job  varchar2(30)

)

--제약추가                    -- job='부장' or job='차장' or job='과장' .....

alter table tb4

      add constraint ck_job check( job in('부장','차장','과장','대리','주임','사원') );

insert into tb4 values(1,'일길동','남자','대리');

--insert into tb4 values(2,'이길동','남','대리');

--insert into tb4 values(2,'이길동','여자','대 리');

--insert into tb4 values(2,'이길동','여자','대리 ');

delete from tb4 where no=2;

select * from tb4;

commit;

--4 [default] :기본값

create table tb5

(

   no int primary key,

   name varchar2(20) not null,

   sex  varchar2(10) default '남자'  --입력되지 않으면 기본값

)

insert into tb5 values(1,'일길동','여자');

insert into tb5(no,name) values(2,'이길동');--입력하지 말던지

insert into tb5 values(3,'삼길동',default); --default

insert into tb5 values(4,'사길동','남자',20);

--필드추가

alter table tb5  add age int default 0;

select * from tb5;

--5 [foregin key]

--부서테이블

create table dept

(

   dno   int primary key,

   dname varchar2(30),

   loc   varchar2(30)

)

insert into dept values(10,'인사부','205');

insert into dept values(20,'영업부','305');

insert into dept values(30,'경리부','505');

select * from dept;

--직위테이블

create table job

(

   job_no int primary key, --직위번호

   job_name varchar2(20),  --직위명

   job_pay  int            --급여 

)

insert into job values(100,'부장',3000000);

insert into job values(101,'차장',2500000);

insert into job values(102,'과장',2000000);

insert into job values(103,'대리',1800000);

insert into job values(104,'주임',1500000);

insert into job values(105,'사원',1000000);

select * from job;

create table emp

(

  emp_no    int primary key, --사번

  emp_name  varchar2(30),

  dno       int   references dept(dno)

)

insert into emp values(1,'일길동',10);

insert into emp values(2,'이길동',40);

alter table emp add job_no int;

--외래키 설정

alter table emp

   add constraint fk_job_no foreign key(job_no) references job(job_no);

--desc emp;

update emp set job_no=105 where emp_no=1;

insert into emp values(3,'삼길동',20,103);

--insert into emp values(4,'사길동',20,303);

select * from emp;