[ SQL ] 테이블과 제약 조건(constraint)

이전 포스팅에서 트랜잭션과 서브 쿼리에 대해서 알아보았습니다. 오늘은 테이블 생성 및 제약조건에 대해서 포스팅해보려 합니다!

 

 

■ 테이블(table)

테이블이란 데이터 베이스에서 실제 데이터가 저장되는 곳입니다. 테이블에 대한 특징은 다음과 같습니다.

 

• 데이터베이스의 기본적인 데이터 저장 단위입니다.
• 데이터 베이스를 생성 하며 n개의 테이블을 생성할 수 있습니다.
• 테이블은 사용자가 접근 가능한 모든 데이터를 보유합니다.(레코드&칼럼으로 구성)
• 시스템 내에서 독립적으로 사용되길 원하는 엔티티를 표현할 수 있습니다.
• 두 엔티티 간의 관계를 표현할 수 있습니다.

 

■ 제약 조건(constraint)

제약 조건이란 데이터의 무결성을 지키기 위해, 데이터를 입력받을 때 실행되는 검사 규칙을 의미합니다. 

이러한 제약 조건은 CREATE문으로 테이블을 생성할 때나 ALTER문으로 필드를 추가할 때도 설정할 수 있습니다. 또한 DESC 명령어를 통해서 변수별로 null이 가능한지 조회가 가능하고, GUI를 이용해 테이블의 제약 조건을 모아서 볼 수도 있습니다.

 

제약 조건은 다음과 같습니다.

 

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• DEFAULT

 

제약 조건을 알아보기 전에 확실한 이해를 위해 오라클의 데이터 타입에 대해서 먼저 알아봅시다.

 

■ 오라클의 데이터 타입

데이터 타입은 문자형,숫자형,날짜,LOB 타입이 있습니다. 하나씩 알아봅시다.

 

1 ) 문자형 데이터 타입

데이터 타입	설 명
CHAR(n)	고정길이 문자, 최대 2000byte, 디폴트값 1byte
VARCHAR2(n)	가변길이 문자, 최대 4000byte, 디폴트값 1byte
VARCHAR(n)	VARCHAR2와 동일하나 ANSI 표준형에서 쓰임
NCHAR(n)	고정길이 유니코드 문자, 최대 2000byte, 디폴트값 1byte
NVARCHAR(n)	가변길이 유니코드 문자, 최대 2000byte, 디폴트값 1byte
LONG	최대 2GB 크기의 가변길이 문자형
CLOB	대용량 텍스트 데이터 타입(최대 4GB)
NCLOB	대용량 텍스트 유니코드 데이터 타입(최대 4GB)

※ "가변길이"란 정해진 크기가 아닌 실제 데이터의 크기에 맞추어 자동으로 가변적 크기의 메모리를 사용하는 것을 의미합니다.

 

예를들어 특정칼럼에 VARCHAR2(10)이라고 정의 후 ‘hello’라는 데이터를 입력하면 해당 칼럼은 10byte까지 데이터를 입력받을 수 있지만 실제 4글자만 입력되었으므로 메모리는 4byte만 쓰게 됩니다.

 

반대로 고정 문자형 데이터타입인 CHAR(10) 하고 ‘hello’라는 데이터를 입력했다면 실제 데이터와는 상관없이 무조건 메모리상 데이터 공간을 10byte 사용합니다. 따라서 특수한 경우가 아니라면 문자형은 대부분 VARCHAR2를 사용합니다.

 

 

2 ) 숫자형 데이터 타입

데이터 타입	설 명
NUMBER(p, s)	가변숫자, p(1~38, 디폴트:38), s(-84~127, 디폴트:0), 최대 22byte
FLOAT(p)	NUMBERS의 하위타입, p(1~128, 디폴트:128), 2진수 기준 최대 22byte
BINARY_FLOAT	32비트 부동소수점 수, 최대 4byte
BINARY_DOUBLE	64비트 부동소수점 수, 최대 8byte

※ 숫자 타입은 4가지 타입이 있는데, 대부분 NUMBER형을 사용합니다.

p는 소수점을 포함한 전체 자릿수를 의미하고, s는 소수점 자릿수를 의미하며 NUMBER는, 가변숫자이므로 p와s를 입력하지 않으면 저장 데이터의 크기에 맞게 자동으로 조절됩니다.

 

 

3 ) 날짜 데이터 타입

데이터 타입	설 명
DATE	BC 4712년 1월 1일부터 9999년 12월 31일까지, 연,월,일,시,분,초 까지 입력 가능
TIMESTAMP	연,월,일,시,분,초 + 밀리초까지 입력가능

※  가장 일반적으로 쓰는 타입은 DATE 입니다.

 

 

4 ) LOB 데이터 타입

데이터 타입	설 명
CLOB	대용량 텍스트 데이터 타입(최대 4GB)
NCLOB	유니코드를 지원하는 문자형 대용량 객체
BLOB	바이너리(2진형) 대용량 객체
BFILE	대용량 바이너리 파일에 대한 위치,이름 저장

※ LOB이란 Large Object의 약자로 대용량 데이터를 저장할 수 있는 데이터 타입입니다. 

