[6/19] 제약조건과 VIEW

[참고]

--테이블 목록확인
--딕셔너리를 이용
SELECT TABLE_NAME FROM TABS;
--TAB이라는 뷰를 이용
SELECT * FROM TAB;

 

📌[PK제약조건]

• 중복된 컬럼값 저장을 방지하기 위한 제약조건
• 테이블에서 하나의 컬럼에만 PRIMARY KEY를 설정할 수 있으며 NULL미허용
• 행을 대표하는 고유값을 저장하기 위한 컬럼에 PRIMARY KEY 제약조건 설정

1) 컬럼에 제약조건 주기

CREATE TABLE MAG1(NO NUMBER(4) CONSTRAINT MGR1_NO_PK PRIMARY KEY,
NAME VARCHAR2(20), STARTDATE DATE);

2) 테이블에 제약조건 주기

CREATE TABLE MAG2(NO NUMBER(4),NAME VARCHAR2(20), STARTDATE DATE,
CONSTRAINT MAG2_NO_PK PRIMARY KEY(NO,NAME));

 

📌[FK제약조건] ->참조해야 하기 때문에 속도에 영향을 줄 수 있다.

• 부모 테이블에 저장된 행의 컬럼값을 참조하여 잘못될 값의 행이 저장되는 것을 방지
• 부모테이블에는 PRIMARY KEY 제약조건이 설정된 컬럼의 참조하여 자식테이블의 컬럼에 FK제약조건설정이 가능하다.
• FK제약조건이 부여된 컬럼은 부모테이블의 PK제약조건이 설정된 컬럼이 참조되도록 선언하면된다.
• FK제약조건은 테이블 수준의 제약조건으로만 설정 가능
• ==> 조인했을때 잘못된 결과가 도출되지 않는다.

--FK제약조건을 주어 테이블만들기
CREATE TABLE TRAINEE2 (TNO NUMBER(4) CONSTRAINT TRAINEE2_TNO_PK PRIMARY KEY, 
                    TNAME VARCHAR2(20), SCODE NUMBER(2), 
                    CONSTRAINT TRAINEE2_SCODE_FK FOREIGN KEY(SCODE) 
                    REFERENCES SUBJECT1(SNO)--어떤 테이블을 참조하는지 적어준다.
                    );
                    
--제약조건 확인
DESC TRAINEE2;
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME ='TRAINEE2';
 
--행삽입
INSERT INTO TRAINEE2 VALUES(1000,'홍길동',10);
INSERT INTO TRAINEE2 VALUES(2000,'임꺽정',20);
INSERT INTO TRAINEE2 VALUES(3000,'전우치',30);
--부모 테이블에참조되는 컬럼에 존재하지 않는 값을 전달할 경우 FK제약조건을 위반하여 에러발생
INSERT INTO TRAINEE2 VALUES(4000,'장길산',40);--ERROR
 
--TRAINEE2와 SUBJECT1테이블을 조인하여 결과보기
--40코드를 가진 장길산은 보이지 않는다.
SELECT TNO, TNAME, SNAME FROM TRAINEE2 JOIN SUBJECT1 ON SCODE = SNO;
 
--FK제약조건을 위반했으므로 ERROR
UPDATE TRAINEE2 SET SCODE = 40 WHERE TNO = 1000;
 
--자식테이블의 행의 컬럼값이 참조하는 부모테이블의 참조컬럼의 컬럼값이 저장된 행 삭제불가능
SELECT DISTINCT SCODE FROM TRAINEE2; -- 참조되는 과목코드는 10,20,30 이란것을 알 수 있음
DELETE FROM SUBJECT1 WHERE SNO = 10;--ERROR
 

FK제약조건 위반

• FK제약조건 설정시 ON DELETE CASCADE또는 ON DELETE SET NULL 기능을 추가 할 수 있다.

--TRAINEE3테이블 생성
CREATE TABLE TRAINEE3 (TNO NUMBER(4) CONSTRAINT TRAINEE3_TNO_PK PRIMARY KEY, 
                    TNAME VARCHAR2(20), 
                    SCODE NUMBER(2), 
                    CONSTRAINT TRAINEE3_SCODE_FK FOREIGN KEY(SCODE) 
                    REFERENCES SUBJECT2(SNO) ON DELETE CASCADE);
                    
--FK제약조건 설정시 ON DELETE CASCADE또는 ON DELETE SET NULL기능을 추가 할 수 있다.

--행삽입
INSERT INTO TRAINEE3 VALUES(1000,'홍길동',10);
INSERT INTO TRAINEE3 VALUES(2000,'임꺽정',20);
INSERT INTO TRAINEE3 VALUES(3000,'전우치',30);
SELECT * FROM TRAINEE3;
COMMIT;

