11 Unique, Check, Not Null, Rename, Flashback, Truncate, Comment, View

-- 제약조건 정보 확인
SELECT * FROM user_constraints WHERE table_name IN ('EMP','DEPT');
SELECT * FROM user_cons_columns WHERE table_name IN ('EMP','DEPT');

-- PRIMARY KEY 제약조건 삭제
ALTER TABLE hr.emp DROP CONSTRAINT emp_id_pk;
또는
ALTER TABLE hr.dept DROP PRIMARY KEY;
    -- 오류 : 나를 참조하고 있는 FOREIGN KEY 제약조건이 있다.

<해결방법1> FOREIGN KEY 삭제 후 PRIMARY KEY 삭제
ALTER TABLE hr.emp DROP CONSTRAINT emp_dept_id_fk;
ALTER TABLE hr.dept DROP PRIMARY KEY;

<해결방법2> CASCADE 옵션은 나를 참조하고 있는 제약조건을 모두 삭제한 후 제약조건을 삭제한다.
ALTER TABLE hr.dept DROP PRIMARY KEY CASCADE;

-----
DROP TABLE hr.dept CASCADE CONSTRAINT PURGE;
DROP TABLE hr.dept CASCADE CONSTRAINTS PURGE;

- CASCADE CONSTRAINT : 참조하는 제약조건들 모드 삭제한 후  테이블을 삭제하는 옵션.
- PURGE : 영구 삭제

 

---

3. UNIQUE 제약조건
- 유일한 값만 체크
- 중복값 불허
- NULL 허용
- 자동으로 UNIQUE INDEX 생성

ALTER TABLE 테이블 ADD CONSTRAINT 명칭_uk UNIQUE(칼럼);

ALTER TABLE hr.dept ADD CONSTRAINT dept_name_uk UNIQUE(dept_name);

- 제약정보 조건 확인
SELECT * FROM user_constraints WHERE table_name IN ('EMP','DEPT');
SELECT * FROM user_cons_columns WHERE table_name IN ('EMP','DEPT');
SELECT * FROM user_indexes WHERE table_name IN ('EMP','DEPT');
SELECT * FROM user_ind_columns WHERE table_name IN ('EMP','DEPT');

INSERT INTO hr.dept(dept_id, dept_name) VALUES(10,'DA');
INSERT INTO hr.dept(dept_id, dept_name) VALUES(20,'DA'); -- 오류 : UNIQUE 제약조건에 위반
INSERT INTO hr.dept(dept_id, dept_name) VALUES(10,NULL); -- UNIQUE 제약조건에 NULL값 허용

SELECT * FROM hr.dept;


> UNIQUE 제약조건 삭제
1) ALTER TABLE hr.dept DROP CONSTRAINT dept_name_uk;
2) ALTER TABLE hr.dept DROP UNIQUE(dept_name);

 

---

 

4. CHECK 제약조건
- 조건의 값이 TRUE인 경우 INSERT, UPDATE 할 수 있도록 만드는 제약조건
- NULL값 허용
- 중복값 허용

ALTER TABLE 소유자.테이블명 ADD CONSTRAINT 제약명칭_ck CHECK(조건);

ALTER TABLE hr.emp ADD CONSTRAINT emp_sal_ck CHECK(sal >= 1000 AND sal <= 2000);
또는
ALTER TABLE hr.emp ADD CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000);

SELECT * FROM hr.emp;
ALTER TABLE hr.emp ADD sal number(10);

INSERT INTO hr.emp(id,name,day,sal) VALUES(1,'홍길동',sysdate,'1500');
INSERT INTO hr.emp(id,name,day,sal) VALUES(2,'박찬호',sysdate,'500');
    -- 오류 : CHECK 제약조건 조건식에 결과 같이 FALSE
INSERT INTO hr.emp(id,name,day,sal) VALUES(2,'박찬호',sysdate,null);
    -- CHECK 제약조건에 NULL값 허용

COMMIT;

UPDATE hr.emp SET sal = 500 WHERE id = 1;
    -- 오류 : CHECK제약조건 조건식에 결과값이 FALSE
UPDATE hr.emp SET sal = 1200 WHERE id = 1;
    -- CHECK제약조건 조건식에 결과값이 TURE이기 때문에 UPDATE된다.
