*.제약조건.
- 목적 :
- NOT NULL : 해당 컬럼값으로 NULL허용하지않음
- UNIQUE : 테이블내 컬럼값은 유일한값.
- PRIMARY KEY : 테이즐내 기준이 될수있는 컬럼값 -> entity를 대표/업무활용도/키값
- FOREIGN KEY : 해당컬럼값이 다른 컬럼값 참조해야함.
- CHECK : 저장데이터 값의범위나 조건 지정
check(조건) ~
-- 회원번호를 추가로 저장
mno number(5)
-- 제약조건
create table 테이블명
(
컬럼 컬럼크기 [컬럼레벨:CONSTRAINT 제약조건명 [primay key | not null | unique | check(~)]] | [default]
[테이블레벨:CONSTRAINT 제약조건명 컬럼타입(컬럼명)]
)
--회원번호(pk),id(nn), 비밀번호, 이름(nn), 주민번호(uk), 전화번호, 등록일, 지역(ck)
create table member2
(
mno number(5) primary key
, id varchar2(10) not null
, pwd varchar2(5)
, mname varchar2(10) not null
, ssn char(14) unique
, tel varchar2(14)
, loc varchar(10) check(loc in ('서울','인천','부천','포천','제주'))
);
--
insert into member2
values (1,'aa','123','홍길동','801123-1234567',null,'울산');
--ORA-02290: 체크 제약조건(SUPER.SYS_C003028)이 위배되었습니다
insert into member2
values (1,'aa','123','홍길동','801123-1234567',null,'제주');
insert into member2
values (2,'bb','222','김연아','801123-1234567',null,'포천');
--ORA-00001: 무결성 제약 조건(SUPER.SYS_C003030)에 위배됩니다
insert into member2
values (2,'bb','222','김연아','901123-1234567',null,'포천');
insert into member2
values (3,'cc','333','','911123-1234567',null,'부천');
--ORA-01400: NULL을 ("SUPER"."MEMBER2"."MNAME") 안에 삽입할 수 없습니다
insert into member2
values (3,'cc','','김현중','911123-1234567',null,'부천');
insert into member2
values (3,'dd','123','원빈','791123-1234567',null,'인천');
--ORA-00001: 무결성 제약 조건(SUPER.SYS_C003029)에 위배됩니다
insert into member2
values (4,'dd','123','원빈','791123-1234567',null,'인천');
*.제약조건 조회
select constraint_name, constraint_type, table_name
from user_constraints;
-- member3
create table member3
(
mno number(5) constraint mem3_mno_pk primary key
, id varchar2(10) constraint mem3_id_nn not null
, pwd varchar2(5)
, mname varchar2(10) constraint mem3_mname_nn not null
, ssn char(14) constraint mem3_ssn_uk unique
, tel varchar2(14)
, loc varchar(10) constraint mem3_loc_ck check(loc in ('서울','인천','부천','포천','제주'))
);
-- 조회
select constraint_name, constraint_type, table_name
from user_constraints
where table_name='MEMBER3';
create table member4
(
mno number(5) constraint mem4_mno_pk primary key
, id varchar2(10) constraint mem4_id_nn not null
, pwd varchar2(5) default '1004'
, mname varchar2(10) constraint mem4_mname_nn not null
, ssn char(14) constraint mem4_ssn_uk unique
, tel varchar2(14)
, loc varchar(10) constraint mem4_loc_ck check(loc in ('서울','인천','부천','포천','제주'))
);
insert into member4
values (2,'bb',null,'자기야','791223-1234567',null,'인천');
insert into member4
values (1,'aa','','자기야','791123-1234567',null,'인천');
MNO ID PWD MNAME SSN TEL LOC
---------- ---------- ----- ---------- -------------- -------------- ----------
2 bb 자기야 791223-1234567 인천
1 aa 자기야 791123-1234567 인천
insert into member4 (mno,id,mname,ssn,loc)
values (3,'cc','저기야','791113-1234567','서울');
MNO ID PWD MNAME SSN TEL LOC
---------- ---------- ----- ---------- -------------- -------------- ----------
2 bb 자기야 791223-1234567 인천
1 aa 자기야 791123-1234567 인천
3 cc 1004 저기야 791113-1234567 서울
--단점 : 테이블레벨에서는 not null 정의 할수 없다.
--장점 : 제약조건 여러개 추가.
--테이블레벨 제약조건있는 member5 작성
--id에 unique제약조건 추가.
create table member5
(
mno number(5)
, id varchar2(10) constraint mem5_id_nn not null
, mname varchar2(10) constraint mem5_mname_nn not null
, ssn char(14)
, loc varchar(10)
, constraint mem5_mno_pk primary key(mno)
, constraint mem5_id_uk unique(id)
, constraint mem5_ssn_uk unique(ssn)
, constraint mem5_loc_ck check(loc in ('서울','인천','부천','포천','제주'))
);
*.제약조건 constraint
-목적 : DB관련 편리성 확보 -> DB무결성확보
-primary key : not null + unique -> entity를 대표(업무활용높은것)
*.계약조건조회
select constraint_name, constraint_type, table_name
from user_constraints
*.table생성
-제약조건 X
->제약조건 추가,삭제
alter table 테이블명
- add 컬럼명 constraint 제약조건명 제약조건
- drop 제약조건명
- modify(유일하게 not null에서 사용) 컬럼명 constraint 제약조건명 제약조건
-제약조건 O(제약조건이름X) 생성
create table 테이블명
(
컬럼명 데이터타입(크기),
컬럼명 데이터타입(크기) default 기본값
)
-제약조건 O(제약조건이름O) 생성
create table 테이블명
(
컬럼명 데이터타입(크기)
constraint 제약조건명 제약조건,
)
create table 테이블명
(
컬럼명 데이터타입(크기),
컬럼명 데이터타입(크기),
제약조건 constraint 제약조건명 제약조건(컬럼명)
)
--jobinfo 테이블생성 제약조건은 추후에 추가
--job_id varchar2(10) -> pk
--job_title varchar2(20) -> nn
create table jobinfo
(
job_id varchar2(10)
,job_title varchar2(20)
)
alter table jobinfo
add constraint jobinfo_pk primary key(job_id)
--emp2 테이블생성 제약조건은 추후에 추가
--empno number(5) -> pk
--ename varchar2(10) -> nn
--job_id varchar2(10) -> fk
create table emp2
(
empno number(5)
,ename varchar2(10)
,job_id varchar2(10)
)
--fk제약조건
--alter tale 테이블명
-- add [contraint 제약조건이름] foreign key(컬럼명) references 상위테이블(상위컬럼명)
alter table emp2
add constraint emp2_pk primary key(empno)
add constraint emp2_fk foreign key(job_id) references jobinfo(job_id)
alter table emp2
modify ename constraint emp2_nn not null
alter table jobinfo
modify job_title constraint jobinfo_nn not null
============================
*.constraint와 DML
--
상태 : 부모(jobinfo) / 자식(emp2)
insert : O / 부모 키값의 존재여부
update : 자식 키값의 존재여부 / 부모 키값의 존재여부
delete : 자식 키값의 존재여부 / O
--
desc jobinfo
desc emp2
insert into jobinfo
values('AD_PRES','President');
insert into jobinfo
values('AD_VP','Admin Vice President');
insert into jobinfo
values('AD_ASST','Admin Assistant');
insert into jobinfo
values('FI_MGR','Finance Manager');
insert into jobinfo
values('FI_ACCOUNT','Accountant');
insert into emp2
values(100,'스티븐','AD_PRES','');
insert into emp2
values(101,'니나','AD_VP','');
insert into emp2
values(111,'렉스','FI_MGR');
insert into emp2
values(112,'다니엘','FI_ACCOUNT');
--emp2의 다니엘의 empno를 120으로 수정
update emp2
set empno=120 where ename='다니엘';
--emp2의 다이넬의 job_id를 AD_ASST로 수정.
update emp2
set job_id='AD_ASST' where ename='다니엘';
--다니엘의 job_id를 it_pg로 수정
update emp2
set job_id='IT_PG' where ename='다니엘';
ORA-02291: 무결성 제약조건(SUPER.EMP2_FK)이 위배되었습니다- 부모 키가 없습니다
--FI_MGR컬럼값을 FI_MG로 수정
update jobinfo
set job_id='FI_MG' where job_id='FI_MGR';
ORA-02292: 무결성 제약조건(SUPER.EMP2_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
--FI_ACC컬럼값을 FI_ACCOUNT로 수정
update jobinfo
set job_id='FI_ACCOUNT' where job_id='FI_ACC';
--jobinfo테이블에서 fi_acc삭제
delete from jobinfo
where job_id='FI_ACCOUNT'
--jobinfo테이블에서 fi_mgr삭제
delete from jobinfo
where job_id='FI_MGR'
ORA-02292: 무결성 제약조건(SUPER.EMP2_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
--emp2테이블에서 '다니엘' 삭제
delete from emp2
where ename='다니엘'
--emp2테이블에서 '렉스' 삭제
delete from emp2
where ename='렉스'