--SUBJECT2 테이블에서 과목코드가 10인 과목정보를 삭제
DELETE FROM SUBJECT2 WHERE SNO = 10;--제거가능
SELECT * FROM SUBJECT2;--10번 삭제됨
SELECT * FROM TRAINEE3;--얘도 삭제됨

1) ON DELETE CASCADE : 부모테이블의 행을 삭제 할 경우, 자식테이블의 참조 컬럼값이 저장된 행도 같이 삭제된다.
2) ON DELETE SET NULL : 부모테이블의 행을 삭제할 경우 자식 테이블의 참조 컬럼값을 NULL로 변경(단, 테이블 조건                                      이 NOT NULL이면 ERROR발생)

 

📌[서브쿼리로 테이블 생성하기] 

• 서브쿼리를 이용하여 테이블 생성 가능 : 기존테이블을 이용하여 새로운 테이블 생성가능(기존테이블의 행을 복사해서 넣을 수 있다.)
• 형식 ) CREATE TABLE 테이블명 [(컬럼명, 컬럼명,....)] AS SELECT 검색대상, ... FROM 테이블명 WHERE 조건식;
• 서브쿼리(일종의 임시테이블이라고 보면 생각하기 쉽다.)의 검색결과와 동일한 구조의 테이블을 생성하며 검색행을 생성된 테이블에 삽입한다. 
• 생성테이블에 컬럼명이 생략된 경우 서브쿼리의 검색대상을 컬럼명으로 사용 
• 기존테이블과같은 구조를 갖지만, 생성테이블에는 제약조건을 설정하지 않는다.

--EMP테이블의 모든 사원정보를 검색하여 EMP2테이블을 생성하고 검색행을 복사
CREATE TABLE EMP2 AS SELECT * FROM EMP;
SELECT * FROM EMP2;

--제약조건 비교
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP';
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP2';

--EMP3생성 (컬럼지정)
CREATE TABLE EMP3 AS SELECT EMPNO, ENAME, SAL FROM EMP;

--EMP3의 테이블의 구조와 행 확인
DESC EMP3;
SELECT * FROM EMP3;

• 구조(속성)은 동일하다
• 하지만, 기존 테이블을 이용하여 생성된 테이블에는 설정된 제약조건은 존재하지 않는다.
• 테이블의 저장행도 똑같이 복사된다.

(위) EMP (아래) EMP2

EMP3 결과

• 기존의 테이블의 검색행은 그대로 복사되지만, 컬럼이름을 바꾸고 싶은 경우 테이블명(바꿀 컬럼명, ....)으로 해주면 된다.

--EMP테이블에서 급여가 2000이상인 사원의 사원번호, 사원이름, 급여를 검색하여 EMP4테이블을 생성
CREATE TABLE EMP4 AS SELECT  EMPNO, ENAME, SAL FROM EMP WHERE SAL >= 2000;
SELECT * FROM EMP4;

----EMP테이블에서 급여가 2000이상인 사원의 사원번호, 사원이름, 급여를
--검색하여 EMP5테이블을 생성하고, 검색행 사원번호 사원이름, 급여에 저장되도록 복사
--컬럼이름이 바뀌어 저장된다.
CREATE TABLE EMP5(NO, NAME, PAY) AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL >= 2000;
SELECT * FROM EMP5;

 

• 조건을 무조건적인 거짓으로 주면, 검색행은 미복사하면서 기존의 테이블과 동일한 구조의 테이블을 생성한다.

--EMP 테이블과 동일한 구조의 EMP6 테이블 생성 : 검색행 미복사
CREATE TABLE EMP6 AS SELECT * FROM EMP WHERE 1 = 0;

 

 

📌[DROP]

• 형식 ) DROP TABLE 테이블 명
• DELETE명령과는 다르게 AUTO COMMIT이 되니까 주의!!
• 단, 오라클에서는 테이블을 삭제할 경우, 테이블과 테이블 관련 객체를 휴지통에 이동하므로 복구 가능하긴함 ㅠ
• RECYCLE.BIN에 가면됨!!🤗

--테이블 목록확인
--딕셔너리를 이용
SELECT TABLE_NAME FROM TABS;
--TAB이라는 뷰를 이용
SELECT * FROM TAB; --TNAME컬럼에 BIN으로 시작되는 테이블은 오라클 휴지통에 존재하는 삭제 테이블
--오라클 휴지통의 객체목록을 확인하는 SHOW RECYCLEBIN;하면됨
SHOW RECYCLEBIN;

