[SQL] 22장. 고급 데이터 조작 옵션

김상현·2022년 10월 21일
sql
0

SQL

목록 보기
22/22
post-thumbnail

[손에 잡히는 10분 SQL - 벤 포터 지음, 박남혜 옮김] 책의 학습 후 정리자료입니다.

📍 제약 조건 이해하기

  • SQL은 여러 차례의 버전 업그레이드를 거쳐 매우 완전하면서도 강력한 언어가 되었다.
  • 이런 강력한 기능들은 제약 조건과 같은 데이터 조작 기술을 제공한다.
  • 관계형 데이터베이스는 데이터를 여러 테이블에 나누어 저장하고, 각 테이블은 관련된 데이터를 가진다.
  • 한 테이블과 다른 테이블을 참조할 때는 키를 사용한다(참조 무결성).
  • 관계형 데이터베이스 설계가 제대로 작동하려면, 유효한 데이터만 테이블에 삽입한다는 보장이 필요하다.
  • 새로운 행을 삽입하기 전에 클라이언트가 직접 다른 테이블과 관련된 값들이 존재하고 유효한지 확인해볼 수 있지만, 다음 이유로 피하는 것이 좋다.
    • 클라이언테 레벨에서 데이터 무결성 규칙을 확인한다면, 모든 클라이언트가 이 규칙을 확인해야 하는데 일부 클라이언트가 제대로 확인하지 않을 수도 있다.
    • UPDATE와 DELETE 작업에서도 이 규칙을 확인해야 한다.
    • 클라이언트에서 무결성을 확인하는 것은 시간이 많이 소요되는 방법이다. DBMS가 확인하는 것이 훨씬 효율적이다.

📒 제약 조건

데이터베이스 데이터를 어떻게 삽입하고 조작할 것인지 통제하는 규칙
  • DBMS는 데이터베이스 테이블에 제약 조건을 정의해 참조 무결성을 보장한다.
  • 대부분의 제약 조건은 테이블 정의에 명시된다(CREATE TABLE, ALTER TABLE 사용).

📌 기본 키

  • 기본 키(Primary Key) 는 특별한 제약 조건으로서 열(또는 열 집합)에 있는 값이 고유하면서 절대 변하지 않는다는 것을 보장하기 위해 사용한다.
  • 테이블의 열(또는 열 집합)로 테이블에 있는 행을 고유하게 구별할 수 있는 값을 가진다.
  • 그리고 특정 행을 매우 쉽게 조작할 수 있게 해준다.
  • 기본 키가 없다면 다른 행에 영향을 주지 않고 특정 행만 안전하게 업데이트하거나 삭제하는 것이 매우 힘들다.
  • 다음 조건만 만족한다면, 테이블에 있는 열은 어떤 열이든 기본 키로 설정할 수 있다.
    • 두 개 이상의 행이 같은 기본 키 값을 가질 수 없다.
    • 모든 행은 기본 키 값을 반드시 가져야 한다(기본 키 열은 NULL 값을 혀용하면 안된다).
    • 기본 키 값을 가진 열은 변경하거나 업데이트할 수 없다.
    • 기본 키 값은 절대 다시 사용되어서는 안 된다. 테이블에서 행이 삭제되더라도, 그 값이 다른 행에 다시 할당되어서는 안된다.

🧷 기본 키 정의

  • CREATE 시 기본 키 정의
CREATE TABLE Vendors (
	vend_id			CHAR(10)	NOT NULL PRIMARY KEY,
    vend_name		CHAR(50)	NOT NULL,
    vend_address	CHAR(50)	NULL,
    vend_city		CHAR(50)	NULL,
    vend_state		CHAR(5)		NULL,
    vend_zip		CHAR(10)	NULL,
    vend_country	CHAR(50)	NULL
);
  • ALTER 시 기본 키 정의
ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY(vend_id);

📌 외래 키

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

왜래 키 정의

  • CREATE 시 기본 키 정의
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 시 기본 키 정의
ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY(cust_id) REFERENCES Customers(cust_id);

💡 왜래 키는 실수로 데이터를 삭제하는 것을 막아준다.

왜래 키는 참조 무결성을 유지하는 데 도움을 준다.
왜래 키가 정의되면, DBMS는 다른 테이블과 연결된 행은 삭제할 수 없다.
예를 들면 주문 테이블과 연결된 고객 정보는 삭제할 수 없다. 고객을 삭제하려면, 연결된 주문을 먼저 삭제해야만 한다.
마찬가지로 이 주문을 삭제하려면 연결된 주문 항목부터 삭제해야 한다는 뜻이다.
이러한 삭제 방식 덕분에 외래 키는 실수로 데이터를 삭제하는 것을 막아준다.
그렇지만 일부 DBMS는 계단식 삭제라고 부른 기능을 지원하는데, 이것은 테이블에서 행이 삭제되면 관련된 행을 모두 삭제하는 기능이다.

📌 고유 키 무결성 제약 조건

  • 고유 키 무결성 제약 조건(Unique Constraint) 은 열(또는 열 집합)에 있는 모든 데이터가 동일한 값을 가질 수 없음을 정의하는 제약 조건이다.
  • 기본 키와 비슷해 보이지만, 몇 가지 차이점이 있다.
    • 테이블은 여러 고유 키 무결성 제약 조건을 가질 수 있지만, 기본 키는 한 테이블에 하나만 정의되어야 한다.
    • 고유 키 무결성 제약 조건 열은 NULL값을 가질 수 있다.
    • 고유 키 무결성 제약 조건 열은 변경되거나 업데이트될 수 있다.
    • 고유 키 무결성 제약 조건 열의 값은 재사용될 수 있다.
    • 기본 키와는 달리 고유 키 무결성 제약 조건은 외래 키로 정의되어 사용될 수 없다.

🧷 Employees 테이블 제약 조건 사용 예

모든 직원은 고유한 주민등록번호를 갖고 있지만, 너무 길기 때문에 기본키로 사용하지 않는다.
또한 주민등록번호를 너무 쉽게 사용하면 곤란하다는 것도 하나의 이유가 될 수 있다.
그래서 기본 키로는 주민등록번호가 아닌 직원 ID를 사용한다.
직원 ID는 기본 키이기 때문에, 고유하다는 것을 확신할 수 있다.
그리고 주민등록번호도 고유하다는 것을 보장받아야 한다(오타로 다른 사람의 번호를 사용하는 것을 막기 위해).
그러면 주민등록번호 열에 고유 키 무결성 제약 조건을 정의하면 된다.
  • 고유 키 무결성 제약 조건 문법은 다른 제약 조건과 비슷하다.
  • 테이블을 정의할 때 UNIQUE 키워드를 사용하거나 CONSTRAINT 를 사용하면 된다.

📌 체크 무결성 제약 조건

  • 체크 무결성 제약 조건(Check Constraint) 은 열에서 허용 가능한 데이터의 범위나 조건을 지정하기 위한 제약 조건이다.
    • 최소값이나 최대값 확인
      • ex) 제품 수량이 0이 될 수 없게 한다(0이 유효한 숫자일지라도).
    • 범위 지정
      • ex) 배송 날짜는 오늘이거나 오늘 이후여야 하고, 현재 날짜에서 1년 이내 여야 한다.
    • 특정 값만 허용
      • ex) 성별 필드에 M과 F만 허용한다.
  • 데이터형은 열에 저장하는 데이터의 형식을 제한한다면, 체크 무결성 제약 조건은 데이터형 내에서 좀 더 제한을 둘 수 있다.
  • 데이터베이스에 원하는 데이터만 삽입하는 것을 보장하는 역할을 한다.
  • 클라이언트 프로그램에 의존하거나 사용자가 올바르게 입력하기를 기대하는 것보다 DBMS가 유효하지 않은 데이터는 모두 거절하는 것이다.

🧷 체크 무결성 제약 조건 정의 예

  • CREATE 시 체크 무결성 제약 조건 정의
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
);
  • ALTER 시 체크 무결성 제약 조건 정의
ALTER TABLE OrderItems
ADD CONSTRAINT CHECK(gender LIKE '[MF]')

💡 사용자 정의 데이터형

일부 DBMS에서는 사용자가 직접 데이터형을 정의할 수 있다.
사용자 데이터형은 사실 체크 무결성 제약 조건이나 다른 제약 조건을 사용하여 만든 데이터형이다.
예를 들어 체크 무결성 제약 조건으로 M과 F라는 한 글자만 허용하는 gender라는 데이터형을 정의하면,
이 데이터형을 테이블 정의에 사용할 수 있다.
이 데이터형의 장점은 데이터형을 정의할 때 제약 조건을 한 번만 사용해도 데이터형이 사용될 때마다 자동으로 적용된다.

📍 인덱스 이해하기

  • 인덱스(INDEX) 는 데이터를 논리적으로 정렬해 검색과 정렬 작업 시 속도를 높이는 데 사용한다.
  • 기본 키 데이터는 항상 정렬되어 있어서, 기본 키로 특정 데이터를 가져오는 것은 언제나 빠르면서 동시에 효율적인 작업이다.
  • 하지만, 다른 열로 값을 찾는 것은 보통 기본 키로 찾는 것만큼 효율적이지 않다.
  • 데이터베이스는 하나 이상의 열을 인덱스로 정의할 수 있는데, 인덱스로 정의한 열은 DBMS가 내용을 정렬해서 저장해 놓는다.
  • 인덱스를 정의하면, DBMS는 책에서 찾아보기를 사용할 때와 거의 비슷한 방식으로 찾는다. 정렬된 인덱스를 검색해서 원하는 데이터의 위치를 먼저 알아내고, 그 위치에서 특정 행을 가져온다.
  • 인덱스 생성시 고려해야할 사항
    • 인덱스는 검색 성능을 개선하지만, 데이터 삽입, 수정, 삭제 성능은 저하된다. 이런 작업을 수행할 때마다 DBMS는 인덱스를 동적으로 업데이트해야 하기 때문이다.
    • 인덱스 데이터는 저장 공간을 많이 차지한다.
    • 모든 데이터가 인덱스에 적합한 것은 아니다. 충분히 고유하지 않은 데이터는 성과 이름 같은 데이터보다 인덱스로 정의하여 얻는 이득이 별로 없다.
    • 인덱스는 데이터 필터링과 정렬에 사용된다. 특정 순서로 데이터를 자주 정렬한다면, 그 데이터는 인덱싱 후보가 될 수 있다.
    • 여러 열을 하나의 인덱스로 정의할 수 있다(예를 들어 나라 이름 + 도시명). 이러한 인덱스는 순서대로 데이터를 정렬할 때만 사용한다(만약 데이터를 도시명으로만 정렬하길 원한다면 이 인덱스는 쓸모가 없다).

🧷 인덱스 생성 예

CREATE INDEX prod_name_ind
ON Products (prod_name)
  • 인덱스는 CREATE INDEX 구문으로 생성할 수 있다.
  • 모든 인덱스는 고유한 이름을 가져야 한다.
  • 여기에서는 prod_name_ind 라는 이름을 사용하였고, CREATE INDEX 키워드 다음에 정의하였다.
  • ON은 인덱스를 정의하는 테이블을 지정하기 위해 사용한다.
  • 인덱스에 포함되는 열은 테이블 이름 다으멩 괄호로 묶어서 지정할 수 있다.

💡 정기적인 인덱스 점검

인덱스의 효과는 테이블에 데이터를 추가하거나 변경하면서 변한다.
많은 데이터베이스 관리자들은 몇 달간 데이터를 조작하고 나면,
한동안 이상적이라고 생각했던 인덱스가 이상적이지 않을 수 있다는 것을 알게 된다.
필요할 때마다 정기적으로 인덱스를 다시 정리하는 것이 좋다.

📍 트리거 이해하기

  • 트리거(TRIGGER) 는 특정한 데이터베이스 작업이 발생하면서 자동으로 수행되는 특별한 저장 프로시저로서, 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 작업이 일어나면 자동을 실행되는 코드이다.
  • 단순히 SQL 문을 저장해 놓은 것일 뿐인 저장 프로시저와는 달리, 트리거는 테이블과 묶여서 동작한다.
  • 트리거는 다음과 같은 데이터에 접근할 수 있다.
    • INSERT 작업으로 추가된 데이터
    • UPDATE 작업으로 처리한 이전 데이터와 새로운 데이터
    • DELETE 작업으로 삭제한 데이터
  • 트리거는 지정한 작업이 수행되기 전 또는 후에 수행되는데, 이는 DBMS에 따라 다르다.
  • 트리거의 일반적인 용도는 다음과 같다.
    • 데이터 일관성 보장
      • ex) INSERT나 UPDATE 작업을 수행할 때 모든 도시명을 대문자가 되게 한다.
    • 테이블의 변화를 감지하여 특정한 작업을 수행
      • ex) 행을 업데이트하거나 삭제할 때마다 로그 테이블에 기록한다.
    • 추가적인 데이터 유효성 검사나 데이터 롤백 수행
      • ex) 고객의 신용 한도가 초과하였는지 확인하고, 초과한 경우 데이터 추가를 막는다.
    • 다른 열들의 값을 기초로 어떠한 계산을 하거나 타입스탬프를 갱신

🧷 트리거 생성 예

  • 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 문을 사용한다는 점을 알아두자.
profile
김상현
목적 있는 글쓰기
이전 포스트

[SQL] 21장. 커서 이용하기

0개의 댓글