제약 조건 (Constraints)
제약 조건은 데이터베이스에서 데이터의 무결성을 보장하기 위해 설정하는 규칙입니다.
제약 조건을 통해 테이블에 삽입되는 데이터의 형식과 일관성을 유지할 수 있으며, 잘못된 데이터 입력을 방지합니다.
| 제약 조건 | 설명 | 키워드 |
| 필수 입력 | 특정 열에 반드시 값이 있어야 함 | NOT NULL |
| 중복 방지 | 중복된 값을 허용하지 않음 | UNIQUE |
| 필수 + 중복 방지 | 고유하고 NULL 값이 없는 기본 키 | PRIMARY KEY |
| 참조 | 다른 테이블의 값을 참조하여 일관성 유지 | FOREIGN KEY |
| 값 검사 | 특정 조건을 만족하는 값만 허용 | CHECK |
| 기본값 설정 | 값을 입력하지 않으면 기본값이 자동 입력됨 | DEFAULT |
[제약조건의 기본 문법]
컬럼 단위 - 컬럼을 생성할 때 정의
CREATE TABLE 테이블명 (
컬럼명 자료형(크기) 제약조건,
컬럼명 자료형(크기) 제약조건,
컬럼명 자료형(크기)
)
테이블 단위 - 컬럼생성 완료 후 정의
CREATE TABLE 테이블명 (
컬럼명 자료형(크기),
컬럼명 자료형(크기),
컬럼명 자료형(크기),
제약조건
)
[내부 정보 스키마로 제약조건 확인하기]
MySQL/MariaDB에서 기본으로 제공되는 스키마는 information_schema, mysql, performance_schema, sys 등이 있으며, 각각 데이터베이스의 내부 정보를 관리하고 접근하는 데 사용됩니다.
| 스키마명 | 설명 | 학습 키포인트 |
| information_schema | 데이터베이스 메타데이터 조회. 테이블, 열, 제약조건, 권한 등의 구조를 파악할 때 사용 | 테이블과 열의 구조, 인덱스 조회, 제약조건 확인 |
| mysql | 사용자 계정, 권한 및 서버 설정 정보를 담고 있는 운영 스키마 | 사용자 관리, 권한 설정 방법 |
| performance_schema | 서버의 성능 데이터 수집. 쿼리 성능 분석, 리소스 사용량 모니터링 등에 유용 | 성능 분석, 대기 시간 분석, 쿼리 최적화 |
| sys | 성능 스키마를 쉽게 사용할 수 있도록 제공되는 뷰 모음. 직관적인 시스템 모니터링 | 성능 데이터 조회, 시스템 자원 사용량 모니터링 |
[information_schema.table_constraints 테이블]
information_schema라는 특별한 스키마에 있는 table_constraints 테이블에서 데이터를 조회합니다. 이 테이블은 모든 데이터베이스의 테이블에 설정된 제약조건에 대한 정보를 저장하고 있습니다.
MariaDB [sample]> desc information_schema.table_constraints;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO | | NULL | |
| CONSTRAINT_SCHEMA | varchar(64) | NO | | NULL | |
| CONSTRAINT_NAME | varchar(64) | NO | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | NULL | |
| TABLE_NAME | varchar(64) | NO | | NULL | |
| CONSTRAINT_TYPE | varchar(64) | NO | | NULL | |
+--------------------+--------------+------+-----+---------+-------+- constraint_name: 제약조건의 이름을 반환합니다. 각 제약조건마다 고유한 이름이 있습니다.
- table_schema: 제약조건이 설정된 스키마(데이터베이스) 이름을 반환합니다. 이 경우, 'sample'이라는 데이터베이스 안에 있는 제약조건들이 조회됩니다.
- table_name: 제약조건이 설정된 테이블 이름을 반환합니다.
- constraint_type: 제약조건의 유형을 반환합니다. 주로 PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK 등이 포함될 수 있습니다.
--sample 스키마에 존재하는 모든 테이블의 제약조건 목록을 조회하는 SQL 쿼리
select constraint_name, table_schema, table_name, constraint_type
from information_schema.table_constraints
where constraint_schema = 'sample';
+-----------------+--------------+------------+-----------------+
| constraint_name | table_schema | table_name | constraint_type |
+-----------------+--------------+------------+-----------------+
| PRIMARY | sample | dept | PRIMARY KEY |
| PRIMARY | sample | emp | PRIMARY KEY |
| fk_emp_dept | sample | emp | FOREIGN KEY |
+-----------------+--------------+------------+-----------------+
1. NOT NULL 제약조건
NOT NULL 제약조건은 특정 열이 반드시 값을 가져야 함을 의미.
-- 샘플 테이블
create table dept_n1 (
deptno int(2) not null,
dname varchar(14),
loc varchar(13)
);
-- 정상적으로 삽입
insert into dept_n1 (deptno, dname, loc) values (10, '개발부', '서울');
-- NULL 삽입 시도 (에러 발생)
insert into dept_n1 (deptno, dname, loc) values (NULL, '영업부', '부산');
ERROR 1048 (23000): Column 'deptno' cannot be null2. UNIQUE 제약조건
UNIQUE 제약조건은 열에 중복된 값이 입력되는 것을 방지.
-- 샘플 테이블
create table dept_u1 (
deptno int(2) unique,
dname varchar(14),
loc varchar(13)
);
-- 정상적으로 삽입
insert into dept_u1 (deptno, dname, loc) values (20, '인사부', '대전');
-- 중복된 값 삽입 시도 (에러 발생)
insert into dept_u1 (deptno, dname, loc) values (20, '재무부', '대구');
ERROR 1062 (23000): Duplicate entry '20' for key 'deptno'
첫 번째 삽입은 성공하지만, 두 번째와 세 번째 삽입에서는 UNIQUE 제약조건 위반으로 에러가 발생합니다.
3. PRIMARY KEY 제약조건
- PRIMARY KEY: 기본 키는 테이블의 각 행을 고유하게 식별하는 역할을 합니다. PRIMARY KEY로 설정된 열은 중복 값과 NULL 값을 허용하지 않습니다.
- 사용 목적: 데이터를 고유하게 식별하여 효율적인 검색과 데이터 무결성을 보장하는 데 사용됩니다.
-- 샘플 테이블
CREATE TABLE dept_p1 (
deptno INT(2) PRIMARY KEY,
dname VARCHAR(14),
loc VARCHAR(13)
);
-- 정상적으로 삽입
insert into dept_p1 (deptno, dname, loc) values (10, '개발부', '서울');
-- 중복된 PRIMARY KEY 값 삽입 시도 (에러 발생)
insert into dept_p1 (deptno, dname, loc) values (10, '영업부', '부산');
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
-- NULL 값 삽입 시도 (에러 발생)
insert into dept_p1 (deptno, dname, loc) values (NULL, '재무부', '대전');
ERROR 1048 (23000): Column 'deptno' cannot be null4. AUTO_INCREMENT 제약조건
- 자동 증가:
- PRIMARY KEY와 함께 사용:
- 초기 값 설정:
- 값이 중복되지 않음:
CREATE TABLE dept_a1 (
deptno INT(2) PRIMARY KEY AUTO_INCREMENT, -- PK와 함께 사용
dname VARCHAR(14),
loc VARCHAR(13)
);
-- 데이터 삽입 예제
INSERT INTO dept_a1 (dname, loc) VALUES ('개발부', '서울');
INSERT INTO dept_a1 (dname, loc) VALUES ('영업부', '부산');
INSERT INTO dept_a1 (dname, loc) VALUES ('인사부', '대전');
SELECT * FROM dept_a1;
+--------+-----------+--------+
| deptno | dname | loc |
+--------+-----------+--------+
| 1 | 개발부 | 서울 |
| 2 | 영업부 | 부산 |
| 3 | 인사부 | 대전 |
+--------+-----------+--------+- deptno 열에 자동으로 1부터 순차적으로 증가하는 값이 할당됩니다.
5. CHECK 제약조건
- 데이터 검증: CHECK 제약조건을 통해 특정 열의 값이 지정된 조건을 충족하는지 검증할 수 있습니다.
- 여러 조건 사용 가능: 하나의 열에 여러 CHECK 제약조건을 설정할 수도 있고, 여러 열을 대상으로 복합 조건을 설정할 수도 있습니다.
- 제약조건 위반 시 에러 발생: 조건을 충족하지 않는 데이터가 삽입되면 에러가 발생하여 데이터 무결성을 유지할 수 있습니다.
- 데이터베이스에 따라 지원이 제한적: 일부 데이터베이스에서는 CHECK 제약조건의 지원이 제한될 수 있습니다(MariaDB의 경우 일부 상황에서 제한이 있을 수 있음).
-- 샘플용 테이블 제약조건
CREATE TABLE dept_c1 (
deptno INT(2) CHECK (deptno >= 30), --제약조건
dname VARCHAR(14),
loc VARCHAR(13)
);
-- 조건을 만족하는 데이터 삽입 (성공)
INSERT INTO dept_c1 (deptno, dname, loc) VALUES (30, '개발부', '서울');
-- 조건을 만족하지 않는 데이터 삽입 (에러 발생)
INSERT INTO dept_c1 (deptno, dname, loc) VALUES (20, '영업부', '부산');
6. 복합키 제약조건
복합 키(Composite Key)는 하나의 열(column)로는 고유하게 식별할 수 없는 데이터를 두 개 이상의 열을 조합하여 고유하게 식별할 수 있도록 설정하는 키입니다. 복합 키는 여러 열을 묶어서 하나의 키처럼 사용하며, 주로 관계형 데이터베이스에서 테이블의 기본 키(Primary Key)나 외래 키(Foreign Key)로 사용됩니다.
- 고유성 보장: 단일 열로는 고유한 식별자가 될 수 없는 경우, 여러 열을 조합하여 데이터의 고유성을 보장합니다.
- 데이터 무결성 유지: 복합 키를 사용하여 특정 데이터가 고유하게 식별되도록 함으로써 데이터의 일관성과 무결성을 유지합니다.
-- 복합키 샘플 테이블
CREATE TABLE order_u1 (
pcode INT(4),
ccode INT(4),
orderdate DATE,
etc VARCHAR(20),
CONSTRAINT UNIQUE (pcode, ccode)
);- UNIQUE (pcode, ccode) 제약조건을 설정하여, pcode와 ccode 열의 조합이 고유하도록 합니다. 즉, 같은 pcode와 ccode의 조합이 테이블에 중복으로 입력될 수 없습니다.
INSERT INTO order_u1 VALUES (1000, 1000, NOW(), 'order1'); -- 성공 1
INSERT INTO order_u1 VALUES (100, 1000, NOW(), 'order2'); -- 성공 2
INSERT INTO order_u1 VALUES (1000, 100, NOW(), 'order3'); -- 성공 3
INSERT INTO order_u1 VALUES (1000, 1000, NOW(), 'order4'); -- 에러 발생 4
--첫 번째, 두 번째, 세 번째 삽입은 pcode와 ccode의 조합이 모두 서로 다르므로 삽입이 성공적
--pcode = 1000과 ccode = 1000의 조합은 이미 첫 번째 삽입된 데이터에서 존재하므로,
--이와 동일한 조합을 다시 삽입하려고 하면 중복된 값 에러가 발생합니다.
--UNIQUE (pcode, ccode) 제약조건이 설정되어 있기 때문에 중복 삽입이 제한됩니다.
6. 제약조건 수정
테이블의 제약조건을 수정할 때는 ALTER TABLE 명령어를 사용합니다. ALTER TABLE을 사용하면 기존 테이블에 제약조건을 추가하거나 제거할 수 있습니다. 하지만 이미 존재하는 제약조건을 직접 수정하는 기능은 없기 때문에, 제거한 후에 새로 추가해야 합니다.
[기본 구조]
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건_이름 제약조건_유형 (열_이름);
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건_이름; -- `FOREIGN KEY`나 `CHECK` 제약조건 삭제
ALTER TABLE 테이블명
DROP PRIMARY KEY; -- 기본 키 삭제
ALTER TABLE 테이블명
DROP INDEX 제약조건_이름; -- UNIQUE 제약조건 삭제
--제약조건_유형: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK 등의 유형.
예제
-- 연습용 테이블
CREATE TABLE dept_n (
deptno INT(2),
dname VARCHAR(14),
loc VARCHAR(13)
);
CREATE TABLE emp_n (
empno INT(4)
ename VARCHAR(10),
job VARCHAR(9),
deptno INT(2)
);
NOT NULL 제약조건 추가 및 삭제
-- 1. NOT NULL 제약조건 추가
ALTER TABLE dept_n
MODIFY loc VARCHAR(13) NOT NULL;
-- 2. NOT NULL 제약조건 삭제
ALTER TABLE dept_n
MODIFY loc VARCHAR(13) NULL;
1. PRIMARY KEY 제약조건 추가 및 삭제
ALTER TABLE dept_n
ADD CONSTRAINT pk_deptno PRIMARY KEY (deptno);
2. FOREIGN KEY 제약조건 추가 및 삭제
ALTER TABLE emp_n
ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept_n (deptno);'Backend > SQL' 카테고리의 다른 글
| [SQL] 데이터베이스 사용자 (1) | 2024.10.29 |
|---|---|
| [SQL] 뷰(VIEW) (0) | 2024.10.29 |
| [SQL] 참조키 : 기본키, 외래키 (0) | 2024.10.29 |
| [SQL] DDL(Data Definition Language) (0) | 2024.10.28 |
| [SQL]JOIN (0) | 2024.10.28 |