제약 조건
FK 제약조건
포링키로 제약조건이 설정되어 있을 때 함부로 삭제할 수 없다. -> 종속 관계
옛날에는(8I) 포링키가 수정되어 있을 때 CHILD까지 LOCK이 걸려 문제가 되었다.(종속관계)
해당 문제의 해결방법으로 INDEX 지정을 제시했지만, 이는 DBA에게도 위험한 방법이다.
하지만 9I부터는 위 문제를 해결하였으므로 FK를 걸어주는 법 하자.
CASCADE
나를 참고하고 있는 제약조건들을 먼저 지우고 테이블 삭제
DROP TABLE hr.emp CASCADE CONSTRATINT PURGE;- DROP TABLE 할 수 있는 사람은?
테이블의 소유자, DBA (DROP ANY TABLE) - ANY: 다른 사람의 것 까지 접근 가능
CTAS를 이용한 테이블 복제시 제약 조건
테이블의 구조와 데이터가 그대로 복제되지만, 제약 조건은 not null만 복제되는 것을 확인할 수 있다.
제약조건 생성시 오류
alter table hr.dept add constraint dept_dept_id_pk PRIMARY KEY(department_id);
alter table hr.emp add constraint emp_dept_id_pk FOREIGN KEY(department_id) REFERENCES hr.dept(department_id);위와 같이 제약조건을 생성할 때 데이터 품질에 따라 제약이 생성되지 않을 때도 있다.
fk 제약조건 on delete cascade
FK제약조건의 옵션으로 PK에 해당하는 행을 삭제할 때 FK쪽에 child data가 있으면 child data를 먼저 삭제하고 PK를 삭제하게 하는 옵션.
DELETE FROM hr.dept where department_id = 10;위 코드의 경우 10번 부서에 해당하는 child record가 존재하기 때문에 삭제되지 않는다.
제약조건을 삭제하지 않고 10번 부서를 지우고 싶으면, 10번 부서에 해당하는 child records를 삭제하면 가능하다.
이를 가능하게 하는 옵션이 cascade이다.
alter table hr.emp add constraint emp_dept_id_pk FOREIGN KEY(department_id) REFERENCES hr.dept(department_id) on delete cascade;
delete rule에 이전에는 no action이었던 것이 cascade로 수정된 것을 확인할 수 있다.
이 경우 해당 fk에 해당하는 record를 삭제시 child record 또한 같이 연쇠적으로 삭제 되는 것을 확인할 수 있다.
즉 제약조건에 해당하는 child record를 먼저 삭제하고 parent reocrd를 삭제한다.
child record를 삭제하지 않아야 한다면 제약조건을 끊고 삭제해야 한다. 또는 SET NULL 옵션을 사용하여 해당하는 데이터 값을 NULL로 지정해야 한다.
DELETE + DELETE
fk 제약조건 ON DELETE SET NULL 옵션
삭제시 child 데이터 행을 지우는 것이 아닌, child 데이터의 칼럼 값을 null 값으로 변경시켜 부모키에 해당하는 데이터를 삭제한다.
alter table hr.emp add constraint emp_dept_id_pk FOREIGN KEY(department_id) REFERENCES hr.dept(department_id) on delete set null;
해당 부서를 삭제하면 사원의 데이터는 존재하면서, 부서_id 칼럼의 데이터는 null 값으로 변경된다.
UPDATE + DELETE
테이블 생성 + 제약조건 생성
- 테이블의 소유자계정으로 접속해 진행한다.
열 레벨 정의라고도 한다.
NOT NULL제약조건은 반드시 열 레벨 정의로 해야함!
CREATE TABLE hr.dept(
dept_id number CONSTRAINT dept_dept_id_pk PRIMARY KEY,
dept_name varchar2(30) CONSTRAINT dept_name_uk UNIQUE);테이블 생성시 열을 정의하면서 제약조건도 같이 정의할 수 있다. 제약조건에 따로 열을 제시할 필요 없음!
한번에 2개의 제약조건을 같이 지정하고 싶을 때는 콤마,를 사용해서는 안된다.
CREATE TABLE hr.emp(
id number CONSTRAINT emp_id_pk PRIMARY KEY,
name varchar2(30) CONSTRAINT emp_name_nn NOT NULL
CONSTRAINT emp_name_uk UNIQUE, ...FK의 열 레벨 정의!
- FOREIGN KEY(COL)을 제거하고 바로 REFERENCES를 소환한다.
CREATE TABLE hr.emp(
id number CONSTRAINT emp_id_pk PRIMARY KEY,
name varchar2(30) CONSTRAINT emp_name_nn NOT NULL
CONSTRAINT emp_name_uk UNIQUE,
sal number,
dept_id number CONSTRAINT emp_dept_id_fk REFERENCES hr.dept(dept_id) -- FK의 열 레벨 정의
CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000));테이블 레벨 정의라고 한다.
NOT NULL제약조건은 테이블 레벨 정의로 할 수 없다. (MODIFY로 설정하기 때문)
CREATE TABLE hr.emp(
id number CONSTRAINT emp_id_pk PRIMARY KEY,
dept_name varchar2(30) CONSTRAINT emp_name_nn NOT NULL,
sal number,
dept_id number,
CONSTRAINT emp_name_uk UNIQUE(name),
CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000),
CONSTRAINT emp_dept_id_fk FOREIGN KEY(name) REFERENCES hr.dept(dept_id));보편적으로 테이블을 만들며 제약조건을 같이 추가한다.
참조하고 있는 pk에 대하여
alter table hr.dept drop constraint dept_dept_id_pk;칼럼 삭제와, 제약조건 삭제는 다르게 봐야한다.
해당 제약을 참조하고 있는 FK가 존재하기 때문에 지울 수 없다.
참조하고 있는 FK 제거
alter table hr.emp drop constraint emp_dept_id_fk;
alter table hr.dept drop constraint dept_dept_id_pk;CASCADE로 자동 삭제
ALTER TABLE hr.dept DROP CONSTRAINT dept_dept_id_pk CASCADE;FK도 같이 삭제됨.
테이블 이름 수정
요청이 들어오면 각팀의 수장들과 합의하에 충분한 합의가 진행된 후 DBA가 수정.
RENAME TO
소유자의 이름을 쓸 수 없음! 즉 소유자만 이름을 변경할 수 있다. (성을 바꿀 순 없자냐...)
RENAME emp TO new_emp;제약조건 자체는 변하지 않지만, 참고하고 있는 테이블에 대해서는 문제가 발생할 수 있다.
ALTER TABLE
여기서도 당연히 변경하고자 하는 이름에 소유자의 이름을 쓸 수 없다.
ALTER TABLE emp RENAME TO emp_new;
ALTER TABLE hr.emp RENAME TO emp_new;칼럼 이름 수정
ALTER TABLE RENAME COLUMN
ALTER TABLE 소유자.테이블 RENAME COLUMN old to new;
ALTER TABLE hr.emp RENAME COLUMN id to emp_id;PK 제약조건이 있어도 아무런 문제가 되지 않는 것을 확인할 수 있다.
인덱스에도 PK에 대해서 칼럼명이 변경되어도 문제가 되지 않는다.
단, 해당 칼럼을 참조하고 있는 데이터에 대해서는 문제가 될 수 있다.
제약 조건 이름 수정
ALTER TABLE 소유자.테이블 RENAME CONSTRAINT old to new;
ALTER TABLE hr.emp RENAME CONSTRAINT emp_id_pk to emp_pk;PK에 대한 제약조건 명은 수정되었지만, pk에 대한 인덱스의 이름은 수정되지 않았다.
인덱스 이름 수정
ALTER INDEX 인덱스 이름 RENAME TO 새로운이름;
지우고 생성하면 안되나요..? NO!
ALTER INDEX emp_id_pk RENAME TO emp_id_idx;
flashback table
삭제한 테이블을 복원하는 SQL
삭제한 테이블에 대한 정보는 다음과 같이 조회할 수 있다.
SHOW recyclebin;
select * from user_recyclebin;
FLASHBACK TABLE emp TO BEFORE DROP;플래시 백을 진행하면 recyclebin에 내용은 없어지고 테이블을 복구할 수 있다.
테이블의 이름은 자동으로 복원되지만, 복원한 인덱스, 제약조건에 대한 이름이 달라진 것을 확인할 수 있다.
-
제약조건 이름
-
인덱스 이름
이렇게 달라진 이름에 대해 rename을 진행하여 수정할 수 있다.
flashback의 recyclename을 이용해서도 데이터를 조작할 수 있다.
즉, originalname을 recyclename으로 바꾼 것이다.
같은 이름의 테이블에 대해 flashback을 진행할 경우 drop타임이 가장 최근의 것을 우선 복원한다.
존재하고 있는 테이블의 이름에 대해 flashback을 진행할 경우 오류가 발생할 수 있다.
이때 rename을 진행하여 복원할 수 있다.
FLASHBACK TABLE emp TO BEFORE DROP RENAME TO emp_2024;이러한 방식으로 복구하면 불완전한 복구방식을 통하지 않고서도 복구할 수 있다.
recyclebin의 데이터는 언제까지 가지고 있을까?
users의 스토리지(quotas)에 여유공간이 있으면 계속 가지고 있고 부족하다면 오래된 데이터부터 없앰
PURGE
purge recyclebin;recyclebin의 데이터를 제거함.
DROP TABLE hr.dept PURGE;drop 실행시 recyclebin에 데이터가 저장되지 않음.
잘못 삭제하면 불완전한 복구를 진행해야 함
날짜 타입
select
sysdate, -- 서버
systimestamp,
current_date, -- client 세션
current_timestamp,
localtimestamp
from dual;date
년,월,일을 보여준다
sysdate, current_date가 해당한다.
timestamp
년,월,일 + 시,분,초(초이하 9자리) 까지 표현한다.
localtimestamp가 여기에 해당한다.
timestamp with timezone
timespace + timezone(시간, 이름)을 표현한다.
systimestamp, current_timestamp가 여기에 해당한다.
timestamp with localtimezone
보는 지역에 따라 날짜 시간이 자동으로 정규화해주는 날짜 타입
time_zone을 '+08:00'으로 변경해주면 다음과 같이 1시간 이전의 시간으로 정규화 된 것을 볼 수 있다.
alter session set time_zone = '+08:00';
날짜 형변환
to_date
문짜 날짜를 날짜형(date)으로 변환하는 함수.
SELECT to_date('20240626','yyyymmdd') FROM dual;to_timestamp
문짜 날짜를 날짜형(timestamp)으로 변환하는 함수.
SELECT to_timestamp('20240626 16:21:33.123456','yyyymmdd hh24:mi:ss.ff')
-- 최대 소숫점 9자리까지 표현 가능하므로 ff를 쓰면 자동으로 9자리 배정.
FROM dual;
to_timestamp_tz
문짜 날짜를 날짜형(timestamp with time zone)으로 변환하는 함수.
SELECT to_timestamp_tz('20240626 16:21:33.123456','yyyymmdd hh24:mi:ss.ff')
-- 최대 소숫점 9자리까지 표현 가능하므로 ff를 쓰면 자동으로 9자리 배정.
FROM dual;현재 세션을 반영한 타임존을 보여준다.
SELECT to_timestamp_tz('20240626 16:21:33.123456789 +08:00','yyyymmdd hh24:mi:ss.ff tzh:tzm')
-- tzh, tzm으로 타임존 설정 가능
FROM dual;
to_yminterval
문자 날짜를 날짜형(interval year to month)으로 변환하는 함수, 기간 명시
SELECT
sysdate,
add_months(sysdate,14),
sysdate + to_yminterval('1-2'), -- 1년 2개월 더함
sysdate - to_yminterval('1-2'), -- 1년 2개월 뺌
sysdate + to_yminterval('-1-2') -- 1년 2개월 뺌
FROM
dual;
add_month랑 같음.
to_yminterval('년수-개월수')
tips : 년수와 개월수를 같이쓸 때 개월수는 최대 11까지 표기 가능하다.
to_dsinterval
문자 날짜를 날짜형(interval day to second)으로 변환하는 함수, 기간 명시
to_yminterval('일수 시분초')
날짜 + 일수 와 같다.
SELECT
localtimestamp,
localtimestamp + to_dsinterval('100 10:00:00.123456789'), -- 100일 10시간 00분 00.123456789초
localtimestamp + to_dsinterval('100 00:00:00') -- 100일
FROM
dual;
tips
날짜 + 일수 = 날짜
날짜 - 일수 = 날짜
날짜 - 날짜 = 일수
날짜 + 날짜 = 오류
날짜 + interval year to month = 날짜
날짜 + interval day to second = 날짜
interval 자료형
CREATE TABLE hr.time_test_1(
a interval year(3) to month,
b interval day(3) to second);
에 대하여...
insert into hr.time_test_1(a,b)
values(to_yminterval('10-00'), to_dsinterval('100 00:00:00'));
-- 10년 추가 / 100일 추가
select * from hr.time_test_1;
다음과 같이 활용할 수 있다.
select localtimestamp + a, localtimestamp + b from hr.time_test_1;
LIKE 유통기한
느낀 점
1교시 cascade 학습 및 저번 시간 fk제약조건 복습
2교시 on delete set null
점심시간
1교시 flash back
2교시 recyclebin
3교시 purge 날짜 타입 종류
4교시 날짜 타입 관련 함수
dba로서 해야할 일
- 제약조건 설정 체크
제약조건이 따로 프로그램으로 지정되어 돌아가고 있는지... (성능 저하 요인)
