제약조건(UNIQUE, CHECK, NOT NULL), DDL(RENAME, TRUNCATE, COMMENT), VIEW

안녕 난 푸름이야·2023년 3월 30일
sql빅데이터&머신러닝
0

빅데이터&머신러닝 - SQL

목록 보기
12/14

오늘은 어제에 이어서 UNIQUE, CHECK, NOT NULL 제약조건과 RENAME, TRUNCATE, COMMENT라는 DDL, 그리고 VIEW에 대해 배웠다.

제약조건

UNIQUE

: 유일한 값만 체크. PRIMARY KEY와 다르게 NULL을 허용한다. 그리고 PRIMARY KEY처럼 자동으로 UNIQUE INDEX를 생성한다.

기본적인 형태는 다음과 같다.

ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름 UNIQUE(컬럼이름);

예를 들어보자.

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

hr.dept라는 테이블에 dept_name_uk라는 이름의 제약조건을 추가할 건데, 그 제약조건은 UNIQUE 제약조건이고 dept_name라는 컬럼에 걸겠다는 뜻이다.

UNIQUE 제약조건을 삭제할 땐 PRIMARY KEY 제약조건과 비슷하지만 약간 다르다. 예를 들어보자.

PRIMARY KEY 제약조건을 삭제할 땐 

ALTER TABLE hr.emp DROP PRIMARY KEY;

라고 작성하였다. PRIMARY KEY 제약조건은 해당 테이블에서 단 하나이기 때문에 굳이 컬럼의 이름을 명시하지 않아도 컬럼의 삭제가 가능했다. 그러나 UNIQUE 제약조건은 다르다.

ALTER TABLE hr.dept DROP CONSTRAINT dept_name_uk;
또는
ALTER TABLE hr.dept DROP UNIQUE(dept_name);

위와 같이 제약조건의 이름을 꼭 써주어야만 삭제가 가능하니 명심하도록 하자.

CHECK

: 조건의 값이 TRUE인 경우 INSERT, UPDATE 할 수 있도록 만드는 제약조건이다. NULL과 중복값을 허용한다.

기본적인 형태는 다음과 같다.

ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름 CHECK(조건식);

예를 들어보자.

ALTER TABLE hr.emp ADD CONSTRAINT emp_sal_ck CHECK(sal >= 2000 AND sal <= 5000);

hr.emp라는 테이블에 emp_sal_ck라는 이름의 CHECK 제약조건을 추가할 건데, 그 조건은 연봉이 2000~5000이어야 한다는 뜻이다. 

SELECT * FROM user_constraints WHERE table_name = 'EMP';

을 수행하여 hr.emp 테이블의 제약조건을 조회해보자.

후술하겠지만 CHECK 제약조건과 NOT NULL 제약조건은 둘 다 제약조건 타입이 C라고 나온다. 그런데 NOT NULL 제약조건과 다르게 CHECK 제약조건은 search_condition에 내가 걸어준 조건식이 나온다.

만약 내가 테이블에 위처럼 CHECK 제약조건을 걸어줬는데 UPDATE를 사용하여 데이터의 값을 수정한다고 해보자.

UPDATE hr.emp
SET sal = 500
WHERE id = 1;

hr.emp 테이블에서 id가 1인 값의 연봉을 500으로 수정하라는 뜻이다. 그런데 이 SQL문은 오류가 나온다.

왜냐하면 내가 이미 CHECK 제약조건으로 연봉이 2000~5000인 값들만 INSERT(또는 UPDATE) 되도록 조건을 걸어주었기 때문에 CHECK 제약조건에 위반된다는 에러 메시지가 나타나는 것이다. 즉, CHECK 제약조건의 결과값이 FALSE이기 때문에 오류가 발생한다.

NOT NULL

: NULL 값을 허용할 수 없는 제약조건이다.

기본적인 형태는 다음과 같다.