SELECT * FROM hr.emp;


> CHECK 제약조건 삭제
ALTER TABLE hr.emp DROP CONSTRAINT emp_sal_ck;

 

---

 

5. NOT NULL 제약조건
- NULL값을 허용 할 수 없는 제약조건

-- 정보확인
SELECT * FROM user_constraints WHERE table_name IN ('EMP','DEPT');
SELECT * FROM user_cons_columns WHERE table_name IN ('EMP','DEPT');
DESC hr.dept

- NOT NULL 제약조건은 MODITY를 이용해서 추가해야 한다.
ALTER TABLE 소유자.테이블명 MODIFY 칼럼 CONSTRAINT 제약조건명_notnull NOT NULL;

ALTER TABLE hr.dept MODIFY dept_name CONSTRAINT dept_name_notnull NOT NULL;
- 이미 null값이 들어있는 경우에는 안먹힘

INSERT INTO hr.dept(dept_id, dept_name) VALUES(30,NULL);
    -- 오류 : NOT NULL 제약조건에 위반

UPDATE hr.dept SET dept_name = NULL WHERE dept_id = 1;
    -- 오류 : NOT NULL 제약조건에 위반

- NOT NULL 제약조건 삭제
1) ALTER TABLE hr.dept DROP CONSTRAINT dept_name_notnull;
2) ALTER TABLE hr.dept MODIFY dept_name NULL;


DROP TABLE hr.emp CASCADE CONSTRAINTS PURGE;
DROP TABLE hr.dept CASCADE CONSTRAINTS PURGE;



- 테이블 생성과 동시에 제약 설정
CREATE TABLE hr.dept(
                dept_id number CONSTRAINT dept_dept_id_pk PRIMARY KEY,
                dept_name varchar2(30));

CREATE TABLE hr.emp(
                id number CONSTRAINT emp_id_pk PRIMARY KEY,         -- 열레벨 정의
                name varchar2(30) CONSTRAINT emp_name_nn NOT NULL,
                sal number,
                dept_id number CONSTRAINT emp_dept_id_fk REFERENCES hr.dept(dept_id),
                CONSTRAINT emp_name_uk UNIQUE(name),                -- 테이블레벨 정의
                CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000));

-- NOT NULL 제약 조건은 무조건 열레벨 정의만 해야한다.

SELECT * FROM user_constraints WHERE table_name IN ('EMP','DEPT');



DROP TABLE hr.emp CASCADE CONSTRAINTS PURGE;

CREATE TABLE hr.emp(
                id number CONSTRAINT emp_id_pk PRIMARY KEY,
                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(dept_id) REFERENCES hr.dept(dept_id));

 

---

■ 테이블 이름 수정 -- 소유자만 가능.

방법1. RENAME 기존이름 TO 새로운이름;

RENAME emp TO emp_new;  -- 소유자 이름은 미사용

SELECT * FROM tab;
SELECT * FROM hr.emp_new;
SELECT * FROM user_tables WHERE table_name = 'EMP_NEW';
SELECT * FROM user_constraints WHERE table_name = 'EMP_NEW';
SELECT * FROM user_cons_columns WHERE table_name = 'EMP_NEW';
SELECT * FROM user_indexes WHERE table_name = 'EMP_NEW';
SELECT * FROM user_ind_columns WHERE table_name = 'EMP_NEW';


방법2. ALTER TABLE 기존이름 RENAME TO 새로운이름;

ALTER TABLE emp_new RENAME TO emp;

SELECT * FROM tab;
SELECT * FROM hr.emp;
SELECT * FROM user_tables WHERE table_name = 'EMP';
SELECT * FROM user_constraints WHERE table_name = 'EMP';
SELECT * FROM user_cons_columns WHERE table_name = 'EMP';
SELECT * FROM user_indexes WHERE table_name = 'EMP';
SELECT * FROM user_ind_columns WHERE table_name = 'EMP';



■ 칼럼 이름 수정

ALTER TABLE 테이블이름 RENAME COLUMN 기존컬럼 TO 새로운컬럼;

DESC hr.emp
ALTER TABLE hr.emp RENAME COLUMN id TO emp_id;

DESC hr.emp



■ 제약조건 이름 수정

