22장 고급 데이터 조작 옵션

제약 조건 이해하기

SQL은 여러 업그레이드를 거쳐 강력한 언어가 되었고 제약 조건과 같은 데이터 조작 기술을 제공한다.

관계형 데이터베이스는 데이터를 여러 테이블에 나누어 저장하고 각 테이블은 관련 데이터를 가진다. 한 테이블과 다른 테이블을 참조할 때는 키를 사용한다.

관계형 데이터베이스 설계가 제대로 작동하려면, 유효한 데이터만 테이블에 삽입한다는 보장이 필요하다.

새로운 행을 삽입하기 전 다른 테이블과 관련된 값이 존재하는지, 유효한지 확인할 수 있지만 이는 비효율적이다.

• 클라이언트 레벨에서 확인한다면, 일부 클라이언트에서 작동하지 않을 수 있다.

• UPDATE와 DELETE 작업에서도 확인해야 한다.

• 클라이언트에서 무결성을 확인한다면 시간과 자원이 많이 소모된다. DBMS에서 확인하는 것이 훨씬 효율적이다.

DBMS는 데이터베이스 테이블에 제약 조건을 정의해 참조 무결성을 보장한다. 대부분의 제약 조건은 테이블 정의에 명시한다.

기본 키

기본 키 Primary Key는 특별한 제약 조건으로서 열에 있는 값이 고유하면서 절대 변하지 않는다는 것을 보장하기 위해 사용한다. 테이블의 열로 테이블에 있는 행을 고유하게 구별할 수 있는 값을 가진다. 그리고 특정 행을 쉽게 조작할 수 있게 해준다. 기본 키가 없다면 특정 행만 안전하게 업데이트하거나 삭제하는 것이 매우 힘들다.

다음 조건을 만족한다면 테이블에 있는 열은 어떤 열이든 기본 키로 설정할 수 있다.

• 두 개 이상의 행이 같은 기본 키 값을 가질 수 없다.

• 모든 행은 기본 키 값을 반드시 가져야 한다. 기본 키 값은 NULL 값을 허용하면 안된다.

• 기본 키 값을 가진 열은 변경하거나 업데이트할 수 없다.

• 기본 키 값은 절대 다시 사용해서는 안 된다. 테이블에서 행이 삭제되더라도 그 값을 다른 행에 다시 할당해서는 안된다.

기본 키 정의 방법

CREATE TABLE Vendors (
    vend_id    CHAR(10)    NOT NULL PRIMARY KEY, -- 기본키 설정
    vend_name    CHAR(10)    NOT NULL,
    ...
)

ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY(vend_id);

외래 키

외래 키 Foreign Key는 테이블에 있는 열이면서 그 값이 다른 테이블의 기본 키 값 중 꼭 존재해야 하는 열이다. 외래 키는 참조 무결성을 보장하는 데 대단히 중요한 역할을 한다.

외래 키 정의 방법

CREATE TABLE Orders(
    order_num    INTEGER    NOT NULL PRIMARY KEY,
    order_date    DATETIME    NOT NULL,
    cust_id    CHAR(10)    NOT NULL REFERENCES Customers(cust_id)
)

ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY(cust_id) REFERENCES Customers(cust_id); -- Customers의 cust_id와 연결

실수에 의한 데이터 삭제를 방지하는 외래 키

외래 키는 참조 무결성을 유지하는 데 도움을 준다. 외래 키가 정의되면, DBMS는 다른 테이블과 연결된 행을 삭제할 수 없다. 이러한 삭제 방식 덕분에 외래 키는 실수로 데이터를 삭제하는 것을 방지한다.

그렇지만 일부 DBMS는 테이블에서 행이 삭제되면 관련된 행을 모두 삭제하는 계단식 삭제 기능을 지원하는데 이를 주의하자.

고유 키 무결성 제약 조건

고유 키 무결성 제약 조건 Unique Constraint은 열에 있는 모든 데이터가 동일한 값을 가질 수 없음을 정의하는 제약 조건이다. 기본 키와 비슷해 보이지만, 몇 가지 차이점이 있다.