ALTER TABLE 테이블이름 MODIFY 컬럼이름 CONSTRAINT 제약조건이름 NOT NULL;

NOT NULL 제약조건은 다른 제약조건들과 다르게 MODIFY를 이용하여 추가해야 한다. 예를 들어보자.

ALTER TABLE hr.dept MODIFY dept_name CONSTRAINT dept_name_notnull NOT NULL;

hr.dept라는 테이블의 dept_name 컬럼에 dept_name_notnull라는 이름의 제약조건을 추가할 건데, 그 제약조건은 NOT NULL 제약조건이라는 뜻이다.

위에서 CHECK 제약조건과 NOT NULL 제약조건은 둘 다 제약조건 타입이 C라고 나온다고 했다. 그런데 NOT NULL 제약조건 search_condition에 IS NOT NULL이 나온다.

NOT NULL 제약조건을 생성할 때 MODIFY를 사용했던 것처럼 삭제 또한 MODIFY를 사용한다. 예를 들어보자.

ALTER TABLE hr.dept DROP CONSTRAINT dept_name_notnull;
또는
ALTER TABLE hr.dept MODIFY dept_name NULL;

기존의 제약조건들을 삭제할 때처럼 DROP CONSTRAINT 컬럼이름 을 사용하든지 MODIFY를 이용하여 삭제할 것인지는 개인의 편의에 따른다.

열 레벨 정의와 테이블 레벨 정의

그렇다면 테이블 생성과 동시에 제약조건을 디자인하는 방법은 없을까? 바로 열 레벨 정의 혹은 테이블 레벨 정의를 이용하면 된다.

열 레벨 정의

: 테이블을 생성할 때, 컬럼을 생성하면서 바로 제약조건을 걸어주는 것. 단, NOT NULL 제약조건은 열 레벨 정의로만 가능하다.

예를 들어보자.

CREATE TABLE hr.emp(   
    id number CONSTRAINT emp_id_pk PRIMARY KEY,
    name varchar2(30) CONSTRAINT emp_name_nn NOT NULL,
    sal number CONSTRAINT emp_name_ck CHECK(sal BETWEEN 1000 AND 2000),
    dept_id number CONSTRAINT emp_dept_id_fk REFERENCES hr.dept(dept_id));

id, name, sal, dept_id라는 컬럼으로 hr.emp 테이블을 생성하는데, id는 숫자 타입인데 emp_id_pk라는 이름의 PRIMARY KEY 제약조건이, name은 최대 30자 크기의 문자 타입인데 emp_name_nn이라는 NOT NULL 제약조건이, sal은 숫자 타입인데 연봉이 1000~2000인 CHECK 제약조건이, dept_id는 숫자 타입인데 hr.dept 테이블의 dept_id 컬럼을 참조하는 emp_dept_id_fk라는 이름의 FOREIGN KEY 제약조건이 걸린다는 뜻이다.

여기서 FOREIGN KEY 제약조건의 문법을 주의하자. 일반적으로 FOREIGN KEY 제약조건의 형태는 아래와 같다.

ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름
FOREIGN KEY(컬럼이름) REFERENCES 참조테이블이름(컬럼이름)

그런데 열 레벨 정의 할 때의 FOREIGN KEY 제약조건은 'CONSTRAINT 제약조건이름 REFERENCES 참조테이블이름(컬럼이름)'으로 FOREIGN KEY(컬럼이름)이 빠진 형태로 나타낸다.

테이블 레벨 정의

: 테이블을 생성할 때, 컬럼을 먼저 정의하고 그 아래에 제약조건을 걸어주는 것.

예를 들어보자.

CREATE TABLE hr.emp(  
    id number,
    name varchar2(30) CONSTRAINT emp_name_notnull NOT NULL,	-- NOT NULL 제약조건은 열 레벨 정의
    sal number,
    dept_id number, 
    CONSTRAINT emp_id_pk PRIMARY KEY(id),
    CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000),
    CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES hr.dept(dept_id));

