SQL - ⑩ 데이터 무결성과 제약조건 (DMCCONET)

hoegon kim·2022년 10월 24일

SQL - DMCCONET

SQL-PLUS

목록 보기

11/17

데이터 무결성과 제약조건

학습내용

1.제약 조건 이란?
2.제약 조건 변경하기
3.제약 조건 활성화 및 비활성화

학습목표

제약 조건을 말하고 그 종류와 각각의 역할을 설명할 수 있습니다.
제약 조건을 생성하고 유지 및 관리 할 수 있습니다.

1.제약조건

제약 조건(Data Integrity Rule)이란 테이블에 유효하지 않은 (부적절한) 데이터가 입력되는 것을 방지하기 위해서 테이블 생성 시 각 칼럼에 대해 정의하는 규칙을 말합니다.
oracle의 제약조건의 종류

제약 조건은 일반적으로 테이블을 생성 될때 생성되며, 이 후 ALTER TABLE을 이용하여 추가 및 삭제가 가능합니다.

제약 조건은 칼럼 레벨과 테이블 레벨로 정의할 수 있습니다.
칼럼 레벨에서 정의하는 경우 하나의 칼럼에 대해서 제약 조건을 지정하며 모든 제약 조건을 정의할 수 있습니다.
테이블 레벨에서 제약 조건을 정의하는 경우 칼럼 정의와는 별도로 정의하여 NOT NULL을 제외한 모든 제약 조건을 정의할 수 있습니다.

1-1 NOT NULL 제약조건

NOT NULL 제약 조건을 사용하면 해당 칼럼이 NULL 값을 가질 수 없게 됩니다. 반드시 값이 있어야 하는 칼럼에 지정합니다.

NOT NULL 제약 조건은 칼럼 레벨로만 정의할 수 있습니다. 테이블에
NOT NULL 제약 조건을 지정하려면 데이터 타입을 기술한 후 연이어 NOT
NULL 기술하면 됩니다.

테이블에 NULL 데이터를 추가하여 봅시다.

1-2 유일한 값만 허용하는 UNIQUE 제약 조건

UNIQUE 제약 조건은 특정 칼럼에 모든 값이 고유하게 유지되도록 하는 고유키를 생성합니다. 즉 칼럼에 중복된 값을 가질 수 없도록 합니다.
oracle은 고유키에 의해 인덱스를 암시적으로 생성합니다.

[예제] 제약 조건의 이름을 지정하여 봅시다.

데이터 제약 조건을 보기 위해 USER_CONSTRAINTS 데이터 사전을 사용합니다.
USER_CONS_COLUMNS 데이터 사전은 USER_CONSTRAINTS 데이터 사전으로 알 수 없는 제약 조건이 설정된 칼럼 이름을 알려줍니다.
[예제] 제약 조건을 확인해봅시다 .

1-3 데이터 구분을 위한 PRIMARY KEY 제약 조건

PRIMARY KEY 제약 조건은 테이블에 기본 키를 생성합니다.
제약 조건이 지정된 칼럼은 중복된 데이터를 저장하지 못하는 고유성이 부여됩니다.
기본 키에 속하는 칼럼은 NULL 값을 가질 수 없습니다.
고유 키 제약 조건과 NOT NULL 제약 조건을 결합한 개념으로 테이블의 모든 로우를 구별하기 위한 식별자를 정의하게 됩니다.

1-4 참조 무결성을 위한 FOREIGN KEY 제약조건

FOREIGN KEY 테이블에 외래 키 제약 조건을 생성합니다.
참조 무결성은 테이블과 테이블 사이의 주종 관계 설정을 위한 제약 조건입니다.

[예제] FOREIGN KEY 제약 조건을 지정하여 봅시다

1-5 값의 범위를 설정하는 CHECK 제약 조건

CHECK 제약 조건은 칼럼에서 허용 가능한 데이터의 범위나 조건을 정의합니다.
입력되는 값을 체크하여 설정된 값 이외의 값이 들어오면 오류 메세지와 함께 명령이 수행되지 못하게 된다.
CHECK 제약 조선의 수에는 제한이 없지만, CYRRVAL, NEXTVAL, ROWNUM 같은 의사 칼럼이나 SYSDATE, USER와 같은 함수에는 사용할 수 없습니다.
[예제] CHECK 제약 조건을 지정하여 봅시다.

1-6 DEFAULT 제약조건

어떤 값도 입력하지 않았을 때 DEFAULT 제약의 값이 입력됩니다.
[예제] DEFAULT 제약 조건을 지정하여 봅시다.

2. 제약 조건 변경

2-1 제약 조건 추가

ALTER … ADD CONSTRAINT 문을 사용하면 기존 테이블에 대해서도 제약 조건을 추가할 수 있습니다.

NULL 무결성 제약 조건은 ALTER … ADD 명령문으로 추가하지 못합니다.

2-1 제약 조건 제거

ALTER TABLE … DROP CONSTRAINT 명령문을 사용하면 기존 테이블에 대해서도 제약 조건을 제거 할 수 있습니다.

[예제] 제약 조건을 제거하여 봅시다.

3.제약 조건 활성화 및 비활성화

ALTER TABLE 문에 DISABLE 절을 사용하면 제약 조건을 삭제하지 않고 일시적으로 비활성화 할 수 있습니다.

[예제] 제약 조건 비활성화

ALTER TABLE 문에 ENABLE 절을 사용하면 제약 조건을 일시적으로 활성화 할 수 있습니다.

[예제] 제약 조건 활성화

실습

약간 수정한 부분있음

--1. 데이터 지우기

drop table sawon;
purge recyclebin;

drop TABLE dept;
purge recyclebin;

drop TABLE gogek;
purge recyclebin;

--2. DEDT 생성
CREATE TABLE dept(
deptno number(3),
dname varchar2(10),
loc varchar2(10),
constraint dept_deptno_PK PRIMARY KEY(deptno),
constraint dept_dname_uk unique(dname)
);

--2-1) DEPT 확인
select owner, constraint_name, table_name from user_constraints
where table_name = 'DEPT';

--3. sawon 생성
CREATE TABLE sawon(
sabun number(3),
saname varchar2(10) constraint sawon_saname_NN not null,
deptno number(3),
sajob varchar2(10),
sapav number(10),
sahire date default sysdate,
sasex varchar2(4),
samgr number(3),
constraint sawon_sabun_PK PRIMARY KEY(sabun),
constraint sawon_deptno_FK FOREIGN KEY(deptno) REFERENCES dept(deptno),
constraint sawon_sasex_ck CHECK(sasex in('남자','여자')),
constraint sawon_samgr_FK FOREIGN KEY(samgr) REFERENCES sawon(sabun)
);

--3-1 SAWON 확인
select owner, constraint_name, table_name from user_constraints
where table_name = 'SAWON';

--4. gogek 생성
CREATE TABLE gogek(
gobun number(3),
goname varchar2(10),
gotel varchar2(20),
gojumin varchar2(14),
godam number(3),
constraint gogek_gobun_PK PRIMARY KEY(gobun),
constraint gogek_godam_FK FOREIGN KEY(godam) REFERENCES sawon(sabun)
);

--4-1 gogek 확인
select owner, constraint_name, table_name from user_constraints
where table_name = 'GOGEK';