SQL을 배우는 중.
2023-10-13. 금요일
TOOL: PostgreSQL
DB작업을 하다보면 잘못 된 정보를 INSERT 하거나 DELETE 할 수 있다. 이를 방지하기 위해 조건을 걸어보자
무결성(Integrity) 제약조건
무결성이란
데이터 베이스에 신뢰할 수 없는 데이터 수정을 못하도록 제약하는 조건 이다.
즉 데이터베이스의 일관성, 신뢰성을 위해 조건을 걸어 두는 것이다.
도메인 무결성(Domain Integrity)
SQL의 도메인:하나의 속성이 취할 수 있는 허가된 값의 집합 (즉, 그 속성의 타입)
SQL 구현 : CHECK
각 열에 정의된 데이터 유형에 따라 유효한 값만 포함해야한다. 이를 위해 자료형과 길이 제한을 걸어둔다.
-ex) 날짜 열에 이름 입력하기
개체 무결성(Entity Integrity)
SQL 구현 : NOT NULL, UNIQUE, PRIMARY KEY
기본 키(primary key) 필드는 NULL 값이 될 수 없으며, 각 행은 고유한 기본 키 값을 가져야 한다. 이는 특정 레코드를 식별하기 위해 사용되는 필드의 일관성을 보장하기 위함.
-ex) 내 ID도 1이고 친구 ID도 1인 경우
참조 무결성(Referential Integrity)
SQL 구현 : Foreign Key
Foreign Key는 다른 테이블의 기본 키를 참조하며, 참조 무결성은 이러한 관계가 항상 유지되도록 한다.
-ex) Orderdetail의 Order ID
제약조건
NOT NULL
결측치 입력을 방지한다.
1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS contact_info;
CREATE TABLE contact_info(
cont_id NUMERIC(3) NOT NULL
, name VARCHAR(15) NOT NULL
, tel INTEGER[] NOT NULL
, email VARCHAR
);
INSERT INTO contact_info VALUES(1);
1
2
3
-- 오류: "name" 칼럼(해당 릴레이션 "contact_info")의 null 값이 not null 제약조건을 위반했습니다.
-- SQL state: 23502
-- Detail: 실패한 자료: (1, null, null, null)
개체 무결성(Entity Integrity) - UNIQUE
고유 값을 가지게 한다.
UNIQUE(1)
하나의 UNIQUE를 걸 때
1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS contact_info;
CREATE TABLE contact_info(
cont_id NUMERIC(3) UNIQUE NOT NULL
, name VARCHAR(15)
, tel INTEGER[]
, email VARCHAR
);
INSERT INTO contact_info(cont_id) VALUES(1),(1),(1);
1
2
3
-- 오류: 중복된 키 값이 "contact_info_cont_id_key" 고유 제약 조건을 위반함
-- SQL state: 23505
-- Detail: (cont_id)=(1) 키가 이미 있습니다.
UNIQUE(2)
복수의 UNIQUE를 걸 때
1
2
3
4
5
6
7
8
9
DROP TABLE IF EXISTS contact_info;
CREATE TABLE contact_info(
cont_id NUMERIC(3) NOT NULL
, name VARCHAR(15) NOT NULL
, tel INTEGER[] NOT NULL
, email VARCHAR
, UNIQUE(cont_id,tel,email)
);
개체 무결성(Entity Integrity) - PRIMARY KEY
기본키 지정. NOT NULL + UNIQUE 와 비슷하다.
1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS contact_info;
CREATE TABLE contact_info(
cont_id SERIAL NOT NULL PRIMARY KEY
, name VARCHAR(15) NOT NULL
, tel INTEGER[] NOT NULL
, email VARCHAR
);
INSERT INTO contact_info VALUES(1),(1),(1);
참조 무결성 (Referential Integrity)(FOREIGN KEY)
참조 무결성 규칙
- 부모 테이블이 자식 테이블의 선행생성
- 두 테이블의 참조 열은 동일 데이터 타입
- 부모 테이블에서 참조 된 열 값만 자식 테이블에서 입력 가능
- 참조 열은 모두
개체 무결성( PRIMARY KEY 와 UNIQUE )제약조건을 따름.
- 부모 테이블 생성
1
2
3
4
5
6
CREATE TABLE subject(
subj_id NUMERIC(5) NOT NULL PRIMARY KEY
, subj_name VARCHAR(10) NOT NULL
);
INSERT INTO subject VALUES(0001, '수학'), (0002, '과학'), (0003, '영어');
- 자식 테이블 생성
1
2
3
4
5
6
7
8
9
10
CREATE TABLE teacher(
teac_id NUMERIC(5) NOT NULL PRIMARY KEY
, teac_name VARCHAR(20) NOT NULL
, subj_id NUMERIC(5) REFERENCES subject
, teach_cerifi_date DATE
);
INSERT INTO teacher VALUES(00011,'정선생',00001,'2023-10-12'),(00021,'김선생',00002,'2023-10-12'),(00031,'이선생',00003,'2023-10-12');
INSERT INTO teacher VALUES(00041,'박선생',00004,'2023-10-12');
- 오류
1
2
-- 오류: "teacher" 테이블에서 자료 추가, 갱신 작업이 "teacher_subj_id_fkey" 참조키(foreign key) 제약 조건을 위배했습니다/SQL state: 23503
--Detail: (subj_id)=(4) 키가 "subject" 테이블에 없습니다.
참조 테이블의 subj_id 열에 4 데이터가 없기 때문.
- 수정
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE subject(
subj_id NUMERIC(5) NOT NULL PRIMARY KEY
, subj_name VARCHAR(10) NOT NULL
);
INSERT INTO subject VALUES(0001, '수학'), (0002, '과학'), (0003, '영어'), (0004, '국어');
CREATE TABLE teacher(
teac_id NUMERIC(5) NOT NULL PRIMARY KEY
, teac_name VARCHAR(20) NOT NULL
, subj_id NUMERIC(5) REFERENCES subject
, teach_cerifi_date DATE
);
INSERT INTO teacher VALUES(00011,'정선생',00001,'2023-10-12'),(00021,'김선생',00002,'2023-10-12'),(00031,'이선생',00003,'2023-10-12');
INSERT INTO teacher VALUES(00041,'박선생',00004,'2023-10-12');
성공!