무결성 제약조건이란?

테이블 생성 시에 Constraint(제약조건)를 사용해서 입력하는 자료에 대해서 제약, 즉 규칙을 정해줄 수 있습니다. 이때 정해진 제약에 따라서 데이터가 입력이 됩니다. 만약 제약에 배반된다면 자료 입력이 거부되면서 오류가 납니다.

네이버 지식백과에서는 데이터 무결성에 대해서 다음과 같이 서술하고 있습니다.

무결성 : 데이터 및 네트워크 보안에 있어서 정보가 인가된 사람에 의해서만 접근이나 변경이 가능한 성질
데이터 무결성 : 데이터를 인가하지 않은 방법으로 변경할 수 없도록 보호하는 성질


쉽게 말해서, 권한이 부여된 계정이나 사람만이 접근 가능하고, 정확하고 완전한 데이터들이 저장되어 있는 상태라고 생각하면 됩니다. 여기서 제약조건은 이러한 데이터 무결성을 보장함으로써 이상한 데이터가 저장되지 않도록 하는 기능을 제공합니다.

제약조건

데이터의 무결성을 위해서 데이터베이스에서는 5가지 제약조건(Constraint)를 제공하고 있습니다.

제약조건내용
NOT NULL '필수 입력 사항'을 의미
 UNIQUE 중복성 배제 즉, '유일한 값'으로 존재해야 함을 의미
 PRIMARY KEY(기본키) NOT NULL + UNIQUE로, '테이블에서 대표되는 컬럼'을 의미
 FOREIGN KEY(외래키) '참조하는 테이블에서 존재하는 값만 사용 가능'을 의미
 CHECK '주어진 조건에 해당하는 값만 입력 가능'을 의미

1. NOT NULL

NOT NULL은 INSERT 시, 즉 데이터 입력시에 누락이 되어서는 안되는 부분입니다. NULL값이 들어가게 되면 오류가 납니다.

2. UNIQUE

UNIQUE는 해당 테이블에 있어서 존재하는 값이 유일해야 합니다. 만일 INSERT 또는 UPDATE 시, 제약이 걸려있는 컬럼에 동일한 데이터가 존재한다면 오류가 납니다. 이때, NULL 값에 대해서는 UNIQUE 제약이 적용되지 않습니다.  즉, NULL 값은 데이터로 인식하지 않기때문에 해당 컬럼에 NULL 데이터행이 여러개 존재 가능합니다.

테이블을 만들때 제일 밑에 CONSTRAINTS 제약명 UNIQUE (컬럼2, 컬럼3) 과 같이 CONSTRAINTS의 이름을 지정이 가능합니다. UNIQUE 제약으로 들어가는 컬럼들은 그들의 조합이 유일해야 합니다. 각 컬럼의 데이터의 유일함은 의미가 없고, 조합이 유일해야 합니다. 예를 들면, a라는 컬럼과 b라는 컬럼에 각각 1,2 그리고 1,3 (a 컬럼의 1 값이 동일)의 데이터는 존재가 가능합니다. a 컬럼의 데이터가 같다고 하더라도 그 조합이 다르기 때문에 오류를 일으키지 않습니다.

3. PRIMARY KEY

PRIMARY KEY는 하나의 테이블에 있는 데이터들을 식별하기 위한 기준으로 인식되는 제약조건입니다. 한개의 테이블에 하나만 생성이 가능합니다. PRIMARY KEY는 NOT NULL + UNIQUE 의 속성을 가집니다. 즉, NULL값이 있으면 안되고 해당 컬럼의 데이터는 중복되지 않고 유일해야 합니다. UNIQUE에서와 같이 여러 컬럼들을 조합해서 지정할 수 있습니다.

4. FOREIGN KEY

