210609_1(데이터베이스5, 락&데드락, 제약조건)
락 & 데드락
서로다른 유저가 동일객체를 동시 사용할때 발생
* 어느 시점이든지 오라클사용자들은 일관적으로 동일한 데이터를 읽을 수 있어야 한다.
하나의 테이블에 동시에 사용자 자가 접근하여 DML 문장을 실행하는 경우
특정 사용자가 자원을 독점하지 못하도록 오라클은 락을 발생시킨다.
이 락을 해제하기 위해서 우리가 배운것이 바로 commit과 rollback이다.
dml문장을 실행하면 commit하기 전까지 lock이 걸린다
lock의 발생
create table emp01 as select * from emp;
예) 서울본사 인천지사
1. select * from emp01 where empno = 7900;
--락과 관련없음--
2. delete from emp01 where empno=7900;
--7900에 락을 발생시킴--
3. update emp01 set sal=200
where empno=7900;
--락에 걸려 진행불가--
4. commit / rollback을 해서 락을 푼다
5. 3번이 실행된다
6. commit/rollback을해서 락을푼다
주의) "웹사이트 port1521"에서 접근할때는 일반적으로 auto commit이 true로 설정되어 있기 때문에
DML문장을 실행하면 바로 적용된다.
SHOW AUTOCOMMIT; -- 오토커밋확인
set autocommit off;
set autocommit on;
dead lock 발생 -- 교착상태
* 서로가 서로를 물고 있어서 commit이나 rollback을 더이상 할 수 없는 상태
서울본사 인천지사
-----------------------------------------------smith allen
1.update emp01 set
sal = 100 where
ename ='SMITH';
2.update emp01 set
sal = 200 where
ename = 'ALLEN';
3. update emp01 set
sal = 400 where
ename ='ALLEN';
<2번때문 진행불가>
4.update emp01 set
sal = 300 where
ename='SMITH';
<1번때문 진행불가>
-----------------------------------------------------------교착상태
오라클은 교착상태를 검출하면 어떤 한쪽을 강제로 풀어버린다
5.rollback / commit
6. lock 해제
7. rollback / commit
무결성 제약조건
기본키PRIMARY KEY,
외래키FOREIGN KEY,
유니크UNIQUE,
널NULL
체크 CHECK
데이터의 무결성이란 데이터베이스 내의 데이터의 정확성을 유지하는 것이다.
즉, 사용자가 원하지않는 데이터가 저장되는 것을 방지하는것.
무결성 제약 조건들
컬럼명 데이터형 [제약조건들]
- not null : null을 불허한다. 반드시 유효한 데이터가 입력되어야한다.
- unique : 유일성 - 동일한 데이터의 입력을 불허 합니다. (어떤 디비는 null도 유니크하다)
- primary key : 기본키 - 행을 구별하는 식별자 역할 : unique + not null
- foreign key : 외래키 - 기본키를 참조하는 키, 기본키와 데이터형이 같다.
- check : 특정지정값(ex. 서울 , 부산, 인천,, 중1택)이나 , 범위(나이 0~100살)만 입력을 허용하는 것.
기본키 연습 primary key
1) dept를 구조만 복제하여 dept01을 생성하시오
create table dept01 as select * from dept where 1=0;
2) dept01 (1,'A','AA') 를 입력하시오
insert into dept01 values ( 1,'A','AA'); --ok
3)한번더 dept01 (1,'A','AA') 를 입력하시오
insert into dept01 values ( 1,'A','AA'); -- ok
4) dept01 (null,'B','BB') 를 입력하시오 . 결과는?
insert into dept01 values ( null,'B','BB'); --ok 제약이 없으니 정상입력된다
DEPTNO DNAME LOC
---------- ------------------------ --------------------------
1 A AA
1 A AA
B BB
create table dept02( deptno number(2) primary key, dname varchar2(15), loc varchar2(15));
1) dept02 (1,'A','AA') 를 입력하시오
insert into dept02 values ( 1,'A','AA');
2)한번더 dept02 (1,'A','AA') 를 입력하시오
insert into dept02 values ( 1,'A','AA'); --no
1행에 오류:
ORA-00001: 무결성 제약 조건(SCOTT.SYS_C003999)에 위배됩니다
3) dept02 (null,'B','BB') 를 입력하시오 . 결과는?
insert into dept02 values ( null,'B','BB'); --no
1행에 오류:
ORA-01400: NULL을 ("SCOTT"."DEPT02"."DEPTNO") 안에 삽입할 수 없습니다
SQL> select * from dept02;
DEPTNO DNAME LOC
---------- ------------------------ ------------------------------
1 A AA
제약조건을 설정하는 방법
1. 컬럼수준의 제약조건 : 테이블 생성시 컬럼 옆에 제약을 기술하는것
ex) 컬럼명 데이타형 [제약조건들] -- 컬럼을 기술할때 제약을 같이 기술
2. 테이블수준의 제약조건 : 모든컬럼을 명기하고 별도의 컬럼처럼 제약조건을 기술
ex) 컬럼명 데이타형 ,
제약조건
3. 테이블을 만든 후 제약을 설정하는 경우 : alter ~
설정한 제약 조건들의 정보
user_constraints 에서 찾을 수 있다.
desc user_constraints; --data dictionary
이름 널? 유형
----------------------------------------------------- -------- ------------------------------------
OWNER --소유자 NOT NULL VARCHAR2(30)
CONSTRAINT_NAME --제약이름 NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE --형태 P기본키/R외래키/ VARCHAR2(1)
U유니크/ C 체크
TABLE_NAME --테이블이름 NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER --외래키소유자 VARCHAR2(30)
R_CONSTRAINT_NAME --참조대상키명 VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
column owner format a15
column constraint_name format a15
column constraint_type format a15
column table_name format a15
column R_OWNER format a15
column R_CONSTRAINT_NAME format a15
select owner, constraint_name,constraint_type,table_name,R_OWNER,R_CONSTRAINT_NAME from user_constraints;
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME R_OWNER R_CONSTRAINT_NA
--------------- --------------- --------------- --------------- --------------- ---------------
SCOTT FK_DEPTNO R --외래키 EMP SCOTT PK_DEPT
SCOTT SYS_C003999 P --기본키 DEPT02
SCOTT PK_DEPT P DEPT
SCOTT PK_EMP P EMP
1. 컬럼수준 이름부여 기본키 만들기
constraint 제약명
create table dept03 (deptno number(2) constraint dept03_pk primary key, dname varchar2(15), loc varchar2(15));
2. 테이블 수준 이름부여 기본키 만들기
별도의 컬럼처럼 명기 : constraint 이름 primary key(해당컬럼)
create table dept04 (deptno number(2), dname varchar2(15), loc varchar2(15)
, constraint dept04_pk primary key(deptno));
3. 수정문으로 제약추가하기
테이블을 생성한 후에,
alter table 테이블명 add constraint 제약이름 제약(컬럼)
create table dept05 (deptno number(2), dname varchar2(15), loc varchar2(15));
alter table dept05 add constraint dept05_pk primary key(deptno);@cons
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME R_OWNER R_CONSTRAINT_NA
--------------- --------------- --------------- --------------- --------------- ---------------
SCOTT FK_DEPTNO R EMP SCOTT PK_DEPT
SCOTT SYS_C003999 P DEPT02
SCOTT DEPT03_PK P DEPT03
SCOTT DEPT04_PK P DEPT04
SCOTT DEPT05_PK P DEPT05
주의 사항
1. 제약의 이름을 부여하지 않으면 이름은 SYS_xxxx 로 자동부여된다.
2. 컬럼수준, 테이블수준의 제약은 테이블을 삭제하면 제약도 같이 제거된다.
alter로 만든 제약은 테이블을 삭제하면 제약조건은 휴지통으로간다.
purge recyclebin; 휴지통 비우기
제약조건은 alter drop으로 제거할수있다.
alter table dept05 drop constraint dept05_pk;
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME R_OWNER R_CONSTRAINT_NA
--------------- --------------- --------------- --------------- --------------- ---------------
SCOTT FK_DEPTNO R EMP SCOTT PK_DEPT
SCOTT SYS_C003999 P DEPT02
SCOTT DEPT03_PK P DEPT03
SCOTT DEPT04_PK P DEPT04
SCOTT PK_DEPT P DEPT
제약조건명 변경
alter table 테이블명 rename constraint 구 to 생;
기본키 + 외래키
1. dept06을 만드시오. deptno 컬럼수준, 기본키 이름은 dept06_pk로 설정하시오
create table dept06 (deptno number(2) constraint dept06_pk primary key, dname varchar2(15), loc varchar2(15));
2. emp06을 만드시오. ------------- 외래키 테이블 수준
empno 컬럼수준, 기본키, 이름은 emp06_pk로 설정하시오
deptno는 테이블 수준, 외래키, 이름은 emp06_fk , dept06의 deptno를 참조 constraint이름 foreign key(해당컬럼)
references 타겟테이블(타겟컬럼)
create table emp06(
empno number(2) constraint emp06_pk primary key,
ename varchar2(15), deptno number(2),
constraint emp06_fk foreign key (deptno) references dept06(deptno)
);
----------- 외래키 컬럼 수준 X
create table emp07(
empno number(2) constraint emp07_pk primary key,
ename varchar2(15), deptno number(2)
constraint emp07_fk foreign key (deptno) references dept06(deptno)
);
create table emp07(
empno number(2) constraint emp07_pk primary key,
ename varchar2(15), deptno number(2)
constraint emp07_fk foreign key references dept06(deptno)
);
------------- 외래키 컬럼 수준 O
create table emp07(
empno number(2) constraint emp07_pk primary key,
ename varchar2(15), deptno number(2) references dept06(deptno)
);
------------- alter 문
create table emp08(
empno number(2) constraint emp08_pk primary key,
ename varchar2(15), deptno number(2)
);
alter table emp08 add constraint emp08_fk foreign key (deptno) references dept06(deptno)
부서-------
부서번호
부서명
위치
create table buseo (
b_no number(2) primary key,
b_name varchar2(20),
b_loc varchar2(20)
);
insert into buseo values (1,'기획부','서울');
insert into buseo values (2,'생산부','인천');
insert into buseo values (3,'영업부','서울');
insert into buseo values (5,'영업부','서울');
사원--------
사번
사원명
부서번호
create table sawon (
s_no number(4) primary key,
s_name varchar2(20),
b_no number(2) ,
constraint sawon_fk foreign key (b_no) references buseo(b_no)
);
1. (1111,'홍길동',3) 을 입력해보자-----------------O
insert into sawon values(1111,'홍길동',3);
2. (2222,'홍길순',1) 을 입력해보자---------------O
insert into sawon values(2222,'홍길순',1);
3.(3333,'홍길당',4) -----------------X 기본키를 참조할수 없는 외래키는 입력불가***
insert into sawon values(3333,'홍길순',4);
1행에 오류:
ORA-02291: 무결성 제약조건(SCOTT.SAWON_FK)이 위배되었습니다- 부모 키가 없습니다
4. update buseo set b_no=5 where b_no=2; --------X 기본키는 중복될수없다
1행에 오류:
ORA-00001: 무결성 제약 조건(SCOTT.SYS_C004010)에 위배됩니다
5. update buseo set b_no=6 where b_no=3; --------X 외래키에 의하여 참조되는 기본키는 수정 불가***
1행에 오류: 해결법 : cascade - 기본키 변경->외래키도 변경
ORA-02292: 무결성 제약조건(SCOTT.SAWON_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
6. delete from buseo where b_no = 5; -------------O
B_NO B_NAME B_LOC
---------- ---------------------------------------- ----------------------------------------
1 기획부 서울
2 생산부 인천
3 영업부 서울
7. delete from buseo where b_no = 1; ---------------X 외래키에 의하여 참조되는 기본키는 삭제 불가***
1행에 오류: 해결법 : cascade -기본키 삭제 - 외래키 삭제
ORA-02292: 무결성 제약조건(SCOTT.SAWON_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
cf) cascade
alter table 테이블명 add constraint 외래키이름 foreign key자식속성 references 부모테이블명(자식속성이 참고할 부모속성)on delete cascade;
alter table feed add constraint foreign key (user_id)references user(id) on delete cascade;
유일성 unique
컬럼수준
컬럼 데이터형 unique ; -- 자동이름
컬럼 데이터형 constraint 이름 unique ; -- 이름부여
테이블수준
, contraint 이름 unique (해당컬럼);
alter 문
alter table 테이블이름 add constraint 제약이름 unique (해당컬럼);
아래 테이블에 부서명에 unique를 적용하시오
create table dept09 (deptno number(2), dname varchar2(15), loc varchar2(15));
컬럼--create table dept10 (deptno number(2), dname varchar2(15) unique, loc varchar2(15)) ;
테이블--create table dept11 (deptno number(2), dname varchar2(15), loc varchar2(15),
constraint dname_con unique (dname)) ;
alter--alter table dept09 add constraint dname_con02 unique (dname);
dept09 부서명 기획, 영업, 영업, null, null 등도 입력해보고 결과가 어떤지 살펴보자
insert into dept09 values(1, '기획', '서울');
insert into dept09 values(2, '영업', '대전');
insert into dept09 values(3, '영업', '대구'); --선택지에 대구가 없다
1행에 오류:
ORA-00001: 무결성 제약 조건(SCOTT.DNAME_CON02)에 위배됩니다
insert into dept09 values(4, 'null', '부산'); --'null'은 문자열이라 null이아니다
insert into dept09 values(5, '', '광주');
insert into dept09 values(6, null, '부산'); --널값은 unique에 영향이 없다
1행에 오류:
ORA-00001: 무결성 제약 조건(SCOTT.DNAME_CON02)에 위배됩니다
DEPTNO DNAME LOC
---------- ------------------------------ ------------------------------
1 기획 서울
2 영업 대전
4 null 부산
5 광주
6 부산 --널값은 unique에 영향이 없다
not null
not null 추가하는 방법
ALTER TABLE 테이블명
MODIFY 컬럼명 유형 NOT NULL;
자세한 설명은 생략한다.
check
특정값이나 영역만 입력이 가능하도록 함
기본형 constraint 이름 check (컬럼 in())
constraint 이름 check (컬럼 between A and B)
-------------------------------아래테이블의 loc에는 서울,인천,분당,일산,남양주 값중 하나가 들어가도록 한다.
create table dept12 (deptno number(2), dname varchar2(15),
loc varchar2(15) constraint dept12_ck check (loc in('서울','인천','분당','일산','남양주') ));
insert into dept12 values(1, '회계', '서울');
insert into dept12 values(2, '영업', '부천'); --선택지에 없는 '부천'
1행에 오류:
ORA-02290: 체크 제약조건(SCOTT.DEPT12_CK)이 위배되었습니다
insert into dept12 values(3, '생산', '제주'); -- 선택지에 없는 '제주'
1행에 오류:
ORA-02290: 체크 제약조건(SCOTT.DEPT12_CK)이 위배되었습니다
insert into dept12 values(4, '무역', '인천');
insert into dept12 values(5, '인사', '남양주');
DEPTNO DNAME LOC
---------- ------------------------------ ------------------------------
1 회계 서울
4 무역 인천
5 인사 남양주
-----------------------------아래테이블의 sal에는 1000~2000 사이에서만 입력되도록 하시오.
create table emp12(
empno number(2) constraint emp12_pk primary key,
ename varchar2(15), deptno number(2),
sal number(10) constraint emp12_ck check (sal between 1000 and 2000 ));
insert into emp12 values (1,'홍길동',2,1000);
insert into emp12 values (2,'아로미',3,900); --sal의 범위에서벗어났다
1행에 오류:
ORA-02290: 체크 제약조건(SCOTT.EMP12_CK)이 위배되었습니다
insert into emp12 values (3,'투투',4,2000);
insert into emp12 values (4,'영심이',5,2100); --sal의 범위에서벗어났다
1행에 오류:
ORA-02290: 체크 제약조건(SCOTT.EMP12_CK)이 위배되었습니다
EMPNO ENAME DEPTNO SAL
---------- ------------------------------ ---------- ----------
1 홍길동 2 1000
3 투투 4 2000
제약조건 중지 시키지
alter table 테이블명 disable constraint 제약이름; -- 중지
alter table 테이블명 enable constraint 제약이름; -- 재가동