• 오라클 휴지통에 존재하는 테이블 복구 - 테이블 관련 객체도 같이 복구가 된다.
• 단, 종속된 것은 X , 테이블만 복구 O
• 형식 ) FLASHBACK TABLE 테이블명 TO BEFORE DROP;
• 테이블에 종속된 객체도 삭제된다.
• 복구될때도, 테이블에 종속된 객체도 같이 복구된다.

USER2테이블에 종속되어있던 인덱스 객체도 삭제되었다.

 

--오라클 휴지통에 존재하는 테이블 제거 - 종속된 INDEX객체도 같이 제거된다
--형식) PURGE TABLE 테이블명
PURGE TABLE USER4; -- 휴지통에 있는 USER4를 삭제해라
SHOW RECYCLEBIN;

--오라클 휴지통에 존재하는 모든 객체를 제거
PURGE RECYCLEBIN;

--MAG1테이블 삭제-- 오라클 휴지통에 거쳐서 삭제
DROP TABLE MAG1;
PURGE TABLE MAG1;

--오라클 휴지통으로 이동하지 않고 직접삭제하기
DROP TABLE MAG2 PURGE;
SHOW RECYCLEBIN;

 

 

📌[테이블 초기화]

• 테이블 생성 직후의 상태로 초기화하기 위한 명령 : 테이블에 저장된 모든행 삭제
• 형식 ) TRUNCATE TABLE 테이블명;

--BONUS테이블에 저장된 모든 행 삭제
--트랜잭션을 이용한 삭제처리만 되어있다. : 롤백처리가 가능하다.
DELETE FROM BONUS;
SELECT * FROM BONUS;

--BONUS테이블 초기화 작업 : 자동커밋이 되므로 롤백처리가 불가능하다
TRUNCATE TABLE BONUS;
SELECT * FROM BONUS;

 

📌[RENAME TABLE]

• 테이블 이름 변경
• 형식) RENAME 기본테이블 명 TO 변경테이블명

 

📌[ALTER TABLE]

• 테이블 구조 변경
• 형식 ) ALTER TABLE 테이블명 변경옵션
• 컬럼추가, 컬럼제거, 컬럼의 자료형 또는 크기 변경, 컬럼 기본값 변경, 제약조건 추가, 제약조건 삭제 등 가능
• 형식) ALTER TABLE 테이블명 AND (컬럼명 자료형 [(크기)] DEFAULT 기본값 제약조건);
• 단, 자료형을 변경할 컬럼에 값이 저장되어 있는 경우 자료형을 변경하면 에러발생

1) 테이블 칼럼 저장

• 기존행이 있는 경우 추가된 컬럼에는 기본값 자동저장

ALTER TABLE USER1 ADD(ADDRESS VARCHAR2(100));
UPDATE USER1 SET ADDRESS = '서울시 강남구' WHERE NO = 1000;

 

2) 컬럼자료형 또는 크기 변경

• 형식 ) ALTER TABLE 테이블명 MODIFY (컬럼명 자료형 [(크기)] [DEFAULT 기본값] [제약조건]);
• 크기를 변경할 값이 저장되어 있어도  크기변경은 가능하다
• 단, 변경크기가 컬럼값의 크기보다 큰 경우만 가능하다.

--자료형 변경
ALTER TABLE USER1 MODIFY(NO VARCHAR2(4));
INSERT INTO USER1 VALUES('1000','홍길동','010-1234-5678','서울시 강남구');

--크기변경
ALTER TABLE USER1 MODIFY(NAME VARCHAR2(10));
ALTER TABLE USER1 MODIFY(NAME VARCHAR2(5)); -- ERROR발생(컬람값의 크기보다 작기때문)

 

3) 컬럼명 변경

• 형식) ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 변경컬럼명

ALTER TABLE USER1 RENAME COLUMN ADDRESS TO ADDR;

 

4) 컬럼삭제

• 형식) ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

ALTER TABLE USER1 DROP COLUMN PHONE;

 

5) 컬럼에 제약조건 설정

• 컬럼수준의 제약조건은 MODIFY변경옵션을 이용하면됨
• 테이블수준의 제약조건은 

ALTER TABLE USER1 MODIFY(NAME VARCHAR2(10) CONSTRAINT USER1_NAME_NN NOT NULL);

 

6) 제약조건 설정 - 테이블 수준의 제약조건은 ADD변경옵션 이용

• 형식 ) ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명);

ALTER TABLE USER1 ADD CONSTRAINT USER_NO_PK PRIMARY KEY(NO);

 

7) 제약조건 제거

