[ORACLE] 테이블 생성(CREATE TABLE)과 제약 조건(CONSTRAINT)

 

---

테이블 생성(CREATE TABLE)과 제약 조건(CONSTRAINT)

---

 

 

1. 오라클의 데이터타입(DataType)

---

테이블을 형성하기 위해서는 필드의 데이터타입을 지정해줘야 한다. 우리가 자주 사용하게 될 데이터 타입을 간단히 표로 알아보자.

표기	데이터타입	특징
NUMBER(n)	정수형 타입	n : 표시되는 숫자의 자리수 NUMBER(2) : -99 ~ 99
NUMBER(n, m)	실수형 타입	소수점을 포함한 전체 자리수, 소수점 아래 자리수 NUMBER(6,2) : 소주점 포함 전체 6자리, 소수점 2자리
VARCHAR2(n)	가변형 문자	지정한 숫자만큼 최대 글자수 지정하되 용량은 실제 글자수만큼 할당 VARCHAR2(10) : "abcd" 저장 시 4글자만큼만 용량 소비
CHAR(n)	고정형 문자	지정한 숫자만큼 용량 할당 CHAR(10) : "abcd" 저장 시 "abcd      " 저장
NVARCHAR(n)	가변형 유니코드 문자	다국어 입력을 위한 가변형 자료형 가변형 최대 4000 Byte
NCHAR(n)	고정형 유니코드 문자	다국어 입력을 위한 고정형 자료형 고정형 최대 2000 Byte
DATE	날짜 데이터	년월일시분초까지 표현된 날짜
TIMESTAMP	날짜 데이터	년월일시분초+밀리초까지 표현된 날짜

 

대부분의 데이터 타입은 SQL에서 활용될 때 뒤에 최대 길이를 나타내는 (괄호)가 붙는다. 예를 들어 VARCHAR2(10)과 같이 표현하면 최대 10자리를 갖을 수 있다는 뜻이다. NUMBER(4)의 경우도 마찬가지로 4자리수(0~9999)까지 입력 받을 수 있다는 뜻이다. byte수라고 혼동해서는 안 된다.

 

이렇듯 보통은 데이터 형식과 용량(크기)를 결정하는데, 날짜 데이터를 나타내는 DATE 데이터 형식은 별도로 크기를 지정하지 않아도 된다.

 

 

아래는 요새 잘 사용하지 않는 자료형이다.

표기	데이터타입	안쓰는 이유
LOB	대용량 데이터 저장( Large Object )	대용량 파일을 별도의 서버에 저장하고, 경로와 이름을 데이터베이스로 관리하는 형식
VARCHAR	가변형 문자	VARCHAR2 형식은 같으나 VARCHAR2의 용량이 업그레이드 됨
LONG	최대 2GB 가변 길이 문자형
FLOAT	2진수 기준 22바이트	NUMBER 하위타입
BINARY_FLOAT	32비트 부동 소수점
BINARY_DOUBLE	64비트 부동 소수점

 

 

 

2. 테이블의 생성 (CREATE TABLE)

---

오라클에서의 테이블 생성 양식은 다음과 같다.

CREATE TABLE 테이블이름(
    필드명1 DATATYPE [DEFAULT값 OR 제약조건 등 ],
    필드명2 DATATYPE [DEFAULT값 OR 제약조건 등 ],
    필드명3 DATATYPE [DEFAULT값 OR 제약조건 등 ],
    ...
    필드명n DATATYPE [DEFAULT값 OR 제약조건 등 ],
);

 

 

도서 대여점의 도서목록을 테이블로 생성해보자.

필드는 booknum(번호), subject(제목), makeyear(출판년도), inprice(입고가격), outprice(대여가격)으로 생성할 것이다.

create table booklist(
    booknum varchar2(5),
    subject varchar2(30) not null, -- 필드 레벨의 제약조건 표시
    makeyear number(4),
    inprice number(5) not null,
    outprice number(6) not null
);

 