위의 테이블 레벨 정의 예제는 앞에서 언급한 열 레벨 정의 예제와 동일한 결과값을 출력한다. 테이블을 생성하고 컬럼의 이름과 타입을 먼저 나열한 뒤, 그 아래에 제약조건을 쓰는 형태이다. 그러나 테이블 레벨 정의를 할 때에도 반드시 NOT NULL 제약조건은 열 레벨 정의로 나타내는 것을 명심하자.

그리고 열 레벨 정의에서는 FOREIGN KEY 문법이 평소와 달랐지만, 테이블 레벨 정의에서는 평상시 FOREIGN KEY 제약조건 생성할 때의 문법과 동일하게 나타난다.

열 레벨 정의와 테이블 레벨 정의는 사용자의 편의에 따라 활용하면 된다. 단, NOT NULL 제약 조건은 반드시 열 레벨 정의 해야하고, FOREIGN KEY 제약조건은 열 레벨 정의 할 때와 테이블 레벨 정의할 때의 문법이 다른 점을 유의하도록 한다.

DDL

RENAME

: 테이블, 컬럼, 제약조건, 인덱스 등의 이름을 수정할 때 쓰이는 SQL문.

테이블 이름 수정

기본적인 형태는 다음과 같다.

RENAME 기존이름 TO 새로운이름;
또는
ALTER TABLE 기존이름 RENAME TO 새로운이름;

예를 들어보자.

RENAME hr.emp TO hr.emp_new;

hr 유저의 emp 테이블의 이름을 emp_new로 변경하라는 뜻이다. 그런데 사실 위 SQL문은 오류가 발생한다. 왜냐하면 테이블의 이름을 변경할 때는 hr.과 같은 소유자 이름을 쓰지 않기 때문이다. 이 말인 즉슨, 테이블 이름 수정은 유저 본인만 할 수 있다.

위 예제의 올바른 사용은 아래와 같다.

RENAME emp TO emp_new;

컬럼 이름 수정

기본적인 형태는 다음과 같다.

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

예를 들어보자.

ALTER TABLE hr.emp RENAME COLUMN id TO emp_id;

hr.emp 테이블의 id라는 컬럼 이름을 emp_id로 변경하라는 뜻이다. 컬럼 이름을 변경해도 제약조건이나 인덱스에는 아무런 영향을 미치지 않는다.

제약조건 이름 수정

기본적인 형태는 다음과 같다.

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

예를 들어보자.

ALTER TABLE hr.emp RENAME CONSTRAINT emp_id_pk TO emp_empid_pk;

hr.emp 테이블의 emp_id_pk라는 제약조건 이름을 emp_empid_pk로 변경하라는 뜻이다. 위 예제 같은 경우 PRIMARY KEY 제약조건의 이름을 변경한 것인데, PRIMARY KEY 제약조건은 생성 시 제약조건 이름을 기준으로 자동으로 UNIQUE INDEX를 생성한다고 했다. 따라서 제약조건 이름을 변경해도 제약조건 생성 시에 만들어진 UNIQUE INDEX의 이름을 그대로 따른다. 그러므로 인덱스의 이름도 변경하고 싶다면 아래와 같은 방법을 취해야 한다.

인덱스 이름 수정

기본적인 형태는 다음과 같다.

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

예를 들어보자.

ALTER INDEX emp_id_pk RENAME TO emp_id_ind;

emp_id_pk라는 인덱스 이름을 emp_id_ind로 변경하라는 뜻이다.

TRUNCATE

: 테이블의 행을 전부 삭제하는 SQL문. DELETE문과 비슷하지만 TRUNCATE문은 저장공간을 초기화 상태로 만든다. TRUNCATE는 ROLLBACK을 할 수 없지만 리소스를 줄인다는 장점이 있다.

기본적인 형태는 다음과 같다.

TRUNCATE TABLE 테이블이름;

