[DATABASE] 제약조건(Constraint)

■ 제약조건(Constraint)이란?

- 데이터베이스에 들어있는 데이터의 무결성(정확성/일관성)을 보장하기 위해

  부정확한 자료가 데이터베이스 내에 저장되는 것을 방지하는 차원에서 걸은 제약 조건

- 모든 제약조건은 데이터사전(Dictionary)에 저장된다.

- 제약조건에 사용자 지정 이름을 달면 Constraint를 쉽게 참조할 수 있다.

  *사용자 지정 이름 생성시, 표준 객체 명명법을 따르는 것이 좋다. 

 

■ 제약조건의 종류

종류	내용	관련 무결성
not null	데이터가 없을 때(null 상태) 삽입을 허용 안 함	널 무결성
unique	도메인 내 중복값 허용 안 함	고유 무결성
check	조건에 맞는 값만 허용	도메인 무결성
default	기본값	-
primary key	기본키  *기본키로 설정된 도메인에 not null + unique + index 제약이 자동 설정	키 무결성
foreign key	외래키 *현재 입력(수정) 값의 유효성을 외부 데이블 부모키(컬럼)을 참조해서 체크	참조 무결성

★ 무결성의 종류

1. 널 무결성 : 릴레이션의 특정속성 값이 Null이 될 수 없도록 하는 규정  *릴레이션 = 테이블   2. 고유 무결성 : 릴레이션의 특정 속성에 대해서 각 튜플이 갖는 값들이 서로 달라야 한다는 규정   3. 참조 무결성 : 외래키 값은 Null이거나 참조 릴레이션의 기본키 값과 동일해야 한다는 규정 즉 릴레이션은 참조할 수 없는 외래키 값을 가질 수 없다는 규정   4. 도메인 무결성 : 특정 속성의 값이, 그 속성이 정의된 도메인에 속한 값이어야 한다는 규정   5. 키 무결성 : 하나의 테이블에는 적어도 하나의 키가 존재해야 한다는 규정

 

■ 제약조건 조회 - 딕셔너리에서 조회 

※ 유의할 점! 딕셔너리 내의 모든 정보는 대문자로 저장된다.

   따라서, where문을 통해 특정 테이블을 지정할 때는 꼭 upper('   ')를 쓰도록 하자.

 

[1] CMD에서 아래와 같이 제약조건에 관한 데이터들 확인

desc user_constraints

>> 결과

[2] 찾고자 하는 정보에 대한 명령어 입력

select owner, constraint_name, constraint_type, table_name from user_constraints

>> 결과

[3] 특정 테이블에 대한 정보만 뽑아서 보기

select owner, constraint_name, constraint_type, table_name from user_constraints
  where table_name = upper('userInfo3')

>> 결과

 

■ 제약조건 생성

[방법1] 테이블 생성 시 제약에 대한 명칭 없이 조건만 다는 방법

create table userInfo
(
    name      varchar2(100) not null,
    nickname  varchar2(100) null       
)
/

 

[방법2] 테이블 생성 시 제약에 대한 명칭과 함께 조건을 다는 방법

create table userInfo2
(
    name      varchar2(100) not null,
    id        varchar2(100) not null unique,
    password  varchar2(100) not null,
    
    constraint unique_userInfo2_password unique(id)
)
/

 

[방법3] 테이블 생성 후 제약에 대한 명칭과 함께 조건을 다는 방법  ** 가장 많이 사용되는 코드이다.

create table userInfo3
(
    name      varchar2(100) not null,
    id        varchar2(100) not null unique,
    password  varchar2(100) not null
)
/

alter table userInfo3 
  add constraint unique_userInfo3_password unique(password);

방법2,3과 같이 제약조건에 사용자지정 명칭을 다는 경우

이클립스 안에서 에러 메세가 나타날 때,사용자 지정 명칭을 확인할 수 있다.

 

[예시] 괄호 안에,   계정.사용자지정명칭   양식으로 나타난다.

※ 제약 조건의 명칭은 모두 대문자로 저장된다.

 

■ 제약조건 삭제

alter table 테이블명 drop constraint 제약조건명

 

  NOT NULL & UNIQUE  

create table userInfo3
(
    name      varchar2(100) not null,
    id        varchar2(100) not null unique,
    password  varchar2(100) not null
)

alter table userInfo3
  add constraint unique_userInfo3_password unique(password);
  
insert into userInfo3 values('홍길동','hong123','1234'); -- OK
insert into userInfo3(name) values('나길동'); -- 에러! 데이터 미삽입 : 널 무결성 위배 
                                            --ORA-01400: NULL을 ("TEST1"."USERINFO3"."ID") 안에 삽입할 수 없습니다
insert into userInfo3 values('다길동','hong123','2345'); -- 에러! 중복값 추가 : 고유 무결성 위배
                                                       -- ORA-00001: 무결성 제약 조건(TEST1.SYS_C004094)에 위배됩니다

위 코드를 이클립스에서 하나씩 [ALT]+[X]해서 실행해보면 먼저, 아래와 같이 에러 메세지가 나타난다. 