제약 조건 중 하나인 NOT NULL 을 사용했다. 밑에서 다루겠지만 NULL 상태 즉, 아무것도 없는 상태를 허용하지 않아 입력을 필수로 해야한다는 뜻이다.

 

 

 

CREATE TABLE 명령의 세부 규칙을 정리하면 다음과 같다.

1. 테이블의 이름은 객체를 의미할 수 있는 적합한 이름을 사용한다. (자바 변수 이름과 비슷)
2. 다른 테이블과 이름이 중복되지 않게 정한다. (같은 아이디로 로그인 했을 때)
3. 한 테이블 내에서 필드 이름이 중복되지 않게 정한다.
4. 각 필드는 " , " 로 구분하여 생성한다.
5. CREATE 를 비롯한 모든 SQL 명령은 " ; " 로 마친다.
6. 필드명 뒤에 DATATYPE은 반드시 지정하고, [] 안에 내용(제약조건)은 생략이 가능하다.
7. 예약어, 명령어 등을 테이블명과 필드명으로 쓸 수 없다.
8. 테이블 생성 시 대/소문자 구분은 하지 않는다. (기본적으로 대문자로 만들어지긴 한다.)
9. 필드(컬럼)과 필드(컬럼)의 구분은 콤마로 하되, 마지막 필드나 CONSTRAINT 를 작성한 후엔 콤마를 찍지 않는다.

 

 

 

3. 제약조건 (CONSTRAINT)

---

제약조건은 적절하지 않은 데이터를 입력하는 일을 방지한다.

 

제약 조건을 정리하면 다음과 같다.

1. 기본키 (PRIMARY KEY)
   1. 테이블에 저장된 레코드를 고유하게 식별하기 위한 키
   2. 하나의 테이블에 하나의 기본키만 정의할 수 있다
   3. 여러 필드를 조합해서 생성할 수도 있다.
   4. 중복된 값을 갖을 수 없으며 빈칸도 있을 수 없다.
   5. PRIMARY KEY = UNIQUE KEY + NOT NULL
2. UNIQUE KEY
   1. 테이블에 저장된 행 데이터값이 고유해야 함을 지정
   2. NULL은 고유키 제약 대상이 아니므로 NULL값을 가진 행 여러개가 이 제약에 위반되지는 않는다.
3. NOT NULL
   1. 비어있는 상태, 아무것도 없는 상태를 허용하지 않는다.
   2. 입력을 필수로 해주어야 한다.
4. CHECK
   1. 입력할 수 있는 값의 범위를 제한한다.
   2. CHECK 제약은 TRUE or FALSE 로 평가할 수 있는 논리식을 지정한다.
5. FOREIGN KEY
   1. 관계형 데이터베이스에서 테이블 간에 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 생성한다. (참조 무결성 제약 옵션이 생성)
6. DEFAULT
   1. 값이 입력되지 않아도 기본적으로 지정될 값을 부여한다.
   2. DEFAULT 뒤에 값을 넣어 설정한다.

 

 

예시를 통해 알아보자. 이번에는 memberlist 라는 테이블을 아래와 같이 생성했다.

create table memberlist(
    membernum varchar2(5) not null,
    membername varchar2(12) not null,
    phone varchar2(13) not null, -- NOT NULL
    birth date,
    bpoint number(6) default 0, -- DEFAULT
    constraint mlist_pk primary key(membernum) -- 기본키
);

 

위의 테이블에서 제약 조건만 살펴봤을 때

membernum, membername, phone 필드는 NULL 을 허용하지 않는다. 다시말해 입력을 필수로 받아야 한다.

bpoint 필드는 값을 입력하지 않을 경우 기본적으로 0의 값이 부여된다.

 

조금 낯설면서 설명이 필요한 기본키와 외래키는 예시를 통해 알아보겠다.

 

 

 

기본키 (PRIMARY KEY) 예시

-- CONSTRAINT 제약조건고유의이름 PRIMARY KEY(필드명)
constraint blist_pk primary key(booknum)

 