일반적으로 그래픽, 이미지, 사운드 등 비정형 데이터를 저장할 때 LOB타입을 사용합니다.

(문자형 대용량 데이터는 CLOB / NCLOB, 그래픽, 이미지, 동영상 등의 대이터는 BLOB을 주로 사용!)

 

그럼 이제 제약 조건을 테이블 생성으로 통해서 알아봅시다.

 

■ 테이블(table) 구조

 

테이블 생성 구조는 다음과 같습니다.

1) 테이블 생성 : create table 테이블명 ( );
2) 테이블명 수정하기: rename A to B
3) 테이블에 데이터 입력 : insert into 테이블명 (컬럼,...)
values(값,...);
4) 테이블에 컬럼 추가 : alter table 테이블명 add (컬럼 데이터타입)
5) 컬럼 수정 : alter table 테이블명 modify (컬럼 데이터타입)
6) 컬럼의 이름 수정 : alter table 테이블명 rename column 컬럼1
to 컬럼2
7) 컬럼의 삭제 : alter table 테이블명 drop column 컬럼명
8) 테이블의 데이터 조작 : insert, update, delete

-- 새로운 데이터 입력
insert into 테이블명(컬럼1,컬럼2,....) values(값1,값2,...)

-- 데이터 삭제
delete from 테이블명 where 조건
--(주의: where절 없이 쓰면 모든 데이터가 삭제됨)

 

그럼 예제 테이블을 통해서 알아봅시다.

아래 테이블은 제약 조건을 반영한 테이블입니다.

 

<예제 1>

create  table c_emp(
id number(5) constraint c_emp_id_pk primary key, --primary ket : 기본 키
name varchar2(25) constraint c_emp_name_nn not null,--not null : 컬럼을 필수 필드화 시킬 때 사용함.
salary number(7,2),
phone varchar2(15) constraint c_emp_phone_ck check(phone like '3429-%'), -- check : 컬럼의 값을 어떤 특정 범위로 제한함.
dept_id number(7) constraint c_emp_dept_id_fk references dept(deptno)
);

위 예제에서 사용한 제약 조건은 primary key, not null, check 조건으로 각 특징은 다음과 같습니다.

 

• primary key : 기본키(UNIQUE + NOT NULL)는 그 데이터 행을 대표하는 칼럼으로서의 역할을 수행하여 다른 테이블에서 외래키들이 참조할 수 있는 키로서의 자격을 갖고 있습니다. 따라서 기본키를 정의하면 자동으로 인덱스를 생성하며, 그 별칭은 기본 키 제약 조건의 별칭과 같습니다.

 

• not null :  not null 제약 조건을 설정하면 해당 칼럼에는 꼭 데이터를 입력해야 합니다. 정리하면 칼럼을 필수로 필드화 시킬 때 사용하는 게 not null입니다.

 

• check : 칼럼의 값을 특정 범위로 제한합니다.

 

제약 조건은 테이블을 생성 후에 추가를 할 수 있습니다.

예제를 통해서 알아봅시다.

 

<예제 2>

--제약조건 추가
alter table c_emp add constraint c_emp_name_un unique(name); -- UNIQUE : 인덱스 생성

--(주의 : not null 제약 조건은 add가 아닌 modify를 써야함)
alter table c_emp modify (name varchar2(25)constraint c_emp_name_nn not null);

 

• unique : 데이터의 유일성을 보장하고, 자동으로 인덱스가 생성됩니다. 즉, 유일성을 보장하기 때문에 중복되는 데이터는 존재할 수 없습니다.

 

※ 제약 조건 추가 시 not null 제약 조건은 add가 아닌 modify를 써야 합니다.

 

<TIP>

-- 제약조건 활성화(enable)/비활성화(disable)
alter table c_emp disable constraint c_emp_name_nn;
alter table c_emp enable constraint c_emp_name_nn;
--비활성화 했던 것을 다시 활성화시킬 때 기존에 입력된 중복자료를 삭제해야 함

 

이번에는 FOREING KET를 지정해 보는 예제 코드입니다.

FOREIGN KEY는 외래키라고 하며, 기본키를 참조하는 칼럼 or 칼럼들의 집합입니다. 

특징은 다음과 같습니다.

 

• 외래키를 가지는 칼럼의 데이터 형은 외래키가 참조하는 기본키의 칼럼과 데이터 형이 일치해야 합니다.
• 외래키에 의해 참조되고 있는 기본키는 삭제가 불가능합니다.
• ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는 그 기본키가 삭제될 때 같이 삭제됩니다.

 

<예제 3>

--foreign key 추가
alter table c_emp add constraint c_emp_deptno_fk foreign key(deptno) reference dept(deptno);

 

결과 표출을 위해 테이블 안에 값을 저장합니다.

insert into c_emp values(c_emp_seq.nextval, 'kim',300,'010-2222-3333',10);
insert into c_emp values((select nvl(max(id)+1,1)from c_emp), 'kim12',500,'010-1111-3333',10);
insert into c_emp values((select nvl(max(id)+1,1) from c_emp), 'kim22',270,'010-7778-5533',30);

---

마치며 

 

오늘은 테이블과 제약 조건에 대해서 알아봤습니다. 

다음 포스팅에 뵙겠습니다.