240103 Oracle DB Linux_ 데이터 제약 조건 체크 여부

Data Base/Linux

240103 Oracle DB Linux_ 데이터 제약 조건 체크 여부

잇꼬 2024. 1. 3. 18:54

728x90

SMALL

■ 제약 조건 체크 여부

※ 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;

728x90

LIST

저작자표시

'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

현재글240103 Oracle DB Linux_ 데이터 제약 조건 체크 여부

250x250

안녕? 나야 :)