ALTER TABLE 테이블이름 RENAME CONSTRAINT 기존제약조건이름 TO 새로운제약조건이름;

ALTER TABLE hr.emp RENAME CONSTRAINT emp_id_pk TO emp_empid_pk;

SELECT * FROM user_constraints WHERE table_name = 'EMP';
SELECT * FROM user_cons_columns WHERE table_name = 'EMP';



■ 인덱스 이름 수정

ALTER INDEX 인덱스이름 RENAME TO 새로운인덱스이름;

ALTER INDEX emp_emp_id_idx RENAME TO emp_id_idx;

 

---

 

■ FLASHBACK TABLE(10g)
삭제한 테이블을 복원하는 SQL문

PURGE RECYCLEBIN    -- 휴지통 비우기

-- 테스트 테이블 생성
CREATE TABLE hr.emp(
                id number CONSTRAINT emp_id_pk PRIMARY KEY,
                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(dept_id) REFERENCES hr.dept(dept_id));
SELECT * FROM hr.emp;

-- 테이블 삭제
DROP TABLE hr.emp;

-- 휴지통 보기
SHOW RECYCLEBIN
SELECT * FROM user_recyclebin;

SELECT * FROM "BIN$~~~"; -- 큰따옴표 사용

- 삭제한 테이블 복원 : 원래 이름으로 복원 됨
FLASHBACK TABLE emp TO BEFORE DROP;
SELECT * FROM hr.emp;

SELECT * FROM user_constraints WHERE table_name = 'EMP';
SELECT * FROM user_cons_columns WHERE table_name = 'EMP';
SELECT * FROM user_indexes WHERE table_name = 'EMP';
SELECT * FROM user_ind_columns WHERE table_name = 'EMP';
- 제약조건명, 인덱스명은 복구가 안되어 이름 수정해야한다. BIN$~~ 

- 제약조건 이름 수정
ALTER TABLE hr.emp RENAME CONSTRAINT "BIN$~~~" TO emp_empid_pk;

- 인덱스 이름 수정
ALTER INDEX "BIN$~~~" RENAME TO emp_id_idx;



예)
purge recyclebin    -- 휴지통 비우기

CREATE TABLE hr.emp_test
AS
SELECT * FROM hr.employees; -- 테이블 생성

SELECT * FROM hr.emp_test;

DROP TABLE hr.emp_test; -- 시간 후 삭제

show recyclebin
SELECT * FROM user_recyclebin;  -- 휴지통 보기

SELECT * FROM hr.emp_test;  -- 오류 : 테이블 없음

CREATE TABLE hr.emp_test
AS
SELECT * FROM hr.employees; -- 테이블 생성

SELECT * FROM hr.emp_test;

DROP TABLE hr.emp_test; -- 시간 후 삭제

show recyclebin
SELECT * FROM user_recyclebin;  -- 휴지통 보기 : 동명의 테이블 존재

FLASHBACK TABLE emp_test TO BEFORE DROP;    -- 테이블 복원
    -- recyclebin에 동일한 이름의 테이블이 있을 경우 가장 최근에 삭제한 테이블을 복원한다.

SELECT * FROM hr.emp_test;

SELECT * FROM user_recyclebin;  -- 휴지통 보기 : 예전꺼 하나 남음

FLASHBACK TABLE emp_test TO BEFORE DROP RENAME TO emp_2023;
    -- 복원해야할 테이블명이 이미 존재할 경우 새로운 이름으로 복원할 수 있다.
    
SELECT * FROM hr.emp_2023;

DROP TABLE hr.emp_2023 PURGE;   -- 영구 삭제

 

---

■ TRUNCATE
- 테이블의 행을 전부 삭제하는 SQL문
- DELETE문과 비슷하지만 TRUNCATE문은 저장공간을 초기화 상태로 만든다.
- TRUNCATE문은 ROLLBACK을 할 수 없으니 주의해야한다.

TRUNCATE TABLE hr.emp_test; -- Table HR.EMP_TEST이(가) 잘렸습니다.

SELECT * FROM hr.emp_test;

 

---

■ COMMENT
테이블과 컬럼의 주석(설명) 만드는 SQL문