FOREIGN KEY는 해당 컬럼에 참조하는 테이블로부터 존재하는 값들만 사용한다는 의미의 제약조건입니다. 만일 참조하는 테이블에 해당하는 값이 없을시에는 INSERT시나 UPDATE시에 오류가 나게 됩니다. 외래키는 여러개의 컬럼에 중복적으로 적용 가능합니다. 외래키로 두 테이블이 부모테이블과 자식테이블로 관계를 맺고 있을시에, 자식테이블이 참조하는 데이터는 부모 테이블에서 삭제가 불가능합니다. (FOREIGN KEY의 기본옵션일 때) FOREIGN KEY는 4가지 옵션을 가지고 있는데 이 옵션에 따라 부모테이블에서 삭제할 시, 자식 테이블에서 참조하는 데이터도 삭제가 되도록 만들 수도 있습니다.

# FOREIGN KEY 옵션

NO ACTION

데이터베이스 엔진에서는 오류가 발생하며 부모 테이블의 행에 대한 삭제 또는 업데이트 동작이 롤백됩니다.

CASCADE

부모 테이블에서 해당 행이 업데이트되거나 삭제될 때 참조 테이블에서도 해당 행이 업데이트 또는 삭제됩니다. timestamp 열이 외래 키 또는 참조되는 키의 일부인 경우에는 CASCADE를 지정할 수 없습니다. INSTEAD OF DELETE 트리거가 있는 테이블에는 ON DELETE CASCADE를 지정할 수 없습니다. INSTEAD OF UPDATE 트리거가 있는 테이블에 대해서는 ON UPDATE CASCADE를 지정할 수 없습니다.

SET NULL

부모 테이블에서 행을 업데이트하거나 삭제하면 해당 외래 키를 구성하는 모든 값이 NULL로 설정됩니다. 이 제약 조건을 실행하려면 외래 키 열이 Null을 허용해야 합니다. INSTEAD OF UPDATE 트리거가 있는 테이블에 대해서는 지정할 수 없습니다.

SET DEFAULT

부모 테이블에서 해당 행을 업데이트하거나 삭제하면 외래 키를 구성하는 모든 값이 기본값(Default로 지정한 값)으로 설정됩니다. 이 제약 조건을 실행하려면 모든 외래 키 열에 기본 정의가 있어야 합니다. 열이 Null을 허용하고 명시적 기본값이 설정되어 있지 않은 경우 NULL은 해당 열의 암시적 기본값이 됩니다. INSTEAD OF UPDATE 트리거가 있는 테이블에 대해서는 지정할 수 없습니다.

RESTRICT

자식테이블에 데이터가 남아있는 경우 부모 테이블의 데이터는 수정 또는 삭제를 할 수 없습니다.


CASCADE, SET NULL, SET DEFAULT 및 NO ACTION은 서로 참조 관계를 가진 테이블에서 결합될 수 있습니다. 데이터베이스 엔진 이 NO ACTION을 발견하면 관련된 CASCADE, SET NULL 및 SET DEFAULT 동작을 멈추고 롤백합니다. DELETE 문으로 CASCADE, SET NULL, SET DEFAULT 및 NO ACTION 동작을 결합하면 데이터베이스 엔진 이 NO ACTION을 확인하기 전에 모든 CASCADE, SET NULL 및 SET DEFAULT 동작을 적용합니다.


외래키를 사용한 테이블을 삭제할 때는 제약조건에 어긋나지 않게 순서대로 삭제해야 삭제가 됩니다.
참조하는 레코드 및 테이블 (자식 레코드 및 테이블)-> 참조당하는 레코드 및 테이블 (부모 레코드 및 테이블)

5. CHECK

CHECK는 조건에 부합하는 데이터만 입력이 가능하도록 하는 제약조건입니다. 조건에는 기본연산자나 비교연산자, IN, NOT IN 등등이 사용 가능합니다.

'DB' 카테고리의 다른 글

[SQL] WHERE절에서 IF문처럼 AND 사용  (0) 2017.02.02
[DB] 클러스터  (0) 2016.11.18
[DB] 무결성 제약조건  (0) 2016.11.18
[DB] 트랜잭션, REDO와 UNDO 개념  (0) 2016.11.18
[DB] DDL, DML, DCL 이란?  (1) 2016.11.18
[SQL] SELECT 결과 UPDATE  (0) 2016.11.18

트랜잭션이란?