• 형식 ) ALTER TABLE 테이블명 DROP(PRIMARY KEY [CONSTRAINT 제약조건명])

ALTER TABLE USER1 DROP CONSTRAINT USER1_NAME_NN;

 

8) 제약조건 비활성화 :

• 컬럼에 설정된 제약조건을 일시중지하는 기능
• 형식) ALTER TABLE 테이블명 DISABLE {PRIMARY KEY|CONSTRAINT 제약조건명}

ALTER TABLE SAWON DISABLE CONSTRAINT SAWON_BNO_FK; 
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
    FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON';

 

9) 제약조건 활성화 

• 활성화 처리될 제약조건을 위반하는 행이 저장되어 있을 경우, 활성화 처리에 대한 에러가 발생
• 제약조건을 위반하는 행을 삭제해야함
• BUSEO 테이블의 BNO 컬럼에 설정된 PRIMARY KEY 제약조건 비활성화 처리를 하려고 할때 FOREIGN KEY 제약조건에 의해 참조되는 컬럼에 설정된 PRIMARY KEY 제약조건은 비활성화 처리하면 에러 발생 

ALTER TABLE SAWON ENABLE CONSTRAINT SAWON_BNO_FK; 

• FK제약조건에 의해 참조되는 부모테이블의 PRIMARY KEY제약조건을 제거하면 에러
• 단, CASCADE 키워드를 사용하여 자식 테이블에 설정된 FOREIGN KEY 제약조건도 같이 비활성화 처리 가능

 

📌[뷰 VIEW]

형식) CREATE [OR REPLACE] [(FORCE | NOFORCE)] VIEW 뷰이름[(컬럼명, 컬럼명, ....)]
        AS SELECT 검색대상, 검색대상.... FROM 테이블명 WHERE 조건식
        [WITH CHECK OPTION] [WITH READ ONLY]

• 테이블을 기반으로 만들어지는 가상의 테이블
• 뷰를 이용하여 보다 쉬운 검색기능을 제공하거나 보안(권한) 기능을 제공함
• CREATE OR REPLACE : 기존 뷰가 존재할 경우 제거 후 생성 즉, 기존뷰가 존재할 경우 새로운 뷰로 생성해야한다.
• FORCE : 서브쿼리의 검색결과가 없어도 강제로 뷰를 생성하는 기능을 제공
• WITH CHECK OPTION : 해당 뷰를 생성한 조건식의 컬럼값을 변경하지 못하도록 설정하는 기능(뷰가 가지고 있는 행을  변경하지 못하도록)
• WITH READ ONLY : 검색기능만 사용하도록 설정하는 기능을 제공(단순뷰만, 복합뷰는 기본이 READ ONLY임)
• 관리자(SYS OR SYSTEM)로 접속하여 일반사용자에게 뷰 관련 시스템권한을 부여해야한다 
• SHELL>SQLPLUS /NOLOG >> SQL프로그램 실행 
• SQL> CONN SYS/SYS AS SYSDB >> 관리자로 접속 
• SQL> GRANT CREATE VIEW TO SCOTT; >> SCOTT사용자에게 VIEW관련 시스템권한을 부여했다.

1) 단순뷰

• 하나의 테이블을 기반으로 만들어진 뷰
• 삽입, 삭제, 검색 기능을 제공한다.
• DISTINCT 키워드/ 그룹함수 사용 X

--권한을 부여하여 뷰 생성
 --행삽입, 삭제 변경 가능
CREATE VIEW EMP_VIEW30 AS SELECT EMPNO, ENAME, DEPTNO FROM EMP_COPY WHERE DEPTNO = 30;

--뷰목록확인 : USER_VIEWS : 뷰정보를 제공하는 딕셔너리
SELECT VIEW_NAME, TEXT FROM USER_VIEWS;

--뷰를 이용하여 테이블에 저장된 행을 검색
SELECT * FROM EMP_VIEW30;

--단순 뷰를 이용하여 테이블에 행 삽입
--뷰에없는 컬럼에는 기본값이 부여된다.
INSERT INTO EMP_VIEW30 VALUES(1111, '홍길동', 30);

 

2) 복합뷰

• 여러개의 테이블을 기반으로 만들어진 뷰 - 검색만 가능(조인으로 생성된 뷰)
• DISTINCT 키워드/ 그룹함수 사용 가능
• SELECT문을 사용해야한다.

--두개의 뷰를 사용하여 조인한 결과로 뷰를 만든다.
CREATE VIEW EMP_VIEW AS SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
--잘 만들어졌는지 확인
SELECT * FROM EMP_VIEW;

 

