MariaDB : 데이터 무결성을 위한 제약 조건

데이터 무결성은 데이터의 정확성, 일관성 및 신뢰성을 보장하는 원칙이다. 데이터의 무결성이 떨어지게 되면 데이터 오류, 중복 데이터, 잘못된 참조 등이 발생할 수 있다. 즉 데이터 무결성을 위한 제약 조건은 데이터베이스의 정확성, 일관성을 보장하기 위해 저장, 삭제 수정 등을 제한하는 조건이다. 데이터 무결성 제약 유형에는 개체무결성, 참조무결성, 범위무결성으로 나뉜다.

• 개체무결성
  개체 무결성은 모든 테이블이 기본키(primary key)를 가져야하며, 기본키로 선택된 열은 고유하고 NULL 값을 허용하지 않아야 한다. 이는 각 데이터가 유일하게 식별될 수 있도록 보장하여 데이터를 정확성을 유지한다. 
  
  
• 참조무결성
  참조 무결성은 모든 외래 키 값은 데이터베이스의 특정 테이블의 기본 키 값을 참조해야 한다. 데이터 간의 관계를 유지한다. 외래 키 값의 경우에는  NULL 값을 허용한다. 
  
  
• 범위무결성
  특정 칼럼의 값이 정의된 범위 내에 있어야 한다. 데이터가 적절한 범위 안에서만 입력될 수 있도록 제한한다. 

 

데이터의 무결성을 위한 제약 조건은 아래와 같이 있다.

• NOT NULL
  특정 칼럼에 NULL 값을 허용하지 않음 
  
  
• UNIQUE
  해당 칼럼의 값에 중복된 값을 허용하지 않음  
  
  
• PRIMARY KEY
  테이블의 각 레코드를 고유하게 식별하는 칼럼, NOT NULL 및 UNIQUE 제약 조건을 갖는다.
  
  
• FOREIGN KEY
  다른 테이블의 기본 키와 연결되어 데이터 간의 관계를 유지함, 참조 무결성을 보장한다. 
  
  
• CHECK
  특정 조건을 만족하는 값만 허용
  
  
• DEFAULT
  칼럼에 값이 제공되지 않을 경우 사용할 기본값 지정

 

제약조건 확인하기

SELECT * from information_schema.table_constraints WHERE TABLE_NAME = '테이블명';

• CONSTRAINT_CATALOG
  제약 조건이 속하는 카탈로그의 이름이다. MariaDB의 경우 기본적으로 사용하는 카탈로그의 이름이 'def'로 설정되어 있기 때문에 일반적으로 'def'로 표시된다. 
  
  
• CONSTRAINT_SCHEMA
  제약 조건이 속하는 스키마(데이터베이스)의 이름이다.
  
  
• CONSTRAINT_NAME
  제약 조건의 이름이다. 특정 제약 조건을 식별할 수 있다.
  
  
• TABLE_SCHEMA
  테이블이 속하는 스키마(데이터베이스) 이름이다.
  
  
• TABLE_NAME 
  테이블 이름이다. 
  
  
• CONSTRAINT_TYPE
  제약 조건 유형을 나타낸다. PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT 유형이 있다.

---

NOT NULL 

해당 칼럼에 NULL값을 허용하지 않는다. 

# 칼럼 생성
ALTER TABLE userinfo ADD groupno VARCHAR(5); 
# 칼럼에 NULL 값이 있는지 확인 
SELECT * FROM userinfo WHERE groupno IS NULL;
# groupno를 NOT NULL 칼럼으로 변경
ALTER TABLE userinfo MODIFY COLUMN groupno VARCHAR(5) NOT NULL;
# groupno에 NULL값을 추가
INSERT INTO userinfo VALUES (33, 'NOA', 'MALE', 22, 'ARGENTINA', NULL, NULL);

 

NULL값이 아니라 groupno에 값을 추가한 다음에 시행시키면, 오류 없이 데이터가 삽입된 모습을 볼 수 있다. favorite 열의 경우에는 NULL값을 허용하기 때문에 NULL값을 삽입해도 오류가 따로 나지 않는다. 

# groupno에 값을 넣어서 추가
INSERT INTO userinfo VALUES (33, 'NOA', 'MALE', 22, 'ARGENTINA', NULL, 3);

---

UNIQUE

해당 칼럼의 값에 중복된 값을 허용하지 않는다. 아래와 같이 같은 값이 있을 경우에는 UNIQUE로 변경하려고 하면 중복 데이터가 있어서 생성이 되지 않는 모습을 볼 수 있다. 

# randomid 를 생성해서 UNIQUE값으로 변경
ALTER TABLE userinfo ADD randomid VARCHAR(12);
ALTER TABLE userinfo MODIFY COLUMN randomid VARCHAR(12) UNIQUE;
INSERT INTO userinfo VALUES (34, 'aslk120','MIEL', 'FEMALE', 38, 'USA', NULL, 2);

 

단 유의할 점은 UNIQUE 제약 조건이 적용된 칼럼에 여러 개의 NULL 값을 입력하는 것이 가능하다. 따라서 NOT NULL을 같이 사용하는 경우가 많다. 

INSERT INTO userinfo VALUES (35, NULL, 'COA', 'MALE', 22, 'KOREA', NULL, 3);

# UNIQUE와 NOT NULL을 함께 사용된다. 
ALTER TABLE userinfo MODIFY COLUMN randomid VARCHAR(12) UNIQUE NOT NULL;