데이터베이스 트랜잭션(Database Transaction)은 데이터베이스 관리 시스템 또는 유사한 시스템에서 상호작용의 단위입니다. 여기서 유사한 시스템이란 트랜잭션이 성공과 실패가 분명하고 상호 독립적이며, 일관되고 믿을 수 있는 시스템을 의미합니다.

이론적으로 데이터베이스 시스템은 각각의 트랜잭션에 대해 원자성(Atomicity), 일관성(Consistency), 고립성(Isolation), 영구성(Durability)을 보장합니다. 이 성질을 첫글자를 따 ACID라 부릅니다. 그러나, 실제로는 성능향상을 위해 이런 특성들이 종종 완화되곤 합니다.


다시말해서 트랜잭션은 데이터베이스 내에서 한꺼번에 수행되어야 할 일련의 연산들입니다. 간단하게 말해서 전부 성공하거나 전부 실패되거나 둘 중 하나의 작업을 수행합니다. 트랙잭션의 모든 연산은 반드시 한꺼번에 완료가 되야 하며 그렇지 않은 경우에는 한꺼번에 취소되어야 하는 원자성을 가지고 있습니다.

한꺼번에 완료가 된 경우에는 COMMIT을 호출해 작업결과를 데이터베이스에 반영 됩니다취소가 되거나 문제가 발생한 경우에는 ROLLBACK을 호출하고 작업결과를 모두 취소하여 데이터베이스에 영향을 미치지 않게 됩니다. 

트랜잭션 성질

트랜잭션이 진행되기 전과 완료된 후에 상태를 볼 수 있지만 트랜잭션이 진행되는 중간 데이터는 볼수 없습니다. 

1. 원자성(Atomicity)

분리 할수 없는 하나의 단위로 작업은 모두 완료되거나 모두 취소 되어야 합니다.

예로 이체 과정 중에 트랜잭션이 실패하게 되어 예금이 사라지는 경우가 발생해서는 안 되기 때문에 DBMS는 완료되지 않은 트랜잭션의 중간 상태를 데이터베이스에 반영해서는 안 됩니다. 즉, 트랜잭션의 모든 연산들이 정상적으로 수행 완료되거나 아니면 전혀 어떠한 연산도 수행되지 않은 상태를 보장해야 합니다. atomicity는 쉽게 'all or nothing' 특성으로 설명된다

2. 일관성(Consistency)

사용되는 모든 데이터는 일관되어야 합니다.

고립된 트랜잭션의 수행이 데이터베이스의 일관성을 보존해야 합니다. 즉, 성공적으로 수행된 트랜잭션은 정당한 데이터들만을 데이터베이스에 반영해야 합니다. 트랜잭션의 수행을 데이터베이스 상태 간의 전이(transition)로 봤을 때, 트랜잭션 수행 전후의 데이터베이스 상태는 각각 일관성이 보장되는 서로 다른 상태가 됩니다. 트랜잭션 수행이 보존해야 할 일관성은 기본 키, 외래 키 제약과 같은 명시적인 무결성 제약 조건들 뿐만 아니라, 자금 이체 예에서 두 계좌 잔고의 합은 이체 전후가 같아야 한다는 사항과 같은 비명시적인 일관성 조건들도 있습니다.

3. 격리성(Isolation)

접근하고 있는 데이터는 다른 트랜잭션으로 부터 격리 되어야 합니다.

여러 트랜잭션이 동시에 수행되더라도 각각의 트랜잭션은 다른 트랜잭션의 수행에 영향을 받지 않고 독립적으로 수행되어야 합니다. 즉, 한 트랜잭션의 중간 결과가 다른 트랜잭션에게는 숨겨져야 한다는 의미인데, 이러한 isolation 성질이 보장되지 않으면 트랜잭션이 원래 상태로 되돌아갈 수 없게 됩니다. Isolation 성질을 보장할 수 있는 가장 쉬운 방법은 모든 트랜잭션을 순차적으로 수행하는 것입니다. 하지만 병렬적 수행의 장점을 얻기 위해서 DBMS는 병렬적으로 수행하면서도 일렬(serial) 수행과 같은 결과를 보장할 수 있는 방식을 제공하고 있습니다.