- 테이블 주석 확인
SELECT * FROM user_tab_comments WHERE table_name = 'EMPLOYEES';

- 컬럼 주석 확인
SELECT * FROM user_col_comments WHERE table_name = 'EMPLOYEES';

- 테이블 주석 생성
COMMENT ON TABLE 소유자명.테이블명 IS '내용';
SELECT * FROM user_tab_comments WHERE table_name = 'EMP';

- 컬럼 주석 생성
COMMENT ON COLUMN 소유자명.테이블명.칼럼명 IS '내용';
SELECT * FROM user_col_comments WHERE table_name = 'EMP';

- 테이블 주석 삭제
COMMENT ON TABLE 소유자명.테이블명 IS '';
SELECT * FROM user_tab_comments WHERE table_name = 'EMP';

- 컬럼 주석 삭제
COMMENT ON COLUMN 소유자명.테이블명.칼럼명 IS '';
SELECT * FROM user_col_comments WHERE table_name = 'EMP';

 

---

 

■ VIEW
- 하나이상의 테이블이 있는 데이터를 논리적으로 처리하는 객체
- 단지 SELECT문을 가지고 있는 객체
- 간접 access를 하기 위해서
- VIEW 객체를 생성하려면 CRE ATE VIEW 시스템 권한이 있어야 한다.

SELECT * FROM session_privs;        -- CRE ATE VIEW 시스템 권한 확인

DROP TABLE hr.emp_2023 PURGE;

CREATE TABLE hr.emp_2023                -- 테이블 생성
AS
SELECT employee_id, last_name, first_name, job_id, manager_id, department_id
FROM hr.employees;

SELECT * FROM hr.emp_2023;

GRANT SELECT ON hr.emp_2023 TO insa;    -- 권한부여

-> 사원정보 업데이트 시 hr.employees, hr.emp_2023 둘다 업데이트 해야 함.
-> 유지관리의 불편함
-> 스토리지 낭비

↓

DROP TABLE hr.emp_2023 PURGE;

-- VIEW 생성 : SELECT문만 가지고 있다 >> 간접 access를 제공하기 위해서
CREATE VIEW hr.emp_2023
AS
SELECT employee_id, last_name, first_name, job_id, manager_id, department_id
FROM hr.employees;

SELECT * FROM hr.emp_2023;

GRANT SELECT ON hr.emp_2023 TO insa;


1. OBJECT_TYPE
SELECT * FROM user_objects WHERE object_name IN ('EMPLOYEES','EMP_2023');

2. VIEW 정보
SELECT * FROM user_views WHERE view_name = 'EMP_2023';

SELECT * FROM hr.emp_2023;

[문제] 부서이름별, 총액급여, 평균급여를 access 하는 dept_sum_sal 뷰를 생성한 후
    insa 유저한테 dept_sum_sal 뷰에 대한 SELECT 권한을 부여해주세요.