테이블의 필드 중에서 저장된 값들에 빈칸이 없고, 서로 중복되는 값도 없어서 필드값만으로 레코드가 모두 구분되는 필드 중 하나를 기본키로 지정한다. (레코드는 여러가지 데이터 타입을 가질 수 있는 복합형 구조의 데이터타입으로, 하나의 행(row)에 대응한다고 보면 된다. )

테이블의 대표 필드라고 할 수 있으며, 테이블마다 반드시 기본키를 지정해야 하는 것은 아니다. 하지만 기본키를 지정하면, 지정하지 않을 때 발생할 수 있는 오류들을 미연에 방지할 수 있어 결함없이 유지된다는 뜻으로 "개체 무결성"이라고 부른다.

 

CONSTRAINT 는 테이블 수준의 제약조건을 지정하는 키워드로 위와 같은 문법을 통해 기본키를 설정한다.

문법에 따르면 위 명령은 " PRIMARY KEY를 booknum 으로 지정하겠다. 그리고 그 제약조건의 이름은 blist_pk 로 하겠다. " 라는 뜻이다.

 

 

 

외래키(FOREIGN KEY) 예시

create table booklist(
    booknum varchar2(5),
    subject varchar2(30) not null,
    makeyear number(4),
    inprice number(5) not null,
    outprice number(6) not null,

    constraint blist_pk primary key(booknum) -- 기본키 : booknum
);

create table memberlist(
    membernum varchar2(5) not null,
    membername varchar2(12) not null,
    phone varchar2(13) not null,
    birth date,
    bpoint number(6) default 0,
    
    constraint mlist_pk primary key(membernum) -- 기본키 : membernum
);

 

위 테이블 두가지를 생성했다고 가정하자.

 

create table rentlist(
    rent_date date default sysdate, -- sysdate : 오늘 날짜를 얻어주는 키워드
    numseq number(4),
    bnum varchar2(5) not null,
    mnum varchar2(5) not null,
    discount number(4) default 0,
    
    constraint rent_pk primary key(rent_date, numseq), -- 필드 두개를 기본키로 지정
    
    -- 현재테이블의 bnum 필드는 booklist 테이블의 booknum 필드값을 참조
    constraint fk1 foreign key(bnum) references booklist(booknum),
    -- 현재테이블의 mnum 필드는 memberlist 테이블의 membernum 필드값을 참조
    constraint fk2 foreign key(mnum) references memberlist(membernum)
);

 

새로운 테이블 rentlist 를 작성했다.

해당 테이블에서 눈여겨 봐야할 부분은 밑에 두줄, 외래키 설정 부분이다.

 

-- 현재테이블의 bnum 필드는 booklist 테이블의 booknum 필드값을 참조
constraint fk1 foreign key(bnum) references booklist(booknum),
-- 현재테이블의 mnum 필드는 memberlist 테이블의 membernum 필드값을 참조
constraint fk2 foreign key(mnum) references memberlist(membernum)

 

외래키는 입력하려는 값이 '참조되는 필드값들 중에 없는 값'이 참조하는 필드에 입력되지 않게 제약을 두는 키다. 다른 테이블에 있는 필드값들 중에 하나여야 할 때, 두 필드를 연결하는 키인 것이다.

 

rentlist의 bnum 필드는 booklist의 booknum을 참조하고,

rentlist의 mnum 필드는 memberlist의 membernum을 참조하도록 외래키를 지정했다.

 

위와 같이 설정을 한다면

참조되는 booknum 값에 1부터 10까지의 수가 있다고 했을 때, 참조하는 필드인 bnum은 1부터 10까지만 입력될 수 있는 것이다.

 

또한 외래키의 참조대상이 되는 상대 필드는 해당 테이블의 기본키여야 한다. 쉽게 말해 연결하고자 하는 테이블의 기본키만 외래키로 지정할 수 있는 것이다. 예시에서의 booknum과 membernum은 각각 해당 테이블의 기본키다.

기본키와 외래키로 지정된 필드는 삭제, 수정 과정에서 제약조건을 삭제하는 등의 번거로운 작업을 거쳐야 한다. 이와 관련된 얘기는 해당 파트에서 예시로 다루도록 하겠다.