• 테이블은 여러 고유 키 무결성 제약 조건을 가질 수 있지만, 기본 키는 하나만 정의되어야 한다.

• 고유 키 무결성 제약 조건 열은 NULL 값을 가질 수 있다.

• 고유 키 무결성 제약 조건 열은 변경되거나 업데이트 될 수 있다.

• 고유 키 무결성 제약 조건 열의 값은 재사용 될 수 있다.

• 기본 키와 달리 고유 키 무결성 제약 조건은 외래 키로 정의되어 사용될 수 없다.

고유 키 무결성 제약 조건의 예는 업무에서 많이 사용하는 Employee 테이블이다. 모든 직원은 고유한 주민등록번호를 갖고 있지만, 너무 길기에 기본 키로 적합하지 않고 주민등록번호를 쉽게 사용하면 곤란하다. 그래서 기본 키로 주민등록번호가 아닌 직원 ID를 사용한다. 직원 ID는 기본 키 이기에 고유함을 확신할 수 있다. 그리고 주민등록번호도 고유함을 보장받아야 한다. 이때 주민등록번호 열에 고유 키 무결성 제약 조건을 정의한다.

고유 키 무결성 제약 조건은 다른 제약 조건과 비슷하다. 테이블 정의시 UNIQUE 키워드를 사용하거나 CONSTRAINT를 사용하면 된다.

체크 무결성 제약 조건

체크 무결성 제약 조건 Check Constraint은 열에서 허용 가능한 데이터의 범위나 조건을 지정하기 위한 제약 조건이다. 일반적 용도는 다음과 같다.

• 최솟값이나 최댓값 확인

• 범위 지정

• 특정 값만 허용

체크 무결성 제약 조건은 데이터형 내에서 좀 더 제한을 둘 수 있다. 그리고 데이터베이스에 원하는 데이터만 삽입하는 것을 보장하는 역할을 한다. 클라이언트 프로그램에 의존하거나 사용자에 기대하는 것보다 DBMS가 유효하지 않은 데이터를 모두 거절하는 것이다.

CREATE TABLE OrderItems
(
    order_num    INTEGER    NOT NULL,
    order_item    INTEGER    NOT NULL,
    prod_id    CHAR(10)    NOT NULL,
    quantity    INTEGER    NOT NULL CHECK (quantity > 0),
    item_price    MONEY    NOT NULL
)
-- 새로운 행을 삽입하거나 업데이트할 때 quantity 가 0보다 큰지 확인한다.

ADD CONSTRAINT CHECK (gender LIKE '[MF]') 
-- gender 열이 M이나 F값을 가졌는지 확인한다.

인덱스 이해하기

인덱스는 데이터를 논리적으로 정렬해 검색과 정렬 작업 시 속도를 높이는데 사용한다.

데이터 검색의 성능은 올바른 정렬이 좌우한다. 잘 정렬된 데이터에서 특정 값을 찾는 것은 어렵지 않다.

데이터베이스의 기본 키는 항상 정렬되어 있어서 기본 키로 특정 데이터를 가져오는 것은 언제나 빠르면서 효율적인 작업이다. 하지만 다른 열로 값을 찾는 것은 그만큼 효율적이지 않다. 이를 위해 인덱스를 사용한다. 하나 이상의 열을 인덱스로 정의할 수 있는데, 인덱스로 정의한 열은 DBMS가 내용을 정렬해 저장한다. 인덱스를 정의하면 DBMS는 정렬된 인덱스를 검색해 원하는 데이터의 위치를 알아내고 그 위치에서 특정 행을 가져온다.

인덱스를 만들 때 염두할 내용

• 인덱스는 검색 성능을 개선하지만, 데이터 삽입, 수정, 삭제 성능은 저하된다. 이 작업들은 인덱스를 동적으로 업데이트해야 하기 때문이다.

• 인덱스 데이터는 저장 공간을 많이 차지한다.

• 모든 데이터가 인덱스에 적합한 것은 아니다. 고유하지 않은 데이터는 인덱스로 얻는 이득이 없다.

• 인덱스는 데이터 필터링과 정렬에 사용된다. 특정 순서로 데이터를 자주 정렬한다면, 그 데이터는 인덱싱 후보가 된다.