---

PRIMARY KEY 

테이블의 각 레코드를 고유하게 식별하는 칼럼(인덱스 역할)으로 UNIQUE 와 NOT NULL 제약 조건을 모두 가지고 있어야 한다. PRIMARY KEY의 경우에는 실물로 존재하는 값을 사용하기 보다는 시스템에서 부여하는 임의의 난수 코드를 사용하는 것이 좋다. 외부의 변화에 의해서 변경되지 않으며, 다른 값과 겹칠 일이 없기 때문에 데이터의 무결성을 유지하는데 효과적이다.

• CONSTRAINT PRIMARY KEY(인덱스 역할을 할 칼럼명);

CREATE TABLE usergroup(
		groupno INT(10), 
		groupname VARCHAR(10),
		#모든 것을 정의한 후에 기본키 설정
		CONSTRAINT PRIMARY KEY(groupno)
	)

 

groupno 칼럼에 PRIMARY KEY가 설정된 모습을 볼 수 있다. PRIMARY KEY가 설정되면 해당 칼럼은 NULL 값을 허용하지 않으며, 동일한 값을 작성할 수 없다. 

 

AUTO_INCREMENT

기본값으로 AUTO_INCREMENT를 사용할 경우 숫자를 1부터 차례대로 증가시킨다. 인덱스로 사용할 수 있지만, 데이터베이스의 크기가 커질 경우에는 다른 데이터베이스의 번호와 겹치는 경우가 생기기 때문에 ID의 고유성을 지키기 위해서 임의의 난수의 값을 사용하는 것이 좋다.

• CREATE TABLE 테이블명 (칼럼명 데이터유형 NOT NULL AUTO_INCREMENT);

---

FOREIGN KEY 

다른 테이블의 기본 키와 연결되어 데이터 간의 관계를 유지하여 참조 무결성을 보장한다. 외래키는 반드시 참조하는 테이블의 기본키(primary key) 또는 UNIQUE 제약 조건이 설정된 칼럼을 가리켜야 한다. 외래키의 경우에는 NULL 값을 허용(약한 연결 관계)할 수도 있고, 허용하지 않을 수도(강한 연결 관계) 있다.

만약 참조하고 있는 데이터가 삭제될 때 데이터 무결성이 깨질 수 있기 때문에, 이를 위해서 CASCADE(참조되는 데이터가 삭제되거나 업데이트될 때 그에 따른 연쇄적인 작업을 자동으로 수행)등의 방법을 통해서 무결성 제약조건을 유지해야한다. 

• FOREIGN KEY (외래키를 설정할 칼럼명) REFERENCES 연결할테이블(칼럼명) ON 제약조건;

#usergroup의 groupno unique 로 변경
ALTER TABLE usergroup MODIFY COLUMN groupno INT(11) UNIQUE NOT NULL;

#userinfo의 groupno의 외래키로 usergroup(groupno)로 연결, 외래키 이름 : USER_GROUPNO_FK
ALTER TABLE userinfo 
ADD CONSTRAINT USER_GROUPNO_FK FOREIGN KEY (groupno) REFERENCES usergroup(groupno) ON DELETE CASCADE;

 

ON 제약조건 옵션

데이터베이스에서 특정 레코드의 값을 변경(UPDATE)하거나 삭제(DELETE)하는 작업이 수행될 때 어떤 동작을 수행할지 정할 수 있다.

• RESTRICT 
  해당 데이터가 외래 키로 참조되고 있는 경우 삭제나 업데이트를 허용하지 않는다.
  
  
• CASCADE
  참조되는 데이터가 삭제되거나 업데이트 될 때, 외래 키로 참조하고 있는 모든 레코드가 자동으로 삭제되거나 업데이트 된다.
• SET NULL
  참조되는 데이터가 삭제되거나 업데이트 될 때 외래 키로 참조하고 있는 레코드의 해당 값이 NULL로 설정된다. 
• NO ACTION
  기본적으로 RESTRICT와 유사하게 동작한다. 참조되는 데이터가 있을 경우 아무 동작도 하지 않으며, 트랜잭션 완료 시점에 참조 무결성을 확인한다. 

---

CHECK

특정 조건을 만족하는 값에 대한 범위를 지정해 준다. 

• CHECK(칼럼명 BETWEEN 조건1 AND 조건2);

#groupno의 범위를 1에서 5사이로 수정한다. 
ALTER TABLE userinfo
ADD CONSTRAINT USER_GROUPNO_CHECK CHECK(groupno BETWEEN 1 AND 5);

제약조건이 생성된 것을 볼 수 있다. 해당하는 범위가 아닌 값을 넣을 경우에는 다음과 같이 에러가 발생한다. 

---

DEFAULT

칼럼에 값이 제공되지 않을 경우 사용할 기본값 지정한다. 

• DEFAULT 값; 

# gender에 기본 값으로 MALE 지정
ALTER TABLE userinfo MODIFY COLUMN gender VARCHAR(10) DEFAULT 'MALE'; 

# gender 값을 생략했을 경우
INSERT INTO userinfo (id, randomid, ename, age, uloc, favorite, groupno) 
VALUES (NULL, '1b9ns2', 'DAN', 29, 'MEXICO', NULL, 2);

 

생략했을 경우 MALE로 기본값이 설정되는 것을 볼 수 있다.