[SQL] 제약 / 무결성

1. 제약 - 무결성 관리

모든 데이터가 결함없이 완벽한 상태를 무결성(Intergrity)라고 합니다.
DBMS는 무결성을 지키기 위해 최선의 노력을 하며, 이전의 단순한 정보 저장 방식과 다르게 데이터를 관리합니다.

📌 클라이언트/서버 측 무결성 관리
데이터를 처리하는 단계별로 클라이언트 측과 서버 측의 무결성 관리 정책을 제공합니다.
▪ 클라이언트 측 : 입력할 정보에 맞는 라디오 버튼, 리스트 박스 등의 컨트롤으로 잘못된 입력을 차단합니다.

▪ 서버 측 : DBMS가 규칙을 기억하고 있다가 비정상 데이터의 입력을 거부하는 방식입니다.

① 칼럼 무결성
칼럼 하나에 저장되는 원자적인 값을 점검하며 도메인 무결성이라고도 합니다. 타입 지정, NULL 허용 여부, 체크(CHECK), 기본 값(DEFAULT) 등의 제약을 지정합니다.

② 엔티티 무결성
레코드끼리 중복 값을 가지지 않도록 하여 유일한 식별자를 관리합니다.

③ 참조 무결성
테이블 간의 관계를 구성하는 키가 항상 유효하도록 관리하기 위해 사용합니다. 참조하는 테이블 간 정보가 반드시 존재하도록 합니다.

2. 제약 조건

1) NULL

(1) NULL 허용

NULL / NOT NULL
필수 입력 필드와 필수 입력이 아닌 필드를 NULL 허용 조건을 통해 제약이 가능합니다.
테이블 생성 시, 필드 선언문 끝에 항상 NULL, NOT NULL을 분명히 명시하는 것이 좋습니다.

2) 기본값

DEFAULT
NULL 허용 속성은 데이터베이스 성능을 저해하게 됩니다.
기본 값이 정해진 경우는 NULL 허용 대신 DEFAULT를 사용하는 것이 성능상 유리합니다.

기본 값은 필드의 값을 지정하지 않았을 때 자동으로 입력할 값을 말하며, 보통 0, 공백, N/A등을 많이 사용합니다.

INSERT INTO 테이블명 VALUES ('강릉', 1111, 22, DEFAULT, '강원');

✔ 값을 입력할 때도 DEFAULT 값을 키워드로 사용 가능

UPDATE 테이블명 SET 필드명 = DEFAULT WHERE name = '인천';

✔ 기본 값으로 변경할 때에도 DEFAULT 키워드를 사용할 수 있습니다.

3) CHECK

CHECK
체크 제약은 필드의 값 종류를 제한할 때 사용합니다.

CREATE TABLE checkTest (
  gender CHAR(3) NULL CHECK(gender = '남' OR gender = '여'),
  grade INT NULL CHECK (grade >= 1 AND grade <= 3),
  origin CHAR(3) NULL CHECK(origin IN ('동','서','남','북')),
  NAME CHAR(10) NULL CHECK(NAME LIKE '김%')
);

✔ CHECK 뒤 ()를 통해서 값을 제한
✔ WHERE 절의 조건을 지정하는 모든 문법 사용 가능
✔ INSERT(삽입), UPDATE(수정) 할 때 도 CHECK 조건이 맞아야 가능
✔ 오타 방지에도 사용 가능

4) 식별자

(1) 기본키

테이블의 특정 레코드를 읽거나 변경하려면 레코드끼리 구분할 수 있는 고유의 키(식별자)가 필요합니다.

키는 반드시 값이 있어야하며, 구분을 위해 고유값을 가져야 합니다.
조건을 만족하는 필드를 후보키(Candidate Key)라고 하며, 한 테이블에 여러 후보키가 있습니다.

후보키 중 대표하는 키를 골라 기본키(Primary Key)로 선정합니다.

🔹 기본키의 특징 : NULL 금지, 중복 방지, 인덱스 생성

🔸기본키 만족 조건

• 대표성 : 레코드를 상징하는 값
• 자주 참조하는 속성 : 기본키에는 자동으로 인덱스가 생성되기에 검색효율이 높아 자주 참조하는 속성에 사용하는 것이 좋음
• 가급적 짧은 속성 : 테이블 간의 연결고리가 되므로 비교 속도 빨라야함

▪ 기본키 설정 방식
① 칼럼 제약
[CONSTRAINT 이름] PRIMARY KEY

② 테이블 제약
[CONSTRAINT 이름] PRIMARY KEY (대상 필드)

CREATE TABLE 테이블 
(
  name CHAR(10) PRIMARY KEY -- 필드 제약
  ...
  PRIMARY KEY (name) USING BTREE -- 테이블 제약
)

-- 예시
CREATE TABLE tCityKey (
  name CHAR(10),
  area INT NULL,
  popu INT NULL,
  metro CHAR(1) NOT NULL,
  region CHAR(6) NOT NULL,
  CONSTRAINT PK_tCityKey_NAME PRIMARY KEY(name)
)

(2) 복합키

하나의 필드만으로 레코드를 특정하기 어려운 경우, 두 개 이상의 키를 묶어 기본키로 지정하는 것을 복합키라고 합니다.

