Ⅰ. 제약조건
제약조건이 필요한 이유
-- 쇼핑몰 프로그램을 만들 때 아이디는 중복이 없어야 한다. → 고유한 값만 들어오도록 설정
-- 필수 입력과 선택 입력 → 필수 입력일 때 반드시 값을 입력해야 한다(Not Null)
-- 사원 테이블을 만들 때 직급 column은 사장, 팀장, 과장 등만 들어와야 한다. → 고정 값 외 들어오면 안 된다.
→ 이러한 조건들을 만족하며 테이블을 생성하기 위해 제약조건이 필요하다.
제약조건(Constraint)의 종류
1. Primary key(주키) 제약
2. Unique key 제약 -> 중복된 값은 들어올 수 없다.
3. Foreign key(== Reference key, 참조키) 제약
4. Check 제약
5. Not Null 제약
1. Primary key(주키) 제약
-- 어떤 컬럼에 Primary key 제약을 걸면 그 컬럼에 들어오는 값은 고유한 값만 들어오고 동시에 NOT NULL이 되어진다. 즉, 그 컬럼에는 반드시 값이 들어와야 한다.
-- 어떤 테이블상에 존재하는 Primary key의 최대 개수는 1개일 뿐이다.
▷▷ 제약 조건이 없을 때 테이블 생성
HR
create table tbl_membertest01
(userid varchar2(20) -- byte
,passwd varchar2(20)
,name Nvarchar2(20) -- 글자 수
);
insert into tbl_membertest01(userid, passwd, name)
values('leess','1234','이순신');
insert into tbl_membertest01(userid, passwd, name)
values('leess','abcd','이순삼');
insert into tbl_membertest01(userid, passwd, name)
values(null,'6789','똘똘이');
insert into tbl_membertest01(passwd, name)
values('0070','강감찬');
commit;
select *
from tbl_membertest01;
▷ 결과
▷▷ 제약 조건 첫 번째 방법
HR
create table tbl_membertest02
(userid varchar2(20) primary key -- column level(컬럼수준) 제약조건
,passwd varchar2(20)
,name Nvarchar2(20)
);
-- 위와 같이 여러 값을 입력해 본다.
HR
insert into tbl_membertest02(userid, passwd, name)
values('leess','1234','이순신');
-- 1 행 이(가) 삽입되었습니다.
HR
insert into tbl_membertest02(userid, passwd, name)
values('leess','abcd','이순삼');
-- 제약 조건의 이름이 없기 때문에 SYS_C006998와 같이 오라클이 임의로 이름을 지정
HR
insert into tbl_membertest01(userid, passwd, name)
values(null,'6789','똘똘이');
HR
insert into tbl_membertest01(passwd, name)
values('0070','강감찬');
HR
insert into tbl_membertest02(userid, passwd, name)
values('hongkd','abcd','이순삼');
commit;
select *
from tbl_membertest02;
▷ 결과
▷▷ 제약 조건 두 번째 방법
HR
create table tbl_membertest03
(userid varchar2(20)
,passwd varchar2(20)
,name Nvarchar2(20)
,constraint PK_tbl_membertest03_userid primary key(userid) -- row level(행수준) 제약조건
-- PK_tbl_membertest03_userid 을 제약조건명이라고 부르는데 단 제약조건명은 최대길이가 30글자이다.
);
-- 두 번째 방법과 쓰는 방법만 다를 뿐 똑같다.
HR
insert into tbl_membertest03(userid, passwd, name)
values('leess','1234','이순신');
-- 1 행 이(가) 삽입되었습니다.
HR
insert into tbl_membertest03(userid, passwd, name)
values('leess','abcd','이순삼');
-- 사용자가 지정한 제약 조건의 이름(HR.PK_TBL_MEMBERTEST03_USERID)으로 나타난다.
HR
insert into tbl_membertest03(userid, passwd, name)
values(null,'6789','똘똘이');
HR
insert into tbl_membertest03(passwd, name)
values('0070','강감찬');
HR
insert into tbl_membertest03(userid, passwd, name)
values('hongkd','1234','홍길동');
commit;
select *
from tbl_membertest03;
▷ 결과
desc를 이용하여 테이블 구조를 살펴 보자.
HR
desc tbl_membertest01;
HR
desc tbl_membertest02;
-- userid에 null 값이 들어오면 안 된다는 것을 확인할 수 있다.
HR
desc tbl_membertest03;
-- userid에 null 값이 들어오면 안 된다는 것을 확인할 수 있다.
-- 어떤 테이블상에 존재하는 Primary key의 최대 개수는 1개일 뿐이다.
Primary key를 여러 개 만들어 보자.
HR
create table tbl_membertest04
(userid varchar2(20) primary key -- column level(컬럼수준) 제약조건
,passwd varchar2(20) primary key
,name Nvarchar2(20)
);
HR
create table tbl_membertest05
(userid varchar2(20)
,passwd varchar2(20)
,name Nvarchar2(20)
,constraint PK_tbl_membertest05_userid primary key(userid)
,constraint PK_tbl_membertest05_passwd primary key(passwd)
);
single Primary key와 Composite Primary key(== 복합 Primary key)
single Primary key : 컬럼 1개에 Primary key
Composite Primary key : 복합 Primary key, 2개 이상 컬럼에 Primary key
테이블을 생성하여 Composite Primary key에 대해 알아보자.
HR
create table tbl_membertest05
(userid varchar2(20)
,passwd varchar2(20)
,name Nvarchar2(20)
,constraint PK_tbl_membertest05_userid primary key(userid, passwd)
);
▷ 결과
Table TBL_MEMBERTEST05이(가) 생성되었습니다.
HR
insert into tbl_membertest05(userid, passwd, name)
values('leess','1234','이순신');
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
insert into tbl_membertest05(userid, passwd, name)
values('leess','abcd','이순삼');
▷ 결과
1 행 이(가) 삽입되었습니다.
-- userid와 passwd를 묶었을 때 중복이 되어지면 안 된다.
-- 'leess1234'와 'leessabcd'는 같지 않다.
HR
insert into tbl_membertest05(userid, passwd, name)
values('leess','1234','홍길동');
HR
insert into tbl_membertest05(userid, passwd, name)
values('1234','leess','홍길동');
▷ 결과
1 행 이(가) 삽입되었습니다.
-- 'leess1234'와 '1234leess'는 같지 않다.
-- 제약조건의 이름은 고유해야 한다.
HR
create table tbl_membertest06
(userid varchar2(20)
,passwd varchar2(20)
,name Nvarchar2(20)
,constraint PK_tbl_membertest05_userid primary key(userid)
);
-- 위에서 PK_tbl_membertest05_userid라는 이름의 제약조건을 이미 생성하였으므로 중복된다.
HR
create table tbl_membertest06
(userid varchar2(20)
,passwd varchar2(20)
,name Nvarchar2(20)
,constraint PK_tbl_membertest06_userid primary key(userid)
);
▷ 결과
Table TBL_MEMBERTEST06이(가) 생성되었습니다.
2. Unique key 제약
-- 어떤 컬럼에 Unique 제약을 걸면 그 컬럼에 들어오는 값은 고유한 값만 들어온다. 하지만 Unique 제약은 Primary key 제약과 달리 NULL은 허락되어진다. 즉, 그 컬럼에는 NULL 값이 들어올 수 있다.
-- 어떤 테이블상에 존재하는 Unique 제약의 개수는 2개 이상 복수개를 허락한다.
HR
create table tbl_member11
(userid varchar2(20)
,passwd varchar2(20) not null
,name varchar2(20)
,jubun varchar2(13)
,email varchar2(50)
,constraint PK_tbl_member11_userid primary key(userid)
,constraint UQ_tbl_member11_jubun unique(jubun)
,constraint UQ_tbl_member11_email unique(email)
);
▷ 결과
Table TBL_MEMBER11이(가) 생성되었습니다.
▷▷ 주민등록번호(jubun)을 varchar2로 지정하는 이유
-- 입력되어지는 데이터 모양이 숫자로 되었다고 하더라도 맨 앞에 숫자 0으로 시작하는 값이 들어와야 한다라면 반드시 데이터 타입은 number가 아닌 varchar2 또는 Nvarchar2로 해야 한다.
-- 왜냐하면 number로 만들면 맨 앞에 0은 생략되어지기 때문이다.
-- ex) 9109201234567, 0210153234567, 0210154234567
HR
select 9109201234567, '9109201234567',
0210153234567, '0210153234567'
from dual;
-- 0이 생략되어 나타나는 것을 확인할 수 있다.
HR
desc tbl_member11;
HR
insert into tbl_member11(userid, passwd, name, jubun, email)
values('leess','1234','이순신','9109201234567','leess@gmail.com');
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
insert into tbl_member11(userid, passwd, name, jubun, email)
values('hongkd','1234','홍길동','9109201234567','leess@gmail.com');
HR
insert into tbl_member11(userid, passwd, name, jubun, email)
values('hongkd','1234','홍길동','9309201234567','leess@gmail.com');
HR
insert into tbl_member11(userid, passwd, name, jubun, email)
values('hongkd','1234','홍길동','9309201234567','hongkd@gmail.com');
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
insert into tbl_member11(userid, passwd, name, jubun, email)
values('eomjh','1234','엄정화','9309202234567',null);
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
insert into tbl_member11(userid, passwd, name, jubun, email)
values('kang','1234','강감찬',null,null);
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
commit;
select *
from tbl_member11;
-- 참고로 Microsoft 사의 MS-SQL Server는 null 또한 고유한 값으로 취급하기에 어떤 칼럼에 unique 제약을 걸어두면 null 값이 입력되어진 후 또 null 값은 입력이 불가해진다.
-- 그러나 Oracle은 어떤 칼럼에 unique 제약을 걸어두면 null을 데이터로 취급하지 않으므로 null은 여러 번 들어올 수 있다.
HR
create table tbl_member12
(userid varchar2(20)
,passwd varchar2(20) not null
,name varchar2(20)
,jubun varchar2(13) not null
,email varchar2(50) not null
,constraint PK_tbl_member12_userid primary key(userid)
,constraint UQ_tbl_member12_jubun unique(jubun)
,constraint UQ_tbl_member12_email unique(email)
);
-- null 이 들어올 수 없도록 not null을 추가한다.
HR
insert into tbl_member12(userid, passwd, name, jubun, email)
values('hongkd','1234','홍길동','9309201234567','hongkd@gmail.com');
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
insert into tbl_member12(userid, passwd, name, jubun, email)
values('leess','1234','이순신',null,'hongkd@gmail.com');
HR
insert into tbl_member12(userid, passwd, name, jubun, email)
values('leess','1234','이순신','9309201234567','hongkd@gmail.com');
HR
insert into tbl_member12(userid, passwd, name, jubun, email)
values('leess','1234','이순신','9609201234567','leess@gmail.com');
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
create table tbl_member13
(userid varchar2(20) primary key
,passwd varchar2(20) not null
,name varchar2(20)
,jubun varchar2(13) unique not null
,email varchar2(50) unique not null
);
-- 컬럼 레벨로 생성한다.
-- 제약조건 이름을 오라클 임의로 지정한다.
HR
insert into tbl_member13(userid, passwd, name, jubun, email)
values('leess','1234','이순신','9609201234567','leess@gmail.com');
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
insert into tbl_member13(userid, passwd, name, jubun, email)
values('hongkd','1234','홍길동','9609201234567','leess@gmail.com');
HR
insert into tbl_member13(userid, passwd, name, jubun, email)
values('hongkd','1234','홍길동','9709201234567','leess@gmail.com');
-- 주민번호 때문에 오류가 발생한 건지 이메일 때문에 오류가 발생한 건지 알기 어렵다.
-- 따라서 컬럼 레벨보다는 행 레벨로 하는 것이 관리하기에 쉽다.
3. Foreign key(== Reference key, 참조키) 제약
HR
create table tbl_membertest31
(userid varchar2(20)
,passwd varchar2(20) not null
,name Nvarchar2(20) not null
,address Nvarchar2(300)
,constraint PK_tbl_membertest31_userid primary key(userid)
);
insert into tbl_membertest31(userid, passwd, name, address)
values('hongkd', '1234', '홍길동', '서울시 강남구');
insert into tbl_membertest31(userid, passwd, name, address)
values('leess', '1234', '이순신', '서울시 강동구');
insert into tbl_membertest31(userid, passwd, name, address)
values('eomjh', '1234', '엄정화', '서울시 강북구');
commit;
select *
from tbl_membertest31;
HR
create table tbl_orderComment -- 구매후기 테이블
(commentno number -- 글번호
,fk_userid varchar2(20) -- 글쓴이ID
,contents varchar2(4000) -- 글내용 varchar2는 최대크기가 4000 byte 이다.
-- contents Nvarchar2(2000) -- 글내용 Nvarchar2는 최대크기가 2000 이다.
-- contents clob -- 글내용 clob는 최대 4GB까지 허용한다.
,constraint PK_tbl_membertest31_commentno primary key(commentno)
);
insert into tbl_orderComment(commentno, fk_userid, contents)
values(1, 'leess', '안녕하세요? 이순신 인사드립니다');
insert into tbl_orderComment(commentno, fk_userid, contents)
values(2, 'yoonbk', '안녕하세요? 윤봉길입니다');
commit;
select *
from tbl_orderComment;
▶▶ 참고
-- varchar2는 최대크기가 4000 byte 이다. 4000 byte 넘게 사용하고 싶을 때 clob을 사용하면 되는데, clob는 최대 4GB까지 허용한다.
-- Nvarchar2는 최대크기가 2000 이다.
HR
create table tbl_orderComment02
(commentno number
,fk_userid varchar2(20)
,contents varchar2(4000)
-- contents Nvarchar2(2000)
-- contents clob
,constraint PK_tbl_orderComment02 primary key(commentno)
,constraint FK_tbl_orderComment02 foreign key(fk_userid)
references tbl_membertest31(userid)
);
HR
insert into tbl_orderComment02(commentno, fk_userid, contents)
values(1, 'leess', '안녕하세요? 이순신 인사드립니다');
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
insert into tbl_orderComment02(commentno, fk_userid, contents)
values(2, 'yoonbk', '안녕하세요? 윤봉길입니다');
HR
insert into tbl_orderComment02(commentno, fk_userid, contents)
values(2, 'eomjh', '안녕하세요? 엄정화입니다');
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
commit;
select *
from tbl_orderComment02;
HR
create table tbl_orderComment03 -- 구매후기 테이블
(commentno number primary key
,fk_userid varchar2(20) references tbl_membertest31(userid)
,contents varchar2(4000)
);
HR
insert into tbl_orderComment03(commentno, fk_userid, contents)
values(1, 'leess', '안녕하세요? 이순신 인사드립니다');
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
insert into tbl_orderComment03(commentno, fk_userid, contents)
values(2, 'yoonbk', '안녕하세요? 윤봉길입니다');
-- 이 방법도 가능하지만 관리가 쉽도록 행 레벨로 작성하는 것이 좋다.
-- Foreign Key 생성시 전제조건은 참조받는 테이블의 컬럼은 반드시 NOT NULL 이면서 고유한 값만 들어오는 제약조건이 되어져 있어야 한다.
HR
create table tbl_membertest32 -- 회원테이블
(userid varchar2(20)
,passwd varchar2(20) not null
,name Nvarchar2(20) not null
,address Nvarchar2(300)
);
create table tbl_orderComment04 -- 구매후기 테이블
(commentno number
,fk_userid varchar2(20)
,contents varchar2(4000)
,constraint PK_tbl_orderComment04 primary key(commentno)
,constraint FK_tbl_orderComment04 foreign key(fk_userid)
references tbl_membertest32(userid)
);
HR
create table tbl_membertest33 -- 회원테이블
(userid varchar2(20) not null
,passwd varchar2(20) not null
,name Nvarchar2(20) not null
,address Nvarchar2(300)
,constraint UQ_tbl_member33_userid unique(userid)
);
create table tbl_orderComment05 -- 구매후기 테이블
(commentno number
,fk_userid varchar2(20)
,contents varchar2(4000)
,constraint PK_tbl_orderComment05 primary key(commentno)
,constraint FK_tbl_orderComment05 foreign key(fk_userid)
references tbl_membertest33(userid)
);
▷ 결과
Table TBL_MEMBERTEST33이(가) 생성되었습니다.
Table TBL_ORDERCOMMENT05이(가) 생성되었습니다.
foreign key 생성시 on delete 옵션을 준 것과 안 준 것의 차이점에 대해서 알아보자.
▷▷ foreign key 생성시 on delete 옵션을 안 준 경우
HR
-- 회원 테이블 생성
create table tbl_hoiwon01
(userid varchar2(20)
,passwd varchar2(20) not null
,name Nvarchar2(20) not null
,phone varchar2(20)
,constraint PK_tbl_hoiwon01_userid primary key(userid)
);
HR
-- 구매 후기 테이블 생성
create table tbl_buyComment01
(commentno number
,fk_userid varchar2(20)
,contents Nvarchar2(2000)
,writeday date default sysdate
,constraint PK_tbl_buyComment01_commentno primary key(commentno)
,constraint FK_tbl_buyComment01_fk_userid foreign key(fk_userid) references tbl_hoiwon01(userid)
);
-- sysdate : PC의 현재시각을 말한다.
-- default 어떤 값을 적어 주면 그 컬럼에 값을 입력하지 않을 경우 자동적으로 default 다음에 적힌 어떤 값이 들어온다.
즉, 지금은 writeday 컬럼에 값을 입력하지 않을 경우 sysdate가 자동적으로 들어온다.
HR
insert into tbl_buyComment01(commentno, fk_userid, contents, writeday)
values(1, 'leess', '좋은 물건이군요', default);
-- '이순신'이 회원 테이블에 없으므로 참조하여 값을 입력할 수 없다. (구매 후기를 작성하려면 먼저 회원가입을 해야 한다.)
HR
insert into tbl_hoiwon01(userid, passwd, name, phone)
values('leess', '1234', '이순신', '01023456789');
insert into tbl_hoiwon01(userid, passwd, name, phone)
values('honkd', '1234', '홍길동', '01022456789');
insert into tbl_hoiwon01(userid, passwd, name, phone)
values('eomjh', '1234', '엄정화', '01033456789');
commit;
select *
from tbl_hoiwon01;
HR
insert into tbl_buyComment01(commentno, fk_userid, contents, writeday)
values(1, 'leess', '좋은 물건이군요', default);
insert into tbl_buyComment01(commentno, fk_userid, contents)
values(2, 'honkd', '값싸고 좋은 상품이군요!');
insert into tbl_buyComment01(commentno, fk_userid, contents)
values(3, 'leess', '훌륭한 제품입니다. ㅎㅎ');
commit;
select *
from tbl_buyComment01;
-- 날짜타입인 date 는 기본적으로 RR/MM/DD 포맷으로 보여진다.
HR
select commentno, fk_userid, contents, to_char(writeday, 'yyyy-mm-dd hh24:mi:ss')
from tbl_buyComment01;
-- 오전/오후를 나타내려면 hh24 대신 hhpm을 사용한다.
-- 회원 탈퇴를 하려고 할 때, 구매 후기를 작성하지 않은 엄정화의 경우
HR
delete from tbl_hoiwon01
where userid = 'eomjh';
▷ 결과
1 행 이(가) 삭제되었습니다.
-- 구매 후기를 작성한 이순신의 경우
HR
delete from tbl_hoiwon01
where userid = 'leess';
-- 자식 테이블(구매 후기 테이블)에 'leess'이 있기 때문에 삭제하지 못한다.
▷▷ foreign key 생성시 on delete cascade 옵션을 준 경우
HR
-- 회원 테이블
create table tbl_hoiwon02
(userid varchar2(20)
,passwd varchar2(20) not null
,name Nvarchar2(20) not null
,phone varchar2(20)
,constraint PK_tbl_hoiwon02_userid primary key(userid)
);
-- 구매 후기 테이블
create table tbl_buyComment02
(commentno number
,fk_userid varchar2(20)
,contents Nvarchar2(2000)
,writeday date default sysdate
,constraint PK_tbl_buyComment02_commentno primary key(commentno)
,constraint FK_tbl_buyComment02_fk_userid foreign key(fk_userid)
references tbl_hoiwon02(userid) on delete cascade
);
insert into tbl_hoiwon02(userid, passwd, name, phone)
values('leess', '1234', '이순신', '01023456789');
insert into tbl_hoiwon02(userid, passwd, name, phone)
values('honkd', '1234', '홍길동', '01022456789');
insert into tbl_hoiwon02(userid, passwd, name, phone)
values('eomjh', '1234', '엄정화', '01033456789');
commit;
select *
from tbl_hoiwon02;
HR
insert into tbl_buyComment02(commentno, fk_userid, contents, writeday)
values(1, 'leess', '좋은 물건이군요', default);
insert into tbl_buyComment02(commentno, fk_userid, contents)
values(2, 'honkd', '값싸고 좋은 상품이군요!');
insert into tbl_buyComment02(commentno, fk_userid, contents)
values(3, 'leess', '훌륭한 제품입니다. ㅎㅎ');
commit;
select commentno, fk_userid, contents, to_char(writeday, 'yyyy-mm-dd hh24:mi:ss')
from tbl_buyComment02;
-- 회원 탈퇴를 하려고 할 때, 구매 후기를 작성하지 않은 엄정화의 경우
HR
delete from tbl_hoiwon02
where userid = 'eomjh';
▷ 결과
1 행 이(가) 삭제되었습니다.
-- 구매 후기를 작성한 이순신의 경우
HR
delete from tbl_hoiwon02
where userid = 'leess';
▷ 결과
1 행 이(가) 삭제되었습니다.
HR
select *
from tbl_hoiwon02;
select commentno, fk_userid, contents, to_char(writeday, 'yyyy-mm-dd hh24:mi:ss')
from tbl_buyComment02;
-- 회원 테이블과 구매 후기 테이블을 보면 leess이 모두 삭제된 것을 확인할 수 있다.
▷ foreign key 생성시 on delete set null 옵션을 준 예제
HR
-- 회원 테이블
create table tbl_hoiwon03
(userid varchar2(20)
,passwd varchar2(20) not null
,name Nvarchar2(20) not null
,phone varchar2(20)
,constraint PK_tbl_hoiwon03_userid primary key(userid)
);
-- 구매 후기 테이블
create table tbl_buyComment03
(commentno number
,fk_userid varchar2(20)
,contents Nvarchar2(2000)
,writeday date default sysdate
,constraint PK_tbl_buyComment03_commentno primary key(commentno)
,constraint FK_tbl_buyComment03_fk_userid foreign key(fk_userid)
references tbl_hoiwon03(userid) on delete set null
);
insert into tbl_hoiwon03(userid, passwd, name, phone)
values('leess', '1234', '이순신', '01023456789');
insert into tbl_hoiwon03(userid, passwd, name, phone)
values('honkd', '1234', '홍길동', '01022456789');
insert into tbl_hoiwon03(userid, passwd, name, phone)
values('eomjh', '1234', '엄정화', '01033456789');
commit;
select *
from tbl_hoiwon03;
HR
insert into tbl_buyComment03(commentno, fk_userid, contents, writeday)
values(1, 'leess', '좋은 물건이군요', default);
insert into tbl_buyComment03(commentno, fk_userid, contents)
values(2, 'honkd', '값싸고 좋은 상품이군요!');
insert into tbl_buyComment03(commentno, fk_userid, contents)
values(3, 'leess', '훌륭한 제품입니다. ㅎㅎ');
commit;
select commentno, fk_userid, contents, to_char(writeday, 'yyyy-mm-dd hh24:mi:ss')
from tbl_buyComment03;
-- 회원 탈퇴를 하려고 할 때, 구매 후기를 작성하지 않은 엄정화의 경우
HR
delete from tbl_hoiwon03
where userid = 'eomjh';
▷ 결과
1 행 이(가) 삭제되었습니다.
-- 구매 후기를 작성한 이순신의 경우
HR
delete from tbl_hoiwon03
where userid = 'leess';
▷ 결과
1 행 이(가) 삭제되었습니다.
HR
select *
from tbl_hoiwon03;
select commentno, fk_userid, contents, to_char(writeday, 'yyyy-mm-dd hh24:mi:ss')
from tbl_buyComment03;
-- on delete set null을 하고자 할 때에는 해당 컬럼에 null 값을 허용해야 한다.
HR
-- 회원 테이블
create table tbl_hoiwon04
(userid varchar2(20)
,passwd varchar2(20) not null
,name Nvarchar2(20) not null
,phone varchar2(20)
,constraint PK_tbl_hoiwon04_userid primary key(userid)
);
-- 구매 후기 테이블
create table tbl_buyComment04
(commentno number
,fk_userid varchar2(20) not null
,contents Nvarchar2(2000)
,writeday date default sysdate
,constraint PK_tbl_buyComment04_commentno primary key(commentno)
,constraint FK_tbl_buyComment04_fk_userid foreign key(fk_userid)
references tbl_hoiwon04(userid) on delete set null
);
insert into tbl_hoiwon04(userid, passwd, name, phone)
values('leess', '1234', '이순신', '01023456789');
insert into tbl_hoiwon04(userid, passwd, name, phone)
values('honkd', '1234', '홍길동', '01022456789');
insert into tbl_hoiwon04(userid, passwd, name, phone)
values('eomjh', '1234', '엄정화', '01033456789');
commit;
select *
from tbl_hoiwon04;
HR
insert into tbl_buyComment04(commentno, fk_userid, contents, writeday)
values(1, 'leess', '좋은 물건이군요', default);
insert into tbl_buyComment04(commentno, fk_userid, contents)
values(2, 'honkd', '값싸고 좋은 상품이군요!');
insert into tbl_buyComment04(commentno, fk_userid, contents)
values(3, 'leess', '훌륭한 제품입니다. ㅎㅎ');
commit;
select commentno, fk_userid, contents, to_char(writeday, 'yyyy-mm-dd hh24:mi:ss')
from tbl_buyComment04;
-- 회원 탈퇴를 하려고 할 때, 구매 후기를 작성하지 않은 엄정화의 경우
HR
delete from tbl_hoiwon04
where userid = 'eomjh';
▷ 결과
1 행 이(가) 삭제되었습니다.
-- 구매 후기를 작성한 이순신의 경우
HR
delete from tbl_hoiwon04
where userid = 'leess';
4. Check 제약
-- 어떤 칼럼에 Check 제약을 걸면 그 컬럼에 들어오는 데이터값들을 Check 제약에 따른 조건에 만족하는지 만족하지 않는지 검사해서 만족할 경우에만 데이터가 들어오게끔 하는 것이다.
employees 테이블에서 90번 부서에 근무하는 직종ID(JOB_ID)가 AD_VP인 사원들만 모든 정보를 나타내 보자.
HR
select *
from employees
where department_id = 90 and job_id = 'AD_VP';
employees 테이블에서 30번, 40번, 90번 부서에 근무하는 사원들에 대해서 사원번호, 사원이름, 사원성, 직종ID, 기본급여, 부서번호를 나타내 보자.
▷ 첫 번째 방법
HR
select employee_id, first_name, last_name, job_id, salary, department_id
from employees
where department_id = 30 or department_id = 40 or department_id = 90;
▷ 두 번째 방법
HR
select employee_id, first_name, last_name, job_id, salary, department_id
from employees
where department_id in(30,40,90);
HR
create table tbl_sawontest01
(said varchar2(20)
,saname Nvarchar2(20)
,jik Nvarchar2(10) -- 직급
,constraint CK_tbl_sawontest01_jik check(jik in('사장','부장','과장','대리','사원'))
);
HR
insert into tbl_sawontest01(said, saname, jik)
values('kh0001','홍길동','사장');
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
insert into tbl_sawontest01(said, saname, jik)
values('kh0002','이순신','전무');
HR
insert into tbl_sawontest01(said, saname, jik)
values('kh0002','이순신','부장');
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
commit;
select *
from tbl_sawontest01;
HR
update tbl_sawontest01 set jik = '팀장'
where said = 'kh0002';
HR
update tbl_sawontest01 set jik = '과장'
where said = 'kh0002';
▷ 결과
1 행 이(가) 업데이트되었습니다.
HR
commit;
select *
from tbl_sawontest01;
HR
create table tbl_sawontest02
(said varchar2(20)
,saname Nvarchar2(20)
,jik Nvarchar2(10)
,salary number(5) -- -99999 ~ 99999
,comm number(5) -- -99999 ~ 99999
,constraint CK_tbl_sawontest02_jik check(jik in('사장','부장','과장','대리','사원'))
);
-- 이 때, salary는 0보다 크고 99999보다 작거나 같아야 하고 comm은 0보다 크거나 같고 salary보다 작아야 한다.
HR
create table tbl_sawontest02
(said varchar2(20)
,saname Nvarchar2(20)
,jik Nvarchar2(10)
,salary number(5) -- -99999 ~ 99999 0 < salary <= 99999
,comm number(5) -- -99999 ~ 99999 0 <= comm <= salary
,constraint CK_tbl_sawontest02_jik check(jik in('사장','부장','과장','대리','사원'))
,constraint Ck_tbl_sawontest02_salary_comm check(comm >= 0 and comm < salary)
);
HR
insert into tbl_sawontest02(said, saname, jik, salary, comm)
values('leess', '이순신', '대리', 0, -20);
HR
insert into tbl_sawontest02(said, saname, jik, salary, comm)
values('leess', '이순신', '대리', 0, 0);
HR
insert into tbl_sawontest02(said, saname, jik, salary, comm)
values('leess', '이순신', '대리', 5000, 7000);
HR
insert into tbl_sawontest02(said, saname, jik, salary, comm)
values('leess', '이순신', '대리', 7000, 5000);
▷ 결과
1 행 이(가) 삽입되었습니다.
HR
update tbl_sawontest02 set salary = 8000
where said = 'leess';
▷ 결과
1 행 이(가) 업데이트되었습니다.
HR
update tbl_sawontest02 set salary = 2000
where said = 'leess';
HR
commit;
select *
from tbl_sawontest02;
5. NOT NULL 제약
HR
create table tbl_membertest21
(userid varchar2(20) not null
,passwd varchar2(20) not null
,name Nvarchar2(20) not null
,phone Nvarchar2(20) null
,job varchar2(20)
,constraint PK_tbl_membertest21_userid primary key(userid)
);
desc tbl_membertest21;
생성되어진 제약조건을 조회하기
HR
select *
from user_constraints;
-- constraint_type
P 는 Primary Key
R 은 Foreign Key(= Reference Key)
U 는 Unique 제약
C 는 NOT NULL 또는 Check 제약
-- column에 대한 정보는 조회되지 않는다.
HR
select *
from user_cons_columns
where table_name = 'EMPLOYEES';
-- 어느 colums에 primary key, unique 제약 등 어떤 제약을 주었는지 확인할 수 있다.
-- 나중에 join을 배우면 같이 조회할 수 있다.
테이블 생성한 이후에 생성되어진 테이블에 대한 주석(코멘트)을 달기
HR
select *
from user_tab_comments;
HR
create table tbl_sungjuk
(hakbun varchar2(20)
,kor number(3)
,eng number(3)
,math number(3)
,pass char(1) -- varchar2(10) 가변형 'abcd' --> 4byte
-- char(10) 고정형 'abcd' --> 10byte
,constraint PK_tbl_sungjuk_hakbun primary key(hakbun)
,constraint CK_tbl_sungjuk_kor_eng_math check( (0 <= kor and kor <= 100) and
(0 <= eng and eng <= 100) and
(0 <= math and math <= 100) )
,constraint CK_tbl_sungjuk_pass check( pass in('P', 'p', 'F', 'f') )
);
HR
comment on table tbl_sungjuk
is '학생들의 성적처리 테이블';
select *
from user_tab_comments;
HR
comment on column tbl_sungjuk.hakbun
is '학번';
comment on column tbl_sungjuk.kor
is '국어점수 0 이상 100 이하';
comment on column tbl_sungjuk.eng
is '영어점수 0 이상 100 이하';
comment on column tbl_sungjuk.math
is '수학점수 0 이상 100 이하';
comment on column tbl_sungjuk.pass
is '합격여부 합격이라면 P 또는 p 불합격이라면 F 또는 f';
select *
from user_col_comments
where table_name = 'TBL_SUNGJUK';
