190530_DAY36 트랜잭션, 무결성제약조건, 데이터 딕셔너리
<Transaction.> 트랜잭션
데이터 처리의 한 단위
오라클에서 발생하는 여러 개의 SQL 명령문 (DML) 들을 하나의 논리적인 작업단위로 처리
하나의 트랜잭션은 All-or-Nothing 방식으로 처리
목적 : 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구
명령어 : COMMIT ROLLBACK SAVEPOINT
sqlplus scott/tiger ---------------------- SELECT 명령문; INSERT 명령문; SELECT 명령문; UPDATE 명령문 ... ... COMMIT; ---------------------- -- COMMIT 할때 트랜잭션에 있던 INSERT UPDATE 등이 물리적으로 DB에 반영된다. -- COMMIT 이후 트랜잭션이 끝나고 다른 트랜잭션이 실행된다.명령어 하나로도 물리적 반영 되지 않나? 왜 트랜잭션을 사용하지 ? INSERT UPDATE간에 연관성이 있을때!
CREATE DROP TRUNCATE 같은 DDL 사용하면 Auto COMMIT 됨, 자동 DB 반영
그냥 INSERT SELECT 는 작업자에게는 보이지만, 외부에서 접근해서 DB조회할때에는 보이지 않는다.
- COMMIT
- 저장되지 않은 모든 데이터를 데이터베이스에 저장하고 현재의 트랜잭션을 종료하라는 명령어
- 커밋하면 트랜잭션의 처리과정이 모두 반영되며 하나의 트랜잭션 과정이 끝남
- 트랜잭션이 발생하는 경우 새롭게 생성되거나 갱신된 데이터들이 물리적으로 영구히 저장됨
- DDL( create, drop, alter, rename, truncate) 은 AutoCommit!
- 정상적인 종료시에도 Commit작업을 수행
- 예) exit종료
- ROLLBACK (TO)
- 저장되지 않은 모든 데이터 변경사항(DML)을 취소하고 현재의 트랜잭션을 끝내라는 명령
- 트랜젝션으로 인한 하나의 묶음처리가 시작되기 이전의 상태로 되돌려지는 것
- 이전상태로 되돌아가 지금까지 수행했던 데이터베이스의 변경을 모두 무효화
- 비정상적인 종료시 ROLLBACK 작업을 수행
- 예) 우측 상단 X버튼 클릭시
문제) 20번 부서와 40번 부서를 삭제하시오 (DML)
DELETE FROM dept3
WHERE deptno IN (20,40);
SELECT * FROM dept3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
30 SALES CHICAGO
--===========================================================================
문제) 30번 부서의 이름을 '영업부'로 변경하시오 (DML)
UPDATE dept3
SET dname = '영업부'
WHERE deptno = 30;
SELECT * FROM dept3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
30 영업부 CHICAGO
하고 ROLLBACK;
SELECT * FROM dept3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS
--===========================================================================
문제) 10, 20, 30 번 부서를 삭제하시오
DELETE FROM dept3
WHERE deptno IN (10,20,30);
SELECT * FROM dept3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
40 OPERATIONS BOSTON
COMMIT;
ROLLBACK;
SELECT * FROM dept3; -- 이미 늦어버렸따, 이미 커밋해버렸어...
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
40 OPERATIONS BOSTON
<SAVEPOINT.> : 저장점
형식) SAVEPOINT 저장점명;
DROP TABLE dept4; CREATE TABLE dept4 AS SELECT * FROM dept; SELECT * FROM dept4; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SAVEPOINT sp1; -- 20번 부서 삭제 DELETE FROM dept4 WHERE deptno = 20; SELECT * FROM dept4; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 40 OPERATIONS BOSTON SAVEPOINT sp2; DELETE FROM dept4 WHERE deptno = 10; DELETE FROM dept4 WHERE deptno = 30; SAVEPOINT sp3; -- 지...지워졌다! 근데 잘못지웠거나, 다시 돌아가고 싶다면 ROLLBACK to sp1; SELECT * FROM dept4; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON -- 돌아왔네! ROLLBACK to sp2; SELECT * FROM dept4; -- 에러 발생...! 순차적으로 가야 한다 여기서는 sp3 - sp2 - sp1 순으로는 롤백이 가능하지만 -- sp1 했다가 sp2 sp3 하게 된다면 에러가 발생한다. ORA-01086: savepoint 'SP2' never established in this session or is invalid
<무결성 제약조건> Data Integrity Constraint Rule
- 테이블에 부적절한 자료가 입력(INSERT, UPDATE) 되는 것을 방지하기 위해서 테이블 생성할 때 각 컬럼에 대해서 정의하는 여러가지 규칙.
- (테이블 생성 후 컬럼에 규칙하는 (CONSTRAINT 적용) 하는 것도 가능)
NOT NULL
- 해당 컬럼값으로 NULL을 허용하지 않음
- (행단위)입력시 데이터를 무조건 받겠다!!
UNIQUE
- 테이블 내에서 해당 컬럼값은 항상 유일무이한 값을 가질 것
- 중복을 허용하지 않겠다!
PRIMARY KEY
- 해당 컬럼값은 반드시 존재해야 하고 유일해야 한다는 조건
- 테이블 내에서 서로 다른 행을 구분하기 위해서 사용
FOREIGN KEY
해당 컬럼의 값이 타컬럼의 값을 참조해야만 함
즉, 참조되는 컬럼에 존재하지 않는 값은 입력 불가
- 참조하는 사원테이블에서 설정( 예 : 사원테이블(자식)의 deptno에 참조키 설정!) - 참조되는 쪽 부서테이블 (부모테이블) EMP테이블 (deptno) -----------------> DEPT테이블(deptno) 10, 20, 30, 40 사원 INSERT 시 (empno, ename, deptno) VALUES (8000, '김길동', 30) => 존재하는 부서30에 저장 성공! VALUES (8002, '박길동', 50) => 존재하지 않는 부서50에 저장 실패 SCOTT 사원의 사번 = 7788 UPDATE emp SET deptno = 70 WHERE empno = 7788; --존재하지 않는 부서 70으로 인해 에러 발생
CHECK
- 해당 컬럼에 저장 가능한 데이터 값의 범위나 사용자 조건을 지정
- age컬럼 : ( 1~100 )
- gender컬럼 : (남성, 여성)
<제약조건 설정하기 1 > - 컬럼레벨 정의 방법
컬럼레벨 : 제약조건의 명시를 테이블 생성시 컬럼정의와 함께!! (같은 줄에 제약 정의)
형식
CREATE TABLE 테이블명 ( 컬럼명 자료형 CONSTRAINT_TYPE ); DROP TABLE dept2; CREATE TABLE dept2 ( deptno number(2), dname varchar2(15), location varchar2(15) ); INSERT into dept2 VALUES ( 10, '영업부', '서울'); INSERT into dept2 VALUES ( 10, '자재부', '서울'); INSERT into dept2 VALUES ( NULL, '총무부', '서울'); INSERT into dept2 VALUES ( 10, '영업부', '서울'); select * from dept2; DEPTNO DNAME LOCATION ---------- ------------------------------ ------------------------------ 10 영업부 서울 10 자재부 서울 총무부 서울 10 영업부 서울
<제약조건 설정하기1_2> - 컬럼레벨(컬럼선언시) 정의 방법, 이름지정!!
형식)
DROP TABLE dept2; CREATE TABLE dept2 ( deptno NUMBER(2) CONSTRAINT dept2_deptno_pk PRIMARY KEY, dname VARCHAR2(15), location VARCHAR2(15) ); INSERT INTO dept2 VALUES ( 10, '영업부', '서울'); INSERT INTO dept2 VALUES ( 10, '자재부', '서울'); ORA-00001: unique constraint (SCOTT.DEPT2_DEPTNO_PK) violated --얘는 에러 발생 제약명을 지정하지 않은 경우 에러!
1. NOT NULL || 2.UNIQUE || 3. PRIMARY KEY || 4. FOREIGN KEY(외래키, 참조키)
NOT NULL
데이터를 반드시 입력!
수정시 NULL로 변환 불가!
DROP TABLE emp2; CREATE TABLE emp2 ( empno NUMBER(4) CONSTRAINT emp2_empno_nn NOT NULL, -- NOT NULL의 제약명은 보통 생략 ename VARCHAR2(15), sal NUMBER(7,2), job VARCHAR2(15) ); -- 위에서 실습했던 첫번째 행과 두번째 행 완전 동일시 지우기 힘듬, (lowid...같은거 사용해야함) 이런 상황 미연에 방지하기 위해 제약을 둬야 한다. CONSTRAINT 나 NOT NULL의 제약명은 생략 INSERT INTO emp2 VALUES (7000, '홍길동', 2000, '사원'); INSERT INTO emp2 (empno, ename, sal, job) VALUES (7002,'길라임', 2000, '사원' ); INSERT INTO emp2 (empno, ename, sal, job) VALUES (7002,'길라임', 2000, '사원' ); SELECT * FROM emp2; --NOT NULL 은 유일한 값을 보장하지 못함. --유일하지 않고 반드시 입력 또는 수정해야 할 컬럼에 설정을 해줘야 한다.
UNIQUE
- 중복된 데이터를 방지
<제약조건 설정하기2> - 데이블 레벨 (전체 컬럼선언이 끝나고 난 후) 정의 방법
DROP TABLE emp2;
CREATE TABLE emp2
(
empno NUMBER(4),
ename VARCHAR2(15),
sal NUMBER(7,2),
job VARCHAR2(15),
CONSTRAINT emp2_empno_uk UNIQUE (empno)
);
INSERT INTO emp2 VALUES (7000, '홍길동', 2000, '사원');
INSERT INTO emp2 VALUES (7002, '홍길동', 2000, '사원');
INSERT INTO emp2 VALUES (7002, '홍길동', 2000, '사원'); -- 위와 같은 데이터값
ORA-00001: unique constraint (SCOTT.EMP2_EMPNO_UK) violated -- 에러,UNIQUE 가 중복 방지
INSERT INTO emp2 VALUES (7004, '홍길동', 2000, '사원');
INSERT INTO emp2 VALUES (NULL, '김주원', 2000, '사원'); -- 입력 성공
INSERT INTO emp2 VALUES (NULL, '김주원', 2000, '사원'); -- 위와 같은 데이터 값, 입력 성공됨!...
--UNIQUE제약은 중복 데이터를 방지해준다, 하지만 NULL의 중복은 방지하지 못함!!
PRIMARY KEY( 기본키, 주키 )
not null AND unique를 동시에 만족하는 데이터 입력.
테이블내의 서로 다른 행을 구분하는 목적으로 사용.
한 테이블내에 단 한 개의 primary key만 존재!!
DROP TABLE emp2; CREATE TABLE emp2 ( empno number(4) primary key, ename varchar2(15) primary key, sal number(7,2), job varchar2(15) ); ==> ORA-02260: table can have only one primary key
<제약조건 설정하기3> - 제약추가 : 이미 테이블 객체가 만들어진 상태에서 제약 추가!!
DROP TABLE EMP2;
create table emp2
(
empno number(4),
ename varchar2(15),
sal number(7,2),
job varchar2(15)
);
ALTER TABLE emp2
ADD CONSTRAINT emp2_pk PRIMARY KEY (EMPNO); -- CONSTRAINT 는 생략 가능, 시스템에서임의로 잡아줌
INSERT INTO EMP2 VALUES (8000, '홍길동', 2000, '사원');
INSERT INTO EMP2 VALUES (8000, '홍길동', 2000, '사원'); -- 에러나야 하는데... 남!
-- ORA-00001: unique constraint (SCOTT.EMP2_PK) violated , 동일 값 잘 잡아줌!
INSERT INTO EMP2 VALUES (NULL, '홍길동', 2000, '사원'); -- 에러나야 하는데... 남!
-- ORA-01400: cannot insert NULL into ("SCOTT"."EMP2"."EMPNO"), NULL 안들어가게 잘 잡아줌!drop table emp2;
create table emp2
(
empno number(4) ,
ename varchar2(15),
sal number(7,2),
job varchar2(15)
);
alter table emp2
add constraint emp2_pk primary key (empno, ename);
==> Table altered. (empno와 ename을 조합한 한 개의 primary key설정!!)
insert into emp2 values (8000,'홍길동',3000,'사원');
--> 성공
insert into emp2 values (8000,'길라임',3000,'사원');
--> 성공
insert into emp2 values (8002,'길라임',3000,'사원');
--> 성공
insert into emp2 values (8002,'길라임',3000,'사원');
--> ORA-00001: unique constraint (SCOTT.EMP2_PK) violated문제)
emp2 테이블의 PRIMARY KEY(emp2_pk)를 삭제하시오
삭제1)
ALTER TABLE emp2
DROP CONSTRAINT emp2_pk;
ALTER TABLE emp2
ADD CONSTRAINT emp2_pk PRIMARY KEY(empno, ename);
삭제2)
ALTER TABLE emp2
DROP PRIMARY KEY;
--===========================================================================
문제)
emp2 테이블을 새롭게 생성(empno, ename, sal, deptno컬럼) 하고 테이블 생성 후 empno에 PRIMARY KEY설정을 하고 ename, sal, deptno에 NOT NULL하시오
DROP TABLE emp2;
CREATE TABLE emp2
(
empno NUMBER(4),
ename VARCHAR2(10),
sal NUMBER(7,2),
deptno NUMBER(6)
);
ALTER TABLE emp2
ADD CONSTRAINT emp2_pk ADD PRIMARY KEY (empno);
--혹은
ALTER TABLE emp2
ADD PRIMARY KEY (empno);
ALTER TABLE emp2
ADD CONSTRAINT emp2_ename_nn NOT NULL (ename);
-- ORA-00904: : invalid identifier 에러발생
-- ADD NOT NULL 안되는 이유? = 테이블 내의 모든 컬럼은 틀별한 설정을 하지 않았을 경우 NULL 값이 기본으로 설정되어짐, 그런데 거기에 ADD 한다고 하니 에러가 발생, NULL을 NOT NULL로 바꿔 주어야 겠다. 밑에서 제약수정!<제약 수정>
ALTER TABLE 테이블명
MODIFY 컬럼명 [ CONSTRAINT 제약명 ] NOT NULL | NULL -- 둘 중 하나가 들어가면 된다.
그럼 위의 예문에 이어서 수정한다면.
ALTER TABLE emp2
MODIFY ename NOT NULL;
ALTER TABLE emp2
MODIFY sal NOT NULL;
ALTER TABLE emp2
MODIFY deptno NOT NULL;
desc emp2
Null? Type
------------------------------------------------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME NOT NULL VARCHAR2(10)
SAL NOT NULL NUMBER(7,2)
DEPTNO NOT NULL NUMBER(6)
문제)
emp2테이블의 sal컬럼의 not null제약을 삭제하시오.
alter table emp2
modify sal null;
not null ---> null변경
<제약이름을 통한 not null삭제>
alter table emp2
modify sal constraint emp2_sal_nn not null;
-- null ---> not null변경
alter table emp2
drop constraint emp2_sal_nn;
SQL> desc emp2
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME NOT NULL VARCHAR2(15)
SAL NUMBER(7,2)
DEPTNO NOT NULL NUMBER(2)foreign key(외래키, 참조키)
참조하는 테이블의 컬럼데이터 범위내의 데이터만 입력.
예) 사원정보를 입력할 때 반드시 존재하는 부서의 번호를 속성값으로 가져와야 함!!
- 사원테이블(emp)에서 사원의 정보를 입력,수정시
- 부서테이블(dept)로 부터 존재하는 부서번호인지를 참조, 확인해야 함!!
참조되는 컬럼은 unique 또는 primary key 설정이 되어 있어야 함.
사원테이블(deptno 입력, 수정) -------> 부서테이블(deptno참조) 10 20 30 40 ---> 컬럼레벨 create table 테이블명emp ( 컬럼명 자료형 [CONSTRAINT 제약명] REFERENCES 참조테이블명 (참조컬럼명) deptno number(2) references dept (deptno) ); ---> 테이블레벨 create table 테이블명emp ( 컬럼명1 자료형, 컬럼명2deptno 자료형, 컬럼명3 자료형, [CONSTRAINT 제약명] FOREIGN KEY (컬럼명2) REFERENCES 참조테이블명 (참조컬럼명) ---> foreign key (deptno) references dept (deptno) ); ---> 테이블 생성 끝나 후 foreign key 추가 alter table 테이블명 ADD [CONSTRAINT 제약명] foreign key (deptno) references dept (deptno) --=========================================================================== 참조되는 테이블(부모테이블) dept2 DROP TABLE emp2; CREATE TABLE emp2 AS SELECT empno, ename, sal, deptno FROM EMP WHERE 1 = 0; -- 데이터는 복사하지 않겠습니다~ --=========================================================================== 문제) emp2 테이블 내의 empno에 PRIMARY KEY 설정, deptno에 FOREIGN KEY 설정을 하시오! ALTER TABLE emp2 ADD PRIMARY KEY(empno); --CONSTRAINT emp2_empno_pk 는 생략 가능 alter table emp2 add constraint emp2_deptno_fk foreign key (deptno) -- 현재테이블의 컬럼 references dept2 (deptno); -- 참조(부모)테이블의 컬럼 ---> ORA-02270: no matching unique or primary key for this column-list -- 참조되는 컬럼(dept2테이블 ---> deptno컬럼)은 반드시 unique 또는 primary key설정!! --dept2의 deptno : unique설정 alter table dept2 add constraint dept2_uk unique (deptno); -- 또는 -- dept2의 deptno : primary key설정 alter table dept2 add constraint dept2_pk primary key (deptno); ==> 변경성공!! ALTER TABLE emp2 ADD CONSTRAINT emp2_dept_fk FOREIGN KEY(deptno) -- 현재 테이블의 칼럼 REFERENCES dept2 (deptno); -- 참조(부모) 테이블의 컬럼 --외래키 설정 성공 --=========================================================================== 문제1) emp3테이블을 새롭게 생성(empno, ename, sal, deptno)하고 dept3테이블을 dept테이블 참조하는 서브쿼리를 통해 생성(전체구조, 데이터)하시오. DROP TABLE emp3; CREATE TABLE emp3 ( empno NUMBER(4), ename VARCHAR2(10), sal NUMBER(7,2), deptno NUMBER(4) ); DROP TABLE dept3; CREATE TABLE dept3 AS SELECT * FROM dept; --=========================================================================== 문제2) emp3테이블의 deptno가 dept3테이블의 deptno를 참조하도록 하시오. (dept3테이블의 deptno에 unique설정) ALTER TABLE emp3 ADD CONSTRAINT dept3_uk unique(deptno); ALTER TABLE emp3 ADD CONSTRAINT emp3_fk FOREIGN KEY (deptno) -- 현재(자식)테이블 REFERENCES dept3(deptno); -- 부모테이블 --=========================================================================== 문제3) dept3테이블의 unique제약을 삭제하고 deptno에 primary key 제약 설정. -- --------DROP --------------------ADD ALTER TABLE dept3 DROP CONSTRAINT dept3_uk; ---> ORA-02273: this unique/primary key is referenced by some foreign keys ---> 참조하는(자식) 테이블이 존재하는 경우는 unique 와 primary key를 삭제할 수 없음!! -- 삭제해결1) -- 참조하는 (자식)테이블의 foreign key를 삭제 후 부모테이블의 unique, primary key 삭제 -- 외래키 삭제 (자식테이블쪽의 foreign key) alter table emp3 drop constraint emp3_fk; --==> 변경성공!! -- unique 삭제 alter table dept3 drop constraint dept3_uk; --==> 변경성공^^* SELECT table_name, constraint_name FROM user_constraints WHERE table_name IN ('EMP3', 'DEPT3'); -- 대문자로 저장됨, 그래서 대문자로 입력
TABLE_NAME CONSTRAINT_NAME
------------------------------------------------------------
DEPT3 DEPT3_UK
EMP3 EMP3_FK
-- 삭제해결2)
CASCADE옵션을 사용(참조하는 (자식) 테이블이 여러 개 있을 때 유용)
ALTER TABLE dept3
DROP CONSTRAINT dept3_uk CASCADE;
-- 변경성공
-- CASCADE : 자동으로 관련된 모든 fk를 먼저 삭제해 줌.
select table_name, constraint_name
from user_constraints
where table_name IN ('EMP3','DEPT3');
==> no rows selected
alter table dept3
add constraint dept3_deptno_pk primary key (deptno);
==> 변경성공!!
alter table emp3
add constraint emp3_deptno_fk
foreign key (deptno)
references dept3(deptno);
==> 변경성공!!
--조회
select table_name, constraint_name
from user_constraints
where table_name IN ('EMP3','DEPT3');
TABLE_NAME CONSTRAINT_NAME
------------------------------------------------------------ -----------------------------
DEPT3 DEPT3_DEPTNO_PK
EMP3 EMP3_DEPTNO_FK
```<CHECK 제약>
특정 범위내의 데이터만 입력
정해진 데이터만 입력
형식
CHECK (조건식) -- 문제) -- emp4테이블 생성 사원번호 : 행을 구분하는 용도 -- PRIMARY KEY 사원명 : 기본값 '무명'지정 -- DEFAULT 데이터 급여 : 최소 500, 최대 1000 -- CHECK ( 조건식 ) 매니저 : 반드시 입력 -- NOT NULL 부서번호 : 부서테이블(dept3)의 부서번호 참조 -- FOREIGN KEY 성별(Gender) : 'M' 또는 'F'입력 체크 -- CHECK ( 조건식 ) DROP TABLE emp4; CREATE TABLE emp4 ( empno NUMBER(4) CONSTRAINT emp4_empno_pk PRIMARY KEY, ename VARCHAR2(10) DEFAULT '무명' , sal NUMBER(7,2) CHECK ( sal BETWEEN 500 AND 1000 ) , mgr NUMBER(4) NOT NULL , deptno NUMBER(2) CONSTRAINT emp4_deptno_fk REFERENCES dept3(deptno), gender CHAR(1) CHECK (gender IN ( 'F', 'M' ) ) -- (gender = 'M' OR gender = 'F') 이렇게 해도 됨 위가 줄인것 ); -- 테이블 생성 성공! --테스트 INSERT INTO emp4 (empno, ename, sal, mgr, deptno, gender) VALUES (8000,'이진주',1000, 8001, 10, 'F'); --성공 INSERT INTO emp4 (empno, sal, mgr, deptno) VALUES (8001, 1000, 8001, 10); -- 무명이라고 잘 나오나? insert into emp4 (empno, ename, sal, mgr, deptno , gender) values (8002, '길라임', 1001, 8001, 10 , 'F' ); -- sal : check --==> ORA-02290: check constraint (SCOTT.SYS_C007032) violated ---> sal : 500~1000 insert into emp4 (empno, ename, sal, mgr, deptno , gender) values (8002, '길라임', 1000, 8001, 10 , 'F' ); -- sal : check insert into emp4 (empno, ename, sal, mgr, deptno , gender) values (8003, '김주언', 1000, 8001, 10 , '남' ); -- gender : check --==> ORA-12899: value too large for column "SCOTT"."EMP4"."GENDER" (actual: 3,maximum: 1) insert into emp4 (empno, ename, sal, mgr, deptno , gender) values (8003, '김주언', 1000, 8001, 10 , 'Y' ); -- gender : check --==> ORA-02290: check constraint (SCOTT.SYS_C007033) violated --==> 'M' 또는 'F' /* EMPNO ENAME SAL GE ---------- ------------------------------ ---------- -- 8000 홍길동 1000 F 8001 무명 1000 8002 길라임 1000 F 8003 김주언 1000 M */
※ 참고)
create table emp5
(
deptno number(2) references 참조테이블명(참조컬럼명) on delete cascade
---------
-- 부모 (부서)테이블에서 특정 번호(예:10번부서)를 삭제했을때
-- 자식 (사원)테이블에서 10번 부서에 근무하는 사원들을 삭제!!
);
create table emp5
(
deptno number(2) references 참조테이블명(참조컬럼명) on delete set null
---------
-- 부모 (부서)테이블에서 특정 번호(예:10번부서)를 삭제했을때
-- 자식 (사원)테이블에서 10번 부서에 근무하는 사원들의 deptno를 null값으로 변경!!
);
테스트1)
drop table dept5;
create table dept5
as select * from dept;
alter table dept5
add constraint dept5_pk primary key (deptno);
------------------------------------------------
drop table emp5;
create table emp5
(
empno number(4),
ename varchar2(15),
sal number(7,2),
deptno number(2) constraint emp5_fk references dept5(deptno) on delete cascade
);
insert into emp5 (empno,ename,sal,deptno)
select empno,ename,sal,deptno from emp;
## 부모 테이블의 10번 부서 삭제 ##
select count(*) from emp5; ---> 14명 사원
delete from dept5 where deptno=10; ---> 1행(10번부서) 삭제
select count(*) from emp5; ---> 11명 사원 (10번부서의 사원들도 함께 삭제 되었음)
테스트2)
alter table emp5
drop constraint emp5_fk;
drop table dept5;
create table dept5
as select * from dept;
alter table dept5
add constraint dept5_pk primary key (deptno);
------------------------------------------------
drop table emp5;
create table emp5
(
empno number(4),
ename varchar2(15),
sal number(7,2),
deptno number(2) constraint emp5_fk references dept5(deptno) on delete set null
);
insert into emp5 (empno,ename,sal,deptno)
select empno,ename,sal,deptno from emp;
## 부모 테이블의 10번 부서 삭제 ##
select count(*) from emp5; ---> 14명 사원
delete from dept5 where deptno=10; ---> 1행(10번부서) 삭제
select count(*) from emp5; ---> 14명 사원 (10번부서의 사원들의 deptno가 null로 변경됨)
SQL> select empno, ename, sal, deptno
from emp5
where deptno is null;
EMPNO ENAME SAL DEPTNO
---------- ------------------------------ ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
<데이터 딕셔너리> DD 시스템 테이블
- 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는
시스템 테이블. - 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할때
데이터베이스서버에 의해 자동으로 갱신되는 테이블.
<제약조건 확인하기>
USER_CONSTRAINTS
DESC USER_CONSTRAINTS
OWNER : 제약조건을 소유한 사용자명 CONSTRAINT_NAME : 제약조건명 CONSTRAINT_TYPE : 제약조건타입 TABLE_NAME : 제약조건이 속한 테이블명 SERCH_CONDITION : check제약일 경우 조건으로 사용되어지는 설명 R_CONSTRAINT_NAME : FOREING KEY경우 어떤 PRIMARY KEY............
<CONSTRAINT_TYPE>
P : PRIMARY KEY
R : FOREIGN KEY
U : UNIQUE
C : CHECK, NOT NULL궁금상자
- TABLE 생성시에 FOREIGN KEY 설정 안되나? 됨...'클라우드 기반 웹 개발자 과정 공부 > DB' 카테고리의 다른 글
| 190603_DAY38 <복습>, <조건문>,<반복문>, <Stored Procedure>, <Stored Function> (0) | 2019.06.03 |
|---|---|
| 190531_DAY <뷰>, <시퀀스>, <PL/SQL> (0) | 2019.05.31 |
| 190529_DAY35, <복습>, <DDL>, <DML> (0) | 2019.05.29 |
| 190528_DAY ANSI JOIN 복습, 서브쿼리, DDL시작 (0) | 2019.05.28 |
| 190527_DAY33 decode, case , join (0) | 2019.05.27 |