ORA-01400: NULL을 ("TEST1"."USERINFO3"."ID") 안에 삽입할 수 없습니다

그 다음으로 나타나는 에러 메세지는 아래와 같은데, unique로 id에 중복을 불허했는데 중복된 id를 넣어버렸다는 의미

 

  CHECK  

- 문법

alter table 테이블명
  add constraint 제약조건명 check(조건절)

- 실제 코드

alter table userInfo3
  add constraint check_userInfo3_mat check(mat between 0 and 100)

 

  DEFAULT  

- 문법

alter table 테이블명 
  modify 칼럼헤딩 타입 default 'UNKNOWN'

- 실제코드

alter table userInfo3 
  modify name varchar2(100) default 'UNKNOWN'

 

  Primary Key  

※ 기본키를 지정해주면 해당키 칼럼에 not null과 unique제약이 걸리며, 해당키 칼럼은 테이블의 index가 된다. 

※ 모든 테이블은 키 무결성 원칙을 지키기 위해 최소 1개의 primary key를 가지고 있다.

 

[1] 하나의 키를 지정하는 방법

- 문법

alter table 테이블명
  add constraint 제약조건명 primary key(칼럼명);

- 실제코드

alter table tb5
  add constraint pk_tb5_idx primary key(idx);

[2] 두개의 키를 함께 지정하는 방법

- 문법

alter table 테이블명
  add constraint 제약조건명 primary key(칼럼명,칼럼명)

- 실제코드

alter table tb5
  add constraint pk_tb55_idx_regdate primary key(idx,regdate)

두 개를 한 번에 키로 묶은 경우에 :    둘 중 하나의 키가 다를 경우에 기본키값이 매칭되지 않은 걸로 인식    두 가지 모두가 일치해야 기본키값이 동일한 것으로 인식하여 에러가 발생한다.

 

  Foreign Key  

※ 테이블을 정규화하여 두 개의 테이블(A테이블, B테이블)로 나누었다고 가정

   -  A테이블의 XXX칼럼이 A테이블의 index(primary key)이며, B테이블에도 XXX칼럼이 있다고 할 때,

   -  B테이블의 XXX칼럼을 foreign key로 주고 A테이블의 primary key를 참조하게 한다.

- 문법

-- A테이블의 기본키
alter table A테이블
  add constraint 제약명칭 primary key(XXX칼럼명)

-- B테이블의 외래키
alter table B테이블
  add constraint 제약명칭 foreign key(XXX칼럼명) references A테이블명(XXX칼럼명);

- 실제 코드

[원본 테이블 : userInfo3]

-- 원본 테이블 ) userInfo3
create table userInfo3
(
    SID       int,           
    name      varchar2(100) not null,
    id        varchar2(100) not null unique,
    password  varchar2(100) not null,
    eng       int           check(eng between 0 and 100),
    mat       int           check(mat between 0 and 100)
)

alter table userInfo3 
  add constraint pk_userInfo3_SID primary key(SID)

insert into userInfo3 values(1001, '김길동', 'kim1234', '1234', 88, 100);
insert into userInfo3 values(1002, '나길동', 'nah1234', '2345', 78, 90);
insert into userInfo3 values(1003, '다길동', 'dah1234', '2345', 98, 50);

select * from userInfo3

>> 결과

[A테이블 : userInfoRevised1]

-- A테이블 ) userInfoRevised1
create table userInfoRevised1
(
    SID       int,           
    name      varchar2(100) not null,
    id        varchar2(100) not null unique,
    password  varchar2(100) not null
)

alter table userInfoRevised1
  add constraint pk_userInfoRevised1_SID primary key(SID)

insert into userInfoRevised1 values(1001, '김길동', 'kim1234', '1234');
insert into userInfoRevised1 values(1002, '나길동', 'nah1234', '2345');
insert into userInfoRevised1 values(1003, '다길동', 'dah1234', '2345');

select * from userInfoRevised1

>> 결과

[B테이블 : userInfoRevised2]

-- B테이블 : userInfoRevised2
create table userInfoRevised2
(
    idx       int,
    SID       int,           
    name      varchar2(100) not null,
    eng       int           check(eng between 0 and 100),
    mat       int           check(mat between 0 and 100)
)

alter table userInfoRevised2 
  add constraint pk_userInfoRevised2_idx primary key(idx)
  
alter table userInfoRevised2
  add constraint fk_userInfoRevised2_SID foreign key(SID) references userInfoRevised1(SID)

insert into userInfoRevised2 values(1, 1001, '김길동', 88, 100); -- OK
insert into userInfoRevised2 values(2, 1002, '나길동', 78, 90);  -- OK
insert into userInfoRevised2 values(3, 1005, '다길동', 98, 50);  -- 에러! 없는 SID 코드

>> 결과

 

 

[출처]

http://www.gurubee.net/lecture/1013

https://programming119.tistory.com/225

https://coding-factory.tistory.com/221

 

[참조]

https://ltk3934.tistory.com/67

https://all-record.tistory.com/151

국비지원 수업 내용 참조