[SQL] 스키마 객체 관리

봄·2025년 6월 4일
0

SQL

목록 보기
14/126

무결성: 정확성과 일관성을 유지 및 보증하는 것

제약조건 구문 추가

ALTER TABLE문을 사용하여 다음을 수행

  • 제약조건 추가 또는 삭제. 제약조건의 구조는 수정하지 않음
  • 제약조건 활성화 또는 비활성화
  • MODIFY절을 사용하여 NOT NULL 제약조건 추가
ALTER TABLE <table_name>
ADD [CONSTRAINT <constraint_name>]
type (<colun_name>);
ALTER TABLE emp2
MODIFY employee_id PRIMARY KEY;

제약조건 삭제

📌 primary key를 참조하고 있는 foreign key가 있을 수 있기 때문에
제약조건을 연쇄화시켜서 같이 날려버릴 수 있도록 `CASCADE 써줘야 됨

CONSTRAINT ONLINE 삭제

ONLINE 키워드를 지정하여 제약조건을 삭제하는 동안
테이블에서의 DML 작업이 허용됨을 나타냄

ON DELETE 절

상위 키가 삭제될 때 하위 행도 삭제하려면
ON DELETE CASCADE절 사용

ALTER TABLE dept2 ADD CONSTRAINT dept_lc_fk
FOREIGN KEY (location_id)
REFERENCES locations(location_id) ON DELETE CASCADE;

상위 키가 삭제될 때 하위 행 값을 null로 설정하려면
ON DELETE SET NULL절 사용

ALTER TABLE dept2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY (department_id)
REFERENCES locations(department_id) ON DELETE SET NULL;

계단식 제약조건

  • CASCADE CONSTRAINTS 절
    • DROP COLUMN 절과 함께 사용
    • 삭제된 열에 정의된 PRIMARY 및 UNIQUE 키를 참조하는 모든 참조 무결성 제약조건 삭제
    • 삭제된 열에 정의된 모든 다중 열 제약조건 삭제
ALTER TABLE emp2
DROP COLUMN employee_id CASCADE CONSTARINTS;

예를 들어, employee_id가 pk(primary key)이고 이 pk 값을 참조하는 fk(foreign key)가 있다면, 컬럼을 먼저 삭제하는 것이 불가능할 때가 있음
--> CASCADE CONSTARINTS를 사용해서 같이 연쇄화시켜 삭제하라는 의미

같은 테이블에 존재한다면 묶어서 한번에 삭제 가능

ALTER TABLE test1
DROP (col1_pk, col2_fk, col1);

테이블 열 및 제약조건 이름 바꾸기

테이블의 이름을 바꾸려면 ALTER TABLE문의 RENAME TABLE절 사용

ALTER TABLE marketing RENAME to new_marketing;

테이블 열의 이름을 바꾸려면 ALTER TABLE문의 RENAME COLUMN절 사용

ALTER TABLE new_marketing RENAME COLUMN team_id TO id;

테이블에 대한 기존 제약조건의 이름을 바꾸려면 ALTER TABLE문의 RENAME CONSTRAINT절 사용

ALTER TABLE new_marketing RENAME CONSTRAINT mktg_pk TO new_mktg_pk;

예제

-- 나를 참조하고 있는 fk가 있어서 drop 안됨
ALTER TABLE copy_emp
DROP PRIMARY KEY;

-- 나를 참조하고 있던 fk 먼저 지우고
ALTER TABLE copy_emp
DROP CONSTRAINT cpemp_mgr_fk;

-- 그러고나서 pk 삭제하면 됨
ALTER TABLE copy_emp
DROP PRIMARY KEY;

-- pk제약조건을 삭제할 때
-- 나를 참조하고 있는 fk를 같이 연쇄화시켜 삭제가능
ALTER TABLE copy_emp
DROP PRIMARY KEY CASCADE;

-- copy_dept의 컬럼삭제
-- 참조되고있는 제약조건 있으면 오류남
ALTER TABLE copy_dept 
DROP (department_id) ; 

-- 문제가 되는 제약조건 먼저 연쇄화시켜 날려버림
ALTER TABLE copy_dept 
DROP (department_id) CASCADE CONSTRAINTS ; 

-- 테이블이름 변경
ALTER TABLE copy_dept RENAME TO cp_dept ; 
-- 다시 되돌아옴
ALTER TABLE cp_dept   RENAME TO copy_dept ; 
-- 컬럼이름 변경
ALTER TABLE copy_dept RENAME COLUMN manager_id TO mgrid ;

제약조건 비활성화

ALTER TABLE문의 DISABLE절을 실행하여 무결성 제약조건을 비활성화 함
Primary key를 비활성화하려면 CASCADE 옵션을 적용
--> 그러면 종속된 모든 foreign key 제약조건도 자동으로 비활성화

ALTER TABLE employees 
DISABLE CONSTRAINT EMP_EMAIL_UK ;

제약조건 활성화

다시 검색하겠다는 의미
ENABLE절을 사용하여 테이블 정의에서 현재 비활성화된 무결성 제약조건을 활성화 함
unique key 또는 primary key 제약조건을 활성화하면
--> unique 인덱스 자동 생성

ALTER TABLE employees 
ENABLE CONSTRAINT EMP_EMAIL_UK ;

제약조건 상태

테이블에 정의된 무결성 제약조건의 상태는 다음 중 하나일 수 있음

  • ENABLE VALIDATE (default값)
  • ENABLE NOVALIDATE
  • DISABLE VALIDATE
  • DISABLE NOVALIDATE (default값)

disable 상태라면 --> 제약이 꺼져있으므로 insert 가능

  • VALIDATE: 유효성 검증 하겠다
  • NOVALIDATE: 유효성 검증 안하겠다
  • 비활성화 됐다(disable): 제약조건에 위배되는 값도 들어갈 수 있다는 의미

예제

-- 오류발생: 0은 들어올 수 없는 값
UPDATE employees 
SET salary = 0
WHERE employee_id = 101 ;

-- disable: 제약조건에 위배되는 값도 들어갈 수 있음
ALTER TABLE employees 
DISABLE NOVALIDATE CONSTRAINT EMP_SALARY_MIN ;

-- 성공
UPDATE employees 
SET salary = 0
WHERE employee_id = 101 ;

-- 오류발생: 잘못된 값(salary = 0)이 이미 들어가 있어서
-- 현재 삽입된 데이터가 제약조건 위배상태
ALTER TABLE employees 
ENABLE VALIDATE CONSTRAINT EMP_SALARY_MIN ;

-- 신규 데이터 제약조건 활성 + novalidate(기존 데이터 유효성 검증 제외) 상태
-- Enable: 신규 데이터의 제약조건은 반영
-- Novalidate: 위배된 데이터는 유지
ALTER TABLE employees 
ENABLE NOVALIDATE CONSTRAINT EMP_SALARY_MIN ;

-- 성공
SELECT employee_id, salary 
FROM employees 
WHERE employee_id = 101 ;

-- 오류발생: 신규 데이터는 제약조건 활성화(Enable) 상태이므로 삽입 불가
UPDATE employees 
SET salary = 0
WHERE employee_id = 102 ;
  • Enable: 신규 데이터의 제약조건은 반영
  • Novalidate: 위배된 데이터는 유지

제약조건 지연

DEFERRABLE 또는 NOT DEFERRABLE
INITIALLY DEFERRED 또는 INITIALLY IMMEDIATE

제약조건의 검증 시기를 지연시킬 수 있음

  • INITIALLY DEFERRED: 트랜잭션이 종료될 때까지 제약조건 검사를 기다림
  • INITIALLY IMMEDIATE: 명령문 실행이 완료되면 제약조건을 검사함
ALTER TABLE copy_emp 
ADD PRIMARY KEY (employee_id) DEFERRABLE INITIALLY DEFERRED ENABLE VALIDATE; 

SELECT * 
FROM copy_emp ;

-- 중복값이 생기니까 원래는 에러 나야함
UPDATE copy_emp 
SET employee_id = 100 
WHERE employee_id = 101 ;

-- 100번이 2개 생김
SELECT * 
FROM copy_emp ;

-- 오류발생 -> 롤백됨
COMMIT ;

-- 100번 행 하나 다시 101번으로 바뀜
SELECT * 
FROM copy_emp ;

삭제

DROP TABLE ... PURGE
purge: 임시로 저장하지 않고 완전 삭제 옵션

✔️ 임시 테이블 생성

⭐ 두개 잘 구분해야 함

📌 1. TEMPORARY ~ COMMIT DELETE ROWS

CREATE GLOBAL TEMPORARY TABLE cart(n NUMBER, d DATE)
ON COMMIT DELETE ROWS;

트랜잭션이 유지되는 동안에만 데이터가 살아있고
COMMIT이 들어가면 데이터가 싹 지워짐

테이블이 임시로 생성되며 commit시 데이터 삭제

📌 2. TEMPORARY ~ COMMIT PRESERVE ROWS

CREATE GLOBAL TEMPORARY TABLE today_sales
ON COMMIT PRESERVE ROWS AS
	SELECT * FROM orders
    		WHERE order_date = SYSDATE;

세션이 유지되는 동안에만 데이터가 살아있음

세션이 종료되면 테이블 데이터 삭제

예제



CREATE GLOBAL TEMPORARY TABLE cart
(id   NUMBER(2),
 name VARCHAR2(10))
ON COMMIT DELETE ROWS; 

-- insert됨
INSERT INTO cart VALUES (1, 'A') ;
-- 데이터 검색됨
SELECT * FROM cart ;

COMMIT ;
-- 데이터가 사라짐
SELECT * FROM cart ;

CREATE GLOBAL TEMPORARY TABLE cart2 
(id   NUMBER(2),
 name VARCHAR2(10))
ON COMMIT PRESERVE ROWS; 

INSERT INTO cart2 VALUES (1, 'A') ;
COMMIT ;

-- 데이터 안사라지고 그대로 있음
-- 접속 끊었다가 다시 들어오면 데이터 사라짐
SELECT * FROM cart2 ;

External Table

디렉토리 생성

외부 데이터를 가져올 때

SYSTEM> CREATE OR REPLACE DIRECTORY emp_dir AS 'C:\DATA' ; 
SYSTEM> GRANT read, write ON DIRECTORY emp_dir TO ORA1 ;

읽기 전용 테이블이기 때문에 DML(insert, update, delete)은 안됨

테이블이 만들어지면 해당 테이블의 정의사항은 DB 안에 있지만,

실제 데이터는 밖에 있음
--> 조회 작업을 통해 밖에 있는 것을 가져다가 보여줄 수 있음

profile
봄
이전 포스트

[SQL] 뷰(view)

0개의 댓글