CREATE TABLE tCityCompoKey (
  name CHAR(10) NOT NULL,
  region CHAR(6) NOT NULL,
  area INT NULL,
  popu INT NULL,
  metro CHAR(1) NOT NULL,
  CONSTRAINT PK_tCity_name_region PRIMARY KEY (name, region)
)
-- name, region 복합키

✔ 복합키의 경우, 각각의 데이터 값은 중복이 가능하지만 두 데이터의 조합이 중복되면 입력이 불가능합니다.

(3) 유니크(UNIQUE)

필드의 중복 값을 방지하여 모든 필드가 고유한 값을 가지도록 제한합니다.
기본키 제약과 유사하지만 차이점이 있습니다.

📌 기본키와 유니크(UNIQUE)의 차이점

• 기본 키는 NULL을 허용하지 않지만 UNIQUE는 딱 하나의 NULL만 존재할 수 있습니다.
• UNIQUE와 NOT NULL을 동시에 지정하면 기본키와 유사해집니다.
• 기본키는 자동으로 인덱스를 생성하여 레코드의 정렬 순서를 결정하지만 유니크는 지정하지 않습니다.

▪ UNIQUE 설정 방식

CREATE TABLE tCityUnique (
  name CHAR(10) PRIMARY KEY,
  area INT NULL,
  popu INT UNIQUE NULL,  -- 컬럼제약 방식
  metro CHAR(1) NOT NULL,
  region CHAR(6) NOT NULL

  CONSTRAUNT Unique_tCityUnique UNIQUE(name, popu) -- 테이블제약 방식
)

5) 시퀀스

(1) 일련번호 필드

기본키는 레코드의 유일성을 보장할 뿐만 아니라 관계를 형성하는 중요한 역할로 테이블마다 기본키를 지정하는 것이 기본이지만 의무적인 것은 아닙니다.

기본키로 사용할 필드가 없는 경우, 이후 관리를 위해 일련번호를 사용할 수 있습니다.
(오라클 - 시퀀스, 마리아DB - AUTO_INCREMENT)

▪ 필드 선언

CREATE TABLE tSale (
  saleno INT AUTO_INCREMENT PRIMARY KEY,
  customer CHAR(10),
  product CHAR(30)
)

✔ 필드를 선언한 데이터가 자동증가 숫자가 매겨짐
✔ 기존 데이터 삭제 후 추가 시에도 일련번호가 재사용되는 것은 아님 (일련번호를 지정해서 입력은 가능)

3. 참조 무결성

(1) 참조 관계

테이블 간 연결관계를 말하며, 서로 관련 있는 두 테이블 간의 데이터가 연결되지 않는 데이터가 있는 것은 참조 무결성이 깨진 것입니다.

(2) 외래키 제약

어떤 필드가 외래키인지, 어떤 테이블의 무슨 키를 참조하는지를 지정하는 것입니다.

외래키로 지정 되어 있을 경우 부모 키에 해당하는 내용을 실행 후 자식 키에 대한 내용이 실행됩니다.
외래키 제약은 참조 무결성을 지키는 강력한 수단입니다.

(3) 연계 참조 무결성 제약

외래키 제약이 강하게 들어가 불편한 경우 관련 작업을 자동화하여 한번에 처리할 수 있는 연계 참조 무결성 제약이 추가됩니다.

ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}
ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

✔ NO ACTION : 아무것도 하지 않고 실패하도록 두는 것(기본값)
✔ CASCADE : 참조되는 키와 연결되어 있는 외래키를 자동으로 삭제하거나 변경
✔ SET NULL/SET DEFAULT : 외래키를 NULL 이나 기본값으로 변경 (외래키가 NULL 허용이거나 기본 값이 지정되어있는 경우)

4. 정규화

정규화는 테이블을 재구성하여 논리적 구조를 개선하는 절차를 말합니다.
정규화를 통해 불필요하게 중복되는 데이터를 제거하여 저장 공간을 절약하고 전체적인 성능을 향상 시킬 수 있습니다.

(1) 제 1정규화

필드의 데이터를 원자화 하는 방법으로 필드에는 단일 값을 저장하고 필드끼리는 독립적이여한다는 절차를 말합니다.

✔ 여러 개의 값을 한 필드에 모아서 저장하거나 유사 필드가 있다면 다른 테이블로 나누는 것이 좋음

• 예시

이름	취미
홍길동	축구, 배드민턴, 야구

예를 들어, 위의 경우 '취미' 필드의 값이 여러 개가 들어가 있기 때문에 제 1정규화를 수행하여 필드에 1개의 값만 들어가도록 합니다.

(2) 제 2정규화

일단 필드를 모두 기본키에 종속시키는 것을 말합니다.
종속되지 않은 필드에 대해서는 따로 테이블을 생성합니다.

(3) 제 3정규화

일반 필드 끼리는 독립적이여야 한다는 규칙으로 기본키가 아닌 일반 필드끼리는 서로 종속되지 않도록 하는 것을 말합니다.

(4) 역정규화

정규화를 너무 과하게 하여 조인을 많이 하게 될 경우 성능 저하가 발생합니다. 이럴 때 의도적으로 데이터를 중복시켜 속도 향상을 하는 것을 역정규화하고 합니다.