[SQL] 무결성과 제약조건

무결성(Integrity)

• 데이터에 결함이 없음
• 정확성, 일관성, 신뢰성을 보장하는 개념

 

무결성의 종류

1. 개체 무결성

• 모든 테이블이 고유한 행을 가져야 한다는 원칙
• 기본 키(Primary Key)를 통해 구현
• 기본 키로 설정된 컬럼은 비워질 수 없고, 중복된 값을 가질 수 없다.

2. 참조 무결성

• 테이블 간의 관계를 정의할 때 한 테이블이 다른 테이블의 값을 참조하는 경우 참조 값이 반드시 존재해야 함을 뜻함
• 외래 키(Foreign Key)를 통해 구현

3. 도메인 무결성

• 테이블의 각 필드(열)가 정의된 도메인에 맞는 값을 가져야 함을 뜻한다.
• 데이터 타입, 포맷, 범위 등이 적절하게 유지
• 도메인: 데이터타입, 길이, 가능한 값의 범위

4. 사용자 정의 무결성

• 특정 애플리케이션의 규칙에 따라 정의
• 구현하고자 하는 비즈니스 규칙이나 애플리케이션의 특정 요구사항을 반영

 

 

 

 

제약조건

• 데이터의 무결성을 지키기 위해 제한하는 조건
• 데이터의 정확성, 일관성, 신뢰성, 무결성을 유지하기 위해 DB 시스템에 의해 강제되는 규칙

 

사용 목적

• 데이터 무결성 보장
• 오류 방지
• 관계 유지 - 테이블 간에 정의된 관계가 지속적으로 유지
• 응용 프로그램 수준에서의 복잡성 감소

 

제약조건의 종류

1. PK, Primary Key(기본 키)

• 테이블의 각 행을 고유하게 식별하는 열
• 테이블의 레코드(행)를 고유하게 구분할 수 있는 식별자 역할
  • 고유성: 중복 될 수 없다
  • Not null: null 값이 될 수 없다, 반드시 유효한 데이터를 포함
  • 유일성 제약: 하나의 테이블 당 하나의 기본 키만 지정 가능
• 1) 컬럼명 데이터타입 Primary Key(제약조건)

create table `Students` (
	student_id int primary key,
    name varchar(100),
    major varchar(100)
);

 

• 2) 제일 마지막 부분에 제약조건 작성

create table `Students` (
	student_id int,
	name varchar(100),
	major varchar(100),
	# 제약 조건 (설정할 컬럼명)
	primary key (student_id)
);

 

• 제약 조건에 대한 수정 (alter)
• 1) 제약조건 삭제

alter table `Students`
drop primary key;

cf) 기본 키 제약 조건 삭제 시 not null에 대한 조건은 사라지지 않음

• 2) 제약조건 추가

alter table `Students`
add primary key (student_id);

 

2. FK Foreign Key(외래 키)

• 두 테이블 사이의 관계를 연결, 데이터의 무결성을 유지
• 외래 키가 설정된 열은 반드시 다른 테이블의 기본 키와 연결
• 기본 테이블: 기본 키가 있는 테이블
• 참조 테이블: 외래 키가 있는 테이블

create table `members` (
	member_id int primary key,
    name varchar(100)
);

create table `orders` (
	order_id int primary key,
    order_date date,
    member_id int,
    # 외래 키 지정 방식
    # foreign key (참조컬럼) references 기본테이블(기본컬럼)
    FOREIGN KEY (member_id) references `members`(member_id)
);

-- 유효하지 않은 고객 참조 시도 (오류)
-- INSERT INTO `orders`
-- values (1, '2024-09-24', 123); # Error: 참조 무결성 위반

-- 고객 데이터 입력
insert into `members`
value (1, '황상기');

-- 유효한 고객 참조 시도
insert into `orders`
value (1, '2024-09-23', 1);

• 외래 키 제약조건 삭제 & 추가
  • 외래 키 제약조건 삭제 시 주의점 → 해당 데이터를 참조하는 데이터가 있을 경우 삭제할 수 없음

# 외래 키 제약 조건 이름 확인
select constraint_name
from information_schema.key_column_usage
where table_name = 'orders' and column_name = 'member_id';

# >> orders_ibfk_1

alter table `orders`
drop FOREIGN KEY `orders_ibfk_1`;
# >> 외래 키 제약조건 삭제 시 제약조건의 이름을 검색하여 작성

# 외래 키 제약조건 재추가
alter table `orders`
add constraint
	FOREIGN KEY (member_id)
    REFERENCES members(member_id);

 

3. UNIQUE

• 특정 열에 대해 모든 값이 고유해야 함을 보장
• 한 테이블 내에서 여러 개 지정 가능 (각각 다른 컬럼에 독립적으로 적용)
• Null값 허용

create table `users` (
	user_id int primary key,
    user_name varchar(100),
    user_email varchar(100) unique
);

insert into `users`
values
	(1, '박영준', 'qwe123'),
	(2, '박영준', 'qwe124');

cf) PK와의 차이

• 여러 컬럼에 사용 가능 & Null값 허용

 

4. CHECK 제약조건

• 입력되는 데이터를 점검하는 기능
• 테이블의 열에 대해 특정 조건을 설정, 조건을 만족하지 않는 경우 입력을 막음

create table employees (
	id int primary key,
    name varchar(100),
    age int,
    -- check 제약 조건 사용 방법
    -- check (조건)
    # 조건식의 경우 다양한 연산자 사용
    check (age >= 20)
);

insert into `employees`
values (1, '이기석', 28);

insert into `employees`
values (2, '홍동현', 18);	-- 삽입 불가

insert into `employees`
values (3, null, 30);

 

5. NOT NULL 제약조건

• 특정 열에 Null 값을 허용하지 않는다는 것을 의미 > 비워져있지 않도록 설정

create table `contacts` (
	id int primary key, -- PK값은 not null을 지정하지 않아도 자동 지정!
    name varchar(100) not null,
    email varchar(100) not null    
);

insert into `contacts`
values (1, '성찬영', 'qwe123');

-- insert into `contacts`
-- values (2, null, 'qwe123'); # not null 오류

 

6. DEFAULT 제약조건

• 테이블의 열에 값이 명시적으로 제공되지 않는 경우 사용되는 기본값
• 선택적인 필드에 대해 데이터 입력을 단순화하여 데이터의 무결성을 유지

create table `inquiry` (
	order_id int primary key,
    product_name varchar(100),
    -- default 제약 조건 설정 방법
    -- 컬럼명 데이터타입 default 기본값
    quntity int default 1 -- 기본 수량이 1
);

insert into `inquiry`
values (1, '노트북', 3);

insert into `inquiry` (order_id, product_name)
values (2, '스마트폰');

select * from `inquiry`;