-- VIEW 새성
CREATE VIEW dept_sum_sal
AS
SELECT d.department_name, e.sumsal, e.avgsal
FROM (SELECT department_id, sum(salary) sumsal, round(avg(salary)) avgsal
        FROM hr.employees
        GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

SELECT * FROM dept_sum_sal;
SELECT * FROM useR_views WHERE view_name = 'DEPT_SUM_SAL';

-- 권한부여
GRANT SELECT ON hr.dept_sum_sal TO insa;

SELECT * FROM user_tab_privs;

<insa session>
SELECT * FROM user_tab_privs;
SELECT * FROM hr.dept_sum_sal;
SELECT * FROM all_objects WHERE owner = 'HR';
SELECT * FROM all_views WHERE owner = 'HR';
<->


-- 권한 회수
REVOKE ALL ON hr.employees FROM insa;
또는
REVOKE SELECT, INSERT, UPDATE, DELETE ON hr.departments FROM insa;

- VIEW 삭제
DROP VIEW hr.dept_sum_sal;


- VIEW 수정
- OR REPLACE : 동일한 이름의 뷰가 있으면 삭제하고 생성한다.

CREATE OR REPLACE VIEW dept_sum_sal
AS
SELECT d.department_name 부서이름, e.sumsal 총액급여, e.avgsal 평균급여        -- 칼럼 별칭
FROM (SELECT department_id, sum(salary) sumsal, round(avg(salary)) avgsal
        FROM hr.employees
        GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;
||
CREATE OR REPLACE VIEW dept_sum_sal(부서이름, 총액급여, 평균급여)        -- 칼럼별칭 여기에 입력 가능
AS
SELECT d.department_name, e.sumsal, e.avgsal
FROM (SELECT department_id, sum(salary) sumsal, round(avg(salary)) avgsal
        FROM hr.employees
        GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;



CREATE TABLE hr.emp_20
AS
SELECT employee_id id, last_name name, salary sal
FROM hr.employees
WHERE department_id= 20;

CREATE OR REPLACE VIEW hr.emp_20_view
AS
SELECT * FROM hr.emp_20;

desc hr.emp_20
desc hr.emp_20_view

SELECT * FROM hr.emp_20;
SELECT * FROM hr.emp_20_view;

INSERT INTO hr.emp_20_view(id,name,sal)VALUES(1,'홍길동',1000);
COMMIT;

UPDATE hr.emp_20_view SET name = '박찬호' WHERE id = 1;
COMMIT;

DELETE FROM hr.emp_20_view WHERE ID = 1;
COMMIT;

->> 간접 access : view를 통해 데이터 수정

GRANT SELECT, INSERT, UPDATE, DELETE ON hr.emp_20_view TO insa;


# 단순뷰
- 뷰를 통해서 DML작업을 수행할 수 있다.
- 뷰 안에 SELECT문에 사용한 테이블이 하나일 경우
- 뷰 안에 SELECT문에 함수를 사용하지 않은 경우

# 복합뷰
- 뷰를 통해서 DML작업을 수행할 수 없다.
- 뷰 안에 SELECT문에 사용한 테이블이 여러개일 경우
- 조인 문장이 있는 경우 
- 뷰 안에 SELECT문에 함수를 사용한 경우
- 복합뷰이지만 DML작업을 하려면 PL/SQL를 이용해서 트리거 프로그램을 작성하면 된다.


# VIEW에 CHECK 제약조건을 생성하는 방법
# WHERE절에 있는 조건식이 CHECK 제약조건의 조건식이 된다.

CREATE OR REPLACE VIEW hr.emp_20_view
AS
SELECT id, name, sal 
FROM hr.emp_20
WHERE sal BETWEEN 1000 AND 10000        -- CHECK 제약조건의 조건식이 된다
WITH CHECK OPTION CONSTRAINT emp_20_view_ck;        --  CHECK 제약조건

SELECT * FROM user_constraints WHERE table_name = 'EMP_20_VIEW';

INSERT INTO hr.emp_20_view(id,name,sal)VALUES(2,'나얼',100);
    -- 오류 : CHECK 제약조건 위반
INSERT INTO hr.emp_20_view(id,name,sal)VALUES(2,'나얼',2000);

UPDATE hr.emp_20_view SET sal = 100 WHERE id = 2;
    -- 오류 : CHECK 제약조건 위반
DELETE FROM hr.emp_20_view WHERE ID = 2;
    -- 수행 : CHECK 제약조건은 INSERT, UPDATE에서 수행된다.
SELECT * FROM hr.emp_20_view;



# 뷰를 통해서 읽기만 가능하도록 하는 방법

CREATE OR REPLACE VIEW hr.emp_20_view
AS
SELECT id, name, sal 
FROM hr.emp_20
WITH READ ONLY;

SELECT * FROM user_constraints WHERE table_name = 'EMP_20_VIEW';
>> CONSTRAINT_TYPE : O  == READ ONLY

INSERT INTO hr.emp_20_view(id,name,sal)VALUES(2,'나얼',100);
    -- 오류 : DML 불허, "cannot perform a DML operation on a read-only view"

 

---

[참고] insa 모든 권한 한번에 삭제 가능 ?
> 리터럴 문자로 문장을 만들어 사용한다.
SELECT 'REVOKE '||privilege||' ON '||grantor||'.'||table_name||' FROM '||grantee||';' 
FROM user_tab_privs 
WHERE grantee = 'INSA';

SELECT 'GRANT '||privilege||' ON '||grantor||'.'||table_name||' TO '||grantee||';' 
FROM user_tab_privs 
WHERE grantee = 'INSA';