--사원테이블 생성
--SAWON 테이블 생성 - 사원번호(숫자형-PRIMARY KEY),사원이름(문자형),부서번호(숫자형-FOREIGN KEY:BUSEO 테이블의 BNO 컬럼 참조)
CREATE TABLE SAWON(SNO NUMBER(4) CONSTRAINT SAWON_SNO_PK PRIMARY KEY,SNAME VARCHAR2(20)
    ,BNO NUMBER(2),CONSTRAINT SAWON_BNO_FK FOREIGN KEY(BNO) REFERENCES BUSEO(BNO));
    
--제약조건 확인
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME 
    FROM USER_CONSTRAINTS WHERE TABLE_NAME IN('BUSEO','SAWON');
    
--SAWON 테이블에 행 삽입
INSERT INTO SAWON VALUES(1000,'홍길동',10);
INSERT INTO SAWON VALUES(2000,'임꺽정',20);
SELECT * FROM SAWON;
COMMIT;

--FOREIGN KEY 제약조건에 의해 에러 발생
INSERT INTO SAWON VALUES(3000,'전우치',30);

--제약조건 비활성화 : 컬럼에 설정된 제약조건을 일시 중지하는 기능
--형식)ALTER TABLE 테이블명 DISABLE {PRIMARY KEY|CONSTRAINT 제약조건명}
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
    FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON';
    
--SAWON 테이블에 BNO 컬럼에 설정된 FOREIGN KEY 제약조건에 대한 비활성화 처리
ALTER TABLE SAWON DISABLE CONSTRAINT SAWON_BNO_FK; 
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
    FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON';

--SAWON 테이블에 행 삽입 - FOREIGN KEY 제약조건이 비활성화 처리되어 미동작
INSERT INTO SAWON VALUES(3000,'전우치',30);
SELECT * FROM SAWON;

--제약조건 활성화 : 비활성화 처리된 제약조건을 활성화 하는 기능
--형식)ALTER TABLE 테이블명 ENABLE {PRIMARY KEY|CONSTRAINT 제약조건명}
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
    FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON';

--SAWON 테이블에 BNO 컬럼에 설정된 FOREIGN KEY 제약조건에 대한 활성화 처리
--활성화 처리될 제약조건을 위반하는 행이 저장되어 있을 경우 활성화 처리에 대한 에러 발생
ALTER TABLE SAWON ENABLE CONSTRAINT SAWON_BNO_FK; 
--제약조건을 위반하는 행을 삭제해야만 활성화 처리 가능
DELETE FROM SAWON WHERE SNO=3000;
ALTER TABLE SAWON ENABLE CONSTRAINT SAWON_BNO_FK; 
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
    FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON';

--BUSEO 테이블의 BNO 컬럼에 설정된 PRIMARY KEY 제약조건 비활성화 처리
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
    FROM USER_CONSTRAINTS WHERE TABLE_NAME='BUSEO';
--FOREIGN KEY 제약조건에 의해 참조되는 컬럼에 설정된 PRIMARY KEY 제약조건은 비활성화 처리하면 에러 발생    
ALTER TABLE BUSEO DISABLE PRIMARY KEY;    
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
    FROM USER_CONSTRAINTS WHERE TABLE_NAME IN('BUSEO','SAWON');

--CASCADE 키워드를 사용하여 자식 테이블에 설정된 FOREIGN KEY 제약조건도 같이 비활성화 처리 가능
ALTER TABLE BUSEO DISABLE PRIMARY KEY CASCADE;    
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
    FROM USER_CONSTRAINTS WHERE TABLE_NAME IN('BUSEO','SAWON');

--부모 테이블에 설정된 PRIMARY KEY 제약조건을 활성화 처리 후 자식 테이블에 설정된 FOREIGN KEY 제약조건을 활성화 처리
ALTER TABLE BUSEO ENABLE PRIMARY KEY;    
ALTER TABLE SAWON ENABLE CONSTRAINT SAWON_BNO_FK;    
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
    FROM USER_CONSTRAINTS WHERE TABLE_NAME IN('BUSEO','SAWON');

--FOREIGN KEY 제약조건에 의해 참조되는 부모 테이블의 PRIMARY KEY 제약조건을 제거하면 에러 발생
ALTER TABLE BUSEO DROP PRIMARY KEY;
--CASCADE 키워드를 사용하면 자식 테이블의 컬럼에 설정된 FOREIGN KEY 제약조건도 같이 제거 가능
ALTER TABLE BUSEO DROP PRIMARY KEY CASCADE;
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
    FROM USER_CONSTRAINTS WHERE TABLE_NAME IN('BUSEO','SAWON');