■ 제약 조건 체크 여부
※ DEFERRABLE
해석 | |
NOT DEFERRABLE (기본값) | 트랜잭션 내에서 즉시 확인(DML 을 수행하는 순간. 즉시 체크) 지연할 수 없다. 이후에 변경 작업을 할 수 없다. |
DEFERRABLE | 트랜잭션 내에서 확인을 미룰 수 있다.(지연가능하다) 변경 작업이 제약조건을 위반하더라도 트랜잭션이 완료될 때까지는 실제로 적용되지 않는다. 즉, 트랜잭션 작업이 완료되기 전까지는 일시적으로 허용하며, 트랜잭션이 성공적으로 완료되면 제약조건이 다시 확인한다. |
※ DEFERRED
해석 | |
INITIALLY IMMEDIATE (기본값) |
DML문 완료되면 제약 조건을 검사한다. |
INITIALLY DEFERRED | DML문에 대한 COMMIT 문을 수행 할 때 제약조건 검사를 수행한다. |
#1) 연습용 INITIALLY 제약 조건의 테이블 생성
CREATE TABLE emp_sal (
salary NUMBER CONSTRAINT sal_ck CHECK ( salary > 100 ) DEFERRABLE INITIALLY IMMEDIATE,
-- DEFERRABLE INITIALLY IMMEDIATE 에서 INITIALLY 작성해줘야 한다. 출력되는 창에서는 생략된다.
bonus NUMBER CONSTRAINT bonus_ck CHECK ( bonus > 0 ) DEFERRABLE INITIALLY DEFERRED -- commit 시에만 제약조건 확인하겠다
);
#2) 테이블 제약조건 생성 확인
select * from user_constraints where table_name = 'EMP_SAL';
#3-1) INSERT문 수행 ⓐ
INSERT INTO emp_sal (salary, bonus) VALUES (90, 5);
- sal_ck 위반
#3-2) INSERT문 수행 ⓑ
INSERT INTO emp_sal (salary, bonus) VALUES (200, -5);
- 위반되어 있지만, 제약조건은 즉시 적용되지 않는다. 데이터는 임시 저장 상태
- bonus 컬럼의 체크 제약조건의 위반여부 체크는 바로 적용하지 않고 COMMIT 시에 수행된다.
#3-3) INSERT문 + COMMIT 수행
- commit 시 체크제약조건의 위반여부 확인
INSERT INTO emp_sal (salary, bonus) VALUES (200, -5);
COMMIT;
1. 현재 session에서 제약조건을 지연가능하도록 수행하겠다는 의미
2. 트랜잭션이 끝나면 자동 해지된다.
3. NOT DEFERRABLE 로 설정되어 있을 경우는 수행할 수 없다.
#) 제약조건 전체 DEFERRED 변경 (아래 2개의 SQL문장 가능)
- 트랜잭션이 돌아가는 동안에만 수행된다.
SET CONSTRAINTS ALL DEFERRED;
ALTER SESSION SET CONSTRAINT = DEFERRED;
#) INSERT문 + COMMIT 문 수행
- commit 시 체크제약조건의 위반여부 확인
insert into emp_sal (salary, bonus) values (90, 5);
insert into emp_sal (salary, bonus) values (101, -5);
commit;
#) 테이블 내에 DATA 존재하지 않음
select * from hr.emp_sal;
# 현재 트랜잭션을 수행하는 동안 모든 제약조건 체크는 즉시 체크하겠다.
- 제약 조건 IMMEDIATE 로 변경 (아래 2개의 SQL문장 가능)
SET CONSTRAINTS ALL IMMEDIATE;
ALTER SESSION SET CONSTRAINT = IMMEDIATE;
#) INSERT문 + COMMIT 문
- IMMEDIATE 로 변경해서 즉시 체크함.
INSERT INTO emp_sal (salary, bonus) VALUES (200, -5);
# bonus_ck 제약조건 여부 체크를 commit 시 체크하도록 설정
- ALL 대신 체크제약조건으로 지정.
- bonus_ck 에 설정 했더라도 설정한 체크제약조건만 해당될 뿐, 다른 제약조건에 영향을 주지 않는다.
SET CONSTRAINTS bonus_ck DEFERRED;
#) INSERT문 + COMMIT 문 실행
INSERT INTO emp_sal (salary, bonus) VALUES (200, -5);
COMMIT;
#) 연습용 테이블 삭제
DROP TABLE emp_sal PURGE;
#1) 연습용 NOT DEFERRABLE 제약조건인 테이블 생성
CREATE TABLE emp_sal (
salary NUMBER CONSTRAINT sal_ck CHECK ( salary > 100 ) NOT DEFERRABLE INITIALLY IMMEDIATE,
bonus NUMBER CONSTRAINT bonus_ck CHECK ( bonus > 0 ) NOT DEFERRABLE INITIALLY IMMEDIATE
);
#2) 테이블 제약조건 생성 확인
SELECT * FROM user_constraints WHERE table_name = 'EMP_SAL';
#3-1) INSERT문 실행 ⓐ
INSERT INTO emp_sal (salary, bonus) VALUES (90, 5);
- 오류발생
#3-2) INSERT문 실행 ⓑ
INSERT INTO emp_sal (salary, bonus) VALUES (200, -5);
- 오류발생
#4) NOT DEFERRABLE 로 설정되어 있을 경우 설정 변경이 안된다. (주의!)
- 제약조건 지연되게 제약조건 변경
SET CONSTRAINTS ALL DEFERRED;
#5) 오류발생 : INSERT문
INSERT INTO emp_sal (salary, bonus) VALUES (90, 5);
INSERT INTO emp_sal (salary, bonus) VALUES (200, -5);
#6) status, validated 는 같이 봐야 한다.
SELECT *
FROM user_constraints
WHERE table_name = 'EMP_SAL';
SELECT status, validated FROM user_constraints WHERE table_name = 'EMPLOYEES';
#1) 연습용 동일한 테이블명이 있다면 삭제
DROP TABLE test PURGE;
#2) 연습용 기본적인 테이블 생성
CREATE TABLE test (
id NUMBER,
name VARCHAR(10),
sal NUMBER
);
#3) INSERT 문 실행 및 영구히 저장
INSERT INTO test(id, name, sal) VALUES (1, 'a', 1000);
INSERT INTO test(id, name, sal) VALUES (2, 'b', 100);
INSERT INTO test(id, name, sal) VALUES (1, 'a', 2000);
COMMIT;
#4) DATA 확인
SELECT * FROM test;
#5) 테이블의 제약조건 확인
SELECT * FROM user_constraints WHERE table_name = 'TEST';
#6) 제약조건 추가
ALTER TABLE TEST ADD CONSTRAINT test_id_pk PRIMARY KEY(ID);
- 오류발생
#7) 비활성화로 제약 조건 변경 : DISABLE
ALTER TABLE hr.test ADD CONSTRAINT test_id_pk PRIMARY KEY ( id ) DISABLE;
- 설정은 하되, 제약조건 체크하지 않음.
#8) 변경한 제약조건 확인
SELECT * FROM user_constraints WHERE table_name = 'TEST';
SELECT owner, constraint_name, constraint_type, table_name, status, validated
FROM user_constraints WHERE table_name = 'TEST';
#9) 터미널창에서 실행.
(sql developer 창과 동일한 session에서 실행할 것. ex: hr계정이면 sqlplus에서는 hr session에서 실행)
SQL> conn hr/hr
SQL> show user
SQL> @$ORACLE_HOME/rdbms/admin/utlexpt1 -- 테이블 생성(로컬에서 생성)
SQL> desc exceptions
- rowid : 행(row)의 위치, 18자리
#10) ROWID 확인
SELECT rowid, id, name, sal FROM test;
ROWID | 해석 | 확인 |
AAAVpS | 객체번호 data object id(6자리) |
-- 객체 확인, OBJECT_ID(고유번호) select * from user_objects where object_name = 'TEST'; |
AAE | file id(3자리) | -- file 확인, tablespace_name select * from user_segments where segment_name = 'TEST'; -- DBA(SYS) 창에서 확인 select * from dba_data_files; |
AAAAJN | block id(6자리) | -- DBA(SYS) 창에서 확인, header_file, blocks 컬럼 확인 select * from dba_segments where segment_name = 'TEST'; -- header_file, blocks 컬럼 확인 select * from dba_segments where segment_name = 'TEST'; -- block_id, blocks 컬럼 확인 select * from dba_extents where segment_name = 'TEST'; |
AAA | row slot(3자리) |
#TIP) 한 block의 갯수
SELECT rowid, employee_id FROM hr.employees;
※ data 찾는 방법 : ROWID SCAN 이 가장 빠름
SELECT *
FROM hr.employees
WHERE rowid = 'AAAVPhAAFAAAADPAAA';
SELECT *
FROM hr.employees
WHERE employee_id = 100;
- 실행계획에서 확인했을 때, EMP_EMP_ID_PK에서 ROW_ID를 찾기 → BY INDEX ROWID 에서 ROWID 찾기
☆ 제약조건 primary-key, unique : INDEX 자동 생성
#1) DATA 중복 체크
- data 추가
INSERT INTO test(id, name, sal) VALUES (5, 'j', 3000);
COMMIT;
#2) data 확인 및 제약조건 확인
SELECT * FROM hr.test;
SELECT * FROM user_constraints WHERE table_name = 'TEST';
#3) 제약조건 추가
ALTER TABLE test ENABLE CONSTRAINT test_id_pk -- 제약조건을 활성화로 조건 변경
EXCEPTIONS INTO exceptions; --문제가 발생한다면, 해당 테이블에 fetch 하기
#4) 중복된 data를 rowid 확인
SELECT * FROM EXCEPTIONS;
#5) 중복된 data 확인
SELECT rowid, t.*
FROM test t
WHERE ROWID IN ( SELECT row_id FROM exceptions );
#6) 중복된 data 변경 작업 후 data 저장. 확
UPDATE TEST
SET ID = 6
WHERE ROWID = 'AAAVpyAAEAAAAJNAAC';
COMMIT;
SELECT * FROM test;
#7) 제약조건 확인
SELECT * FROM user_constraints WHERE table_name = 'TEST';
#8) exceptions 내용 지우기
TRUNCATE TABLE EXCEPTIONS;
#9) 제약조건 변경
- 활성화로 변경
ALTER TABLE test ENABLE CONSTRAINT test_id_pk
EXCEPTIONS INTO exceptions; --문제가 발생한다면, 해당 테이블에 fetch 하기
#10) 제약조건 확인 : enabled 변경 확인
SELECT * FROM user_constraints WHERE table_name = 'TEST';
※ data 중복시 제약조건 추가 및 변경 순서
1. ALTER TABLE hr.test ADD CONSTRAINT test_id_pk PRIMARY KEY ( id ) DISABLE; -- 필수X, 생략가능
2. @$ORACLE_HOME/rdbms/admin/utlexpt1 -- 테이블 생성(로컬에서 생성)
3. ALTER TABLE test ENABLE CONSTRAINT test_id_pk EXCEPTIONS INTO exceptions; -- 제약조건 추가할 때, 문제 발견시 exceptions으로 넣어주기
4. SELECT rowid, t.* FROM test t WHERE ROWID IN ( SELECT row_id FROM exceptions ); -- 중복 data 확인
5. 중복된 data 변경 및 삭제 : 업무팀에 요청, DML문 실행
6. truncate table exceptions; -- 기존 data 지우기
7. ALTER TABLE test ENABLE CONSTRAINT test_id_pk EXCEPTIONS INTO exceptions; -- 제약조건 생성(문제발견X)
8. select * from user_constraints where table_name = 'TEST'; -- 제약조건 확인
SELECT * FROM user_constraints WHERE table_name = 'TEST';
#1) sal 컬럼 check 제약조건 추가
- 테이블 확인
SELECT * FROM hr.test;
#2) 새로운 data에 대해서만 check 제약 조건에 검증할 것.
ALTER TABLE TEST ADD CONSTRAINT test_sal_ck CHECK(sal >= 1000)
ENABLE NOVALIDATE;
#3) 생성된 제약조건 확인
SELECT * FROM user_constraints WHERE table_name = 'TEST';
#5) INSERT문 수행
INSERT INTO TEST(ID, NAME, sal) VALUES(10, 'j', 500);
# enable novalidate -> enable validate 변경
→ 해결) exceptions 를 활용할 것
#1) 테이블에 제약조건 확인
SELECT * FROM user_constraints WHERE table_name = 'TEST';
#2-1) 값이 존재하는지 확인 후 지우기
SELECT * FROM EXCEPTIONS;
TRUNCATE TABLE EXCEPTIONS;
#2-2) 제약조건 변경
ALTER TABLE TEST ENABLE VALIDATE CONSTRAINT test_sal_ck EXCEPTIONS INTO exceptions;
#3) data 확인
SELECT * FROM EXCEPTIONS;
#4) check 제약조건 위반되는 data 확인
SELECT ROWID, T.*
FROM TEST T
WHERE ROWID IN (SELECT row_id FROM EXCEPTIONS);
#5) 변경 작업 후 저장
UPDATE test
SET sal = 1001
WHERE ROWID = 'AAAVpyAAEAAAAJNAAB';
COMMIT;
#6) 변경된 data 확인
SELECT * FROM test;
#7) 기존에 있는 DATA 지우기
TRUNCATE TABLE exceptions;
#8) 제약 조건 추가
ALTER TABLE TEST ENABLE VALIDATE CONSTRAINT test_sal_ck EXCEPTIONS INTO exceptions;
#9) 제약조건 확인
SELECT * FROM user_constraints WHERE table_name = 'TEST';
#1) DISABLE VALIDATE 변경
ALTER TABLE TEST DISABLE VALIDATE CONSTRAINT test_sal_ck;
#2) 변경제약조건 확인
SELECT * FROM user_constraints WHERE table_name = 'TEST';
#3) DML문 불허
INSERT INTO test(id, name, sal) VALUES (10, 'j', 3000);
#1) DISABLE NOVALIDATE 변경
ALTER TABLE TEST DISABLE NOVALIDATE CONSTRAINT test_sal_ck;
#2) 변경제약조건 확인
SELECT * FROM user_constraints WHERE table_name = 'TEST';
#3) DML문 가능
INSERT INTO test(id, name, sal) VALUES (10, 'j', 3000);
SELECT * FROM hr.test;
#1) DISABLE VALIDATE 변경
ALTER TABLE TEST DISABLE VALIDATE CONSTRAINT test_sal_ck;
#2) 변경제약조건 확인
SELECT * FROM user_constraints WHERE table_name = 'TEST';
#3) DML문을 불허
INSERT INTO test(id, name, sal) VALUES (10, 'j', 3000);
SELECT * FROM hr.test;
'Data Base > Linux' 카테고리의 다른 글
240103 Oracle DB Liunx_데이터 이관 작업 순서(연습) (0) | 2024.01.04 |
---|---|
240103 Oracle DB Linux_데이터 제약 조건 상태, 데이터 이관 작업 (1) | 2024.01.03 |
231228 Linux_감사 정책, 종속성 관계, 컴파일 (0) | 2023.12.28 |
231227 Linux_DBA 감사, TRIGGER (1) | 2023.12.27 |
231227 Linux_권한, sys.aud$ 이관작업, SQL문 감사, 객체 감사, 시스템 권한 감사, Find Grained Auditing (1) | 2023.12.27 |