• 여러 열을 하나의 인덱스로 정의할 수 있다. 이러한 인덱스는 데이터를 정렬할 때만 사용한다.

어떤 열을 언제 인덱스로 정의해야 하는지 특별한 규칙은 없지만, 많은 DBMS가 인덱스 효과를 검증하기 위해 유틸리티를 제공한다.

인덱스는 CREATE INDEX 문으로 생성할 수 있다.

CREATE INDEX prod_name_ind
ON Products (prod_name)
-- prod_name_ind라는 이름으로 Products 테이블의 prod_name으로 인덱스를 생성한다.

트리거 이해하기

트리거는 특정 데이터베이스 작업이 발생하면 자동으로 수행되는 특별한 저장 프로시저로 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 작업이 발생하면 자동으로 실행되는 코드이다.

단순히 SQL 문을 저장해놓은 저장 프로시저와 달리 트리거는 테이블과 묶여서 동작한다.

트리거는 다음과 같은 데이터에 접근할 수 있다.

• INSERT 작업으로 추가된 데이터

• UPDATE 작업으로 처리한 이전 데이터와 새로운 데이터

• DELETE 작업으로 삭제한 데이터

트리거는 지정 작업이 수행되기 전 또는 후에 수행되는데, 이는 DBMS에 따라 다르다.

트리거의 일반적인 용도는 다음과 같다.

• 데이터 일관성 보장

• 테이블의 변화를 감지하여 특정 작업 수행

• 추가적인 데이터 유효성 검사나 데이터 롤백 수행

• 다른 열들의 값을 기초로 어떠한 계산을 하거나 타입스탬프를 갱신

트리거 예시

-- Customers 테이블에 INSERT나 UPDATE 작업마다 cust_state 열을 대문자로 변환하는 트리거 생성

-- SQL Server
CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;

-- Oracle, PostgreSQL
CREATE TRIGGER customer_state
AFTER INSERT OR UPDATE
FOR EACH ROW
BEGIN
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = :OLD.cust_id
END;

제약 조건이 트리거보다 빠르다. 가능하면 트리거 대신 제약 조건을 사용할 것.

데이터베이스 보안

데이터보다 중요한 것은 없다.

데이터는 항상 잘 보호되어야 하고, 동시에 사용자가 필요한 데이터에 접근할 수 있어야 한다. 그래서 대부분의 DBMS는 관리자에게 데이터 접근 권한을 부여하거나 제재하는 메커니즘을 제공한다.

보안 시스템의 기본은 사용자의 승인과 인증이다. 이는 사용자가 본인의 신분을 밝히는 것과 허용된 작업을 수행할 수 있는지 확인하는 절차이다. 몇몇 DBMS는 운영체제 보안과 통합해 사용하기도 하고, 어떤 DBMS는 데이터베이스 내부에서 각자 사용자와 패스워드 목록을 보관하기도 한다. 어떤 DBMS는 외부 서버와 연동하여 관리하기도 한다.

일반적으로보안이 적용되어야 할 작업은 다음과 같다.

• 테이블 생성, 변경, 삭제와 같은 데이터베이스 관리 기능에 대한 접근

• 특정 데이터베이스나 테이블에 대한 접근

• '읽기 전용', '특정 열에만 접근'과 같은 접근 유형

• 뷰나 저장 프로시저를 통해 접근할 수 있는 테이블 지정

• 로그인한 계정에 따라 접근과 제어 권한을 다양하게 부여하는 다단계 보안 레벨 생성

• 사용자 계정 관리 권한

보안은 SQL GRANT와 REVOKE 문으로 관리할 수 있는데, 대부분의 DBMS는 대화형 관리자 유틸리티를 제공한다. 이 유틸리티 내부에서 GRANT와 REVOKE 문을 사용한다.

정리하기

• 고급 SQL 기능

• 제약 조건으로 참조 무결성을 보장하기

• 데이터 검색 성능을 향상하는 인덱스

• 지정한 특정 작업 처리에 수행되는 트리거

• 데이터 접근 권한을 관리하는 보안 옵션