똑같이 삭제의 기능을 담당하지만 DELETE와 TRUNCATE의 가장 큰 차이점은 TRUNCATE는 ROLLBACK을 할 수 없다는 점이다. 왜냐하면 DELETE로 행을 삭제했을 때, (지난번에도 언급했듯이) UNDO TABLESPACE에 삭제한 값이 저장이 된다. 즉, DELETE는 ROLLBACK이나 읽기 일관성을 위해 이전값을 저장하는데 만약 삭제해야하는 데이터가 대용량일 경우 문제가 발생한다.

이를 테면 UNDO TABLESPACE의 여유공간이 10GB이고 삭제해야하는 데이터가 20GB라고 가정하자. 만약 DELETE로 삭제를 해버리면 삭제된 데이터가 저장되는 UNDO TABLESPACE는 용량이 넘쳐서 장애가 발생하게 된다. 따라서 이런 경우엔 DELETE보단 UNDO TABLESPACE에 삭제한 데이터를 저장하지 않는 TRUNCATE를 사용하는 것이 리소스를 줄이므로 더 적절하다. 하지만 TRUNCATE문은 테이블을 초기화 상태로 만들기 때문에 ROLLBACK을 할 수 없어서 신중하게 사용해야 한다.

COMMENT

: 테이블과 컬럼의 주석(설명)을 만드는 SQL문.

기본적인 형태는 다음과 같다.

COMMENT ON TABLE 테이블이름 IS '테이블설명'			-- 테이블 주석 생성
COMMENT ON TABLE 테이블이름 IS ''						-- 테이블 주석 삭제
COMMENT ON COLUMN 소유자.테이블.컬럼이름 IS '컬럼설명'	-- 컬럼 주석 생성(소유자는 생략 가능)
COMMENT ON COLUMN 소유자.테이블.컬럼이름 IS ''			-- 컬럼 주석 삭제

현장에서 모르는 데이터에 대한 정보를 알기 위해 항상 우선적으로 확인해야 할 것이 바로 주석이다. 주석에 대한 정보를 확인하는 방법은 다음과 같다.

SELECT * FROM user_tab_comments WHERE table_name = '테이블이름'	-- 테이블 주석 확인
SELECT * FROM user_col_comments WHERE table_name = '테이블이름'	-- 컬럼 주석 확인

FLASHBACK

VIEW

: 하나 이상의 테이블이 있는 데이터를 논리적으로 처리하는 객체. 단지 SELECT문을 가지고 있는 객체이다. 테이블에 직접 액세스 하지 않고 VIEW를 통해 바라보는 간접 액세스를 하기 위해 VIEW를 사용한다.

이러한 VIEW를 생성하려면 CREATE VIEW 라는 시스템권한이 있어야 한다. 그리고 VIEW 안에 컬럼 이름으로 함수나 표현식이 쓰일 수 없기 때문에 반드시 별칭을 사용하는 습관을 들여야 한다.

VIEW를 통해서 DML 작업(INSERT, UPDATE, DELETE)도 가능하다. 대신 COMMIT이나 ROLLBACK으로 DML 작업 시 발생하는 트랜잭션을 반드시 완료해주어야만 VIEW에 적용이 된다.

VIEW 생성

기본적인 형태는 다음과 같다.

CREATE VIEW 뷰이름 
AS
SELECT 컬럼, 컬럼, 컬럼, 컬럼...
FROM 소유자.테이블;

VIEW를 생성한 뒤 조회해보면 VIEW 내부의 SELECT 문이 돌아가기 때문에 하나의 테이블을 조회한 것 같은 효과가 있다. 이것을 바로 간접 액세스 라고 한다.

예를 들어보자.

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

hr.emp_details_view라는 VIEW를 생성하는데, 이 VIEW는 hr.employees 테이블과 hr.departments 테이블을 department_id로 조인하고 department_id별 총액급여와 평균급여를 department_name 별로 조회하라는 뜻이다. 위 예제는 조인의 일 양을 줄이기 위해 VIEW가 가진 SELECT문의 FROM절에 INLINE VIEW를 추가한, 즉 VIEW와 INLINE VIEW가 합쳐진 형태라고 보면 된다. VIEW의 SELECT 문에는 컬럼의 별칭이 사용되었다.

만약 이 hr.emp_details_view가 VIEW인지 모른다고 가정했을 때, 이게 테이블인지 VIEW인지는 어떻게 확인할 수 있을까?

SELECT * FROM user_objects WHERE object_name = 'EMP_DETAILS_VIEW';

위 SELECT 문을 수행하면 hr.emp_details_view의 객체 타입이 무엇인지 알려준다. 여기서는 우리가 VIEW로 생성해주었기 때문에 VIEW라고 나온다. 만약 'EMP_DETAILS_VIEW'에 테이블 이름을 넣으면 객체 타입은 TABLE로 나타난다.

VIEW 삭제 및 수정

DROP

: VIEW를 삭제한다.

기본적인 형태는 다음과 같다.

DROP VIEW 뷰이름;

만약, VIEW의 내용을 수정한다고 하면 꼭 VIEW를 DROP하고 다시 생성해야 할까? 그럴 때 사용하는 것이 바로 OR REPLACE이다.

OR REPLACE

: 동일한 이름의 VIEW가 있으면 삭제하고 생성한다. 일반적으로 VIEW를 수정해야할 일이 많기 때문에 그냥 VIEW를 생성하기 보다 CREATE OR REPLACE VIEW를 많이 사용한다.

기본적인 형태는 다음과 같다.

CREATE OR REPLACE VIEW 뷰이름 
AS
SELECT 컬럼, 컬럼, 컬럼, 컬럼...
FROM 소유자.테이블;

단순 VIEW와 복합 VIEW

단순 VIEW

  • VIEW 안의 SELECT 문에 테이블 하나만 있는 경우
  • VIEW 안의 SELECT 문에 함수를 사용하지 않은 경우

VIEW를 통해서 DML 작업을 수행할 수 있다.

예를 들어보자.

CREATE OR REPLACE VIEW test_view
AS
SELECT * FROM hr.test

test_view라는 VIEW의 SELECT문에 hr.test라는 테이블 하나만 있는 경우에 속한다.

복합 VIEW

  • VIEW 안의 SELECT 문에 여러 개의 테이블을 사용하는 경우(조인구문 있음)
  • VIEW 안의 SELECT 문에 함수를 사용하고 있는 경우

VIEW를 통해서 DML 작업을 수행할 수 없다. 복합 VIEW에서 DML 작업을 수행하려면 PL/SQL을 이용해서 트리거 프로그램을 이용하면 해결할 수 있다.

예를 들어보자.

CREATE OR REPLACE VIEW hr.emp_details_view
AS
SELECT d.department_name 부서이름, sumsal 총액급여, avgsal 평균급여
FROM (SELECT department_id, sum(salary) sumsal, avg(salary) avgsal
        FROM hr.employees
        GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

hr.emp_details_view라는 VIEW의 SELECT문에 조인구문을 포함한 여러 개의 테이블을 사용하는 경우에 속한다.

profile
안녕 난 푸름이야
푸름이의 우당탕탕 코딩생활
이전 포스트

다중테이블 INSERT(INSERT ALL, 조건 INSERT ALL, INSERT FIRST), MERGE, 컬럼 추가·수정·삭제(ADD, MODIFY, DROP), 제약조건(PRIMARY KEY, FOREIGN KEY)

다음 포스트

WITH CHECK OPTION CONSTRAINT, WITH READ ONLY, SEQUENCE, SYNONYM, DATA ACCESS METHOD(Full table scan, Rowid scan), INDEX(Nonunique, Unique, Range scan, Unique scan, 조합 INDEX)

0개의 댓글