4. 영속성(Durability)

트랙잭션이 정상 종료되면 그 결과는 시스템에 영구적으로 적용되어야 합니다.

트랜잭션이 성공적으로 완료되어 COMMIT되고 나면, 해당 트랜잭션에 의한 모든 변경은 향후에 어떤 소프트웨어나 하드웨어 장애가 발생되더라도 보존되어야 합니다.

5. 순차성(Sequentiality)

데이터를 다시 로드하고 트랜잭션을 재생하여 원래 트랜잭션이 수행된 후의 상태로 데이터를 되돌리는 것을 말합니다.

UNDO, REDO

REDO는 "다시 하다."라는 뜻을 가지고 UNDO는  "원상태로 돌리다" 라는 뜻을 가지고 있습니다. 즉 REDO는 무언가를 다시 하는 것이고 UNDO는 무언가를 되돌리는 것입니다.

REDO는 기본적으로 복구의 역할을 합니다. 오라클 서버에 무슨 작업을 하든지 모두 REDO에 기록이 됩니다. (UNDO 포함)

UNDO는 작업 롤백과, 읽기 일관성, 복구를 합니다.

REDOUNDO의 공통점은 복구를 한다는 것입니다.


하지만 둘의 복구 방법은 차이가 있습니다.

REDO는 복구를 할때 사용자가 했던 작업을 그대로 다시 하지만 UNDO는 사용자가 했던 작업을 반대로 진행합니다. 즉 사용자의 작업을 원상태로 돌립니다.


예를 들어 아래와 같은 작업을 했을때 세션이 비정상 종료 되었다고 가정합니다.

update t1
set no = no + 1
where id = 1;


세션이 복구 되는 과정에서 아래의 작업을 진행합니다.

update t1
set no = no + 1
where id = 1;


 하지만 만일 세션이 비정상 종료가 되기전 COMMIT을 하지 않았다면 UNDO를 이용하여 아래와 같은 작업을 이어서 하게됩니다.

update t1
set no = no - 1
where id = 1;


복구는 UNDO 를 통해서 복구를 하게 됩니다. 즉, ROLLBACK을 한다는 말입니다. 시스템 장애가 발생하게 되면 UNDO 데이터도 모두 날아갑니다. 결국 시스템 장애시 REDO 데이터를 이용해서 마지막 CHECK POINT부터 장애까지의 DB BUFFER CACHE 를 복구하게 됩니다. 이게 완료가 되면 UNDO를 이용하여 COMMIT되지 않은 데이터를 모두 ROLLBACK 함으로써 복구를 완료하게 됩니다. 결국 REDO가 UNDO를 복구하고 최종적으로 UNDO가 복구를 하게 됩니다. 

참고로 UNDO 데이터는 아래와 같이 기록 됩니다. 

INSERT 시, insert 된 로우의 rowid 기록
UPDATE 시, 바뀐 컬럼의 바뀌기 전 값 기록
DELETE 시, 지워진 모든 데이터 기록

'DB' 카테고리의 다른 글

[DB] 클러스터  (0) 2016.11.18
[DB] 무결성 제약조건  (0) 2016.11.18
[DB] 트랜잭션, REDO와 UNDO 개념  (0) 2016.11.18
[DB] DDL, DML, DCL 이란?  (1) 2016.11.18
[SQL] SELECT 결과 UPDATE  (0) 2016.11.18
[SQL] SQL VS CODE  (0) 2016.10.27


명령어 종류 

명령어 

설명 

데이터 조작어

(DML :  Data Manipulation Language

SELECT 

데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE 라고도 함

INSERT

UPDATE

DELETE 

데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류(데이터 삽입, 수정, 삭제)의 명령어들을 말함.

 데이터 정의어

(DDL : Data Definition Language)

CREATE

ALTER

DROP

RENAME

TRUNCATE 

테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 (생성, 변경, 삭제, 이름변경) 데이터 구조와 관련된 명령어들을 말함.

데이터 제어어

(DCL : Data Control Language) 

GRANT

REVOKE 

데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어들을 말함.

트랜잭션 제어어

(TCL : Transaction Control Language)

COMMIT

ROLLBACK

SAVEPOINT 

논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어를 말함.



'DB' 카테고리의 다른 글

[DB] 무결성 제약조건  (0) 2016.11.18
[DB] 트랜잭션, REDO와 UNDO 개념  (0) 2016.11.18
[DB] DDL, DML, DCL 이란?  (1) 2016.11.18
[SQL] SELECT 결과 UPDATE  (0) 2016.11.18
[SQL] SQL VS CODE  (0) 2016.10.27
[SQL] Union과 Union all 차이  (0) 2016.10.12
  1. 코딩하는흑구 2019.02.27 11:13 신고

    항상 잘보고있습니다.^^

select 결과를 update하는 방법은 여러가지가 존재합니다.

Set Subquery

set 절에 subquery로 쿼리 결과를 update할 수 있습니다. 이 방법의 단점은 쿼리 결과가 0~1개 일때만 유효합니다. 만약 쿼리 결과가 1개 이상 나올 경우 에러가 납니다.

UPDATE [테이블명]
SET (컬럼, 컬럼1, ...) = (값, 값1, ... ) 
[WHERE conditions];

-- 예시
update test set (pk, id, name) = (select pk, id, name from test1 where pk = 1) -- 만약 해당 서브쿼리의 조회결과 갯수가 2개 이상일 시 에러

From Subquery

이 방법은 from절에 subquery를 넣어 사용하는 방법입니다. set절에 subquery를 사용하는 것과는 다르게 쿼리결과가 2개 이상나와도 업데이트를 할 수 있습니다. 단 업데이트를 해야하는 컬럼은 set절에 전부 명시해야합니다.(귀찮)

update [테이블명]
set [업데이트할 컬럼] = [업데이트 값]
from [테이블명]
where [조건]
 
 
--예시
update test
    set count = test.count + a.count
    from (select * from test1) as a
    where test.pk = a.pk
 
-- test테이블 내 pk와 test1 테이블 내 pk 가 같은 데이터에만 count를 추가해줌
-- 조인도 가능


'DB' 카테고리의 다른 글

[DB] 트랜잭션, REDO와 UNDO 개념  (0) 2016.11.18
[DB] DDL, DML, DCL 이란?  (1) 2016.11.18
[SQL] SELECT 결과 UPDATE  (0) 2016.11.18
[SQL] SQL VS CODE  (0) 2016.10.27
[SQL] Union과 Union all 차이  (0) 2016.10.12
[SQL] Joins  (0) 2016.08.24

만약 text 타입의 a라는 컬럼을 smallint로 타입을 변경한다고 할 때 아래와 같은 에러가 납니다.

alter table test alter column a type smallint;
 
-- 에러 발생
You might need to specify "USING a::smallint


텍스트 또는 varchar에서 정수로의 자동 변환을 지원하지 않습니다. (즉, 정수로 예상하는 함수에 varchar를 전달하거나 varchar 필드를 정수로 할당 할 수 없습니다.) 따라서 ALTER TABLE ...을 사용하여 형변환에 대해 명시적으로 지정해야합니다.

alter table test alter column a type smallint using a::smallint;


기타 sql에는 first()와 last() aggregate function을 제공하지만 postgresql에서는 직접 제공해주지 않습니다. 따라서 다른 sql에서 사용하는 first()와 last()를 사용하기 위해선 아래의 프로시저 함수를 추가해야 동일한 기능을 사용가능합니다.

-- Drop a function if exists
DROP FUNCTION IF EXISTS first_agg(anyelement, anyelement) CASCADE;
 
-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $1;
$$;
 
-- And then wrap an aggregate around it
CREATE AGGREGATE FIRST (
        sfunc    = first_agg,
        basetype = anyelement,
        stype    = anyelement
);
 
-- Drop a function if exists
DROP FUNCTION IF EXISTS last_agg(anyelement, anyelement) CASCADE;
-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $2;
$$;
 
-- And then wrap an aggregate around it
CREATE AGGREGATE LAST (
        sfunc    = last_agg,
        basetype = anyelement,
        stype    = anyelement
);


+ Random Posts