[오라클] SQL 테이블 생성하는 방법

 

 

 

핫.. 기본의 기본도 안쓰면 가물가물한 법. 

 

 

 

1) 테이블 구상

SQL로 쿼리문을 작성하기 전에 ERD (Entity Relationship Diagram)를 미리 작성하는게 좋다.

테이블을 정의 내리고 사전에 테이블끼리의 관계를 맺어 DB 설계가 어떻게 되었는지를 한눈에 볼 수 있는 다이어그램 정리표로 나는 무료 사이트인 ERD Cloud를 애용한다. 관계를 알아야 쿼리문을 작성할 때 기본키 참조키 등 설정하기 편리하다.

 

https://www.erdcloud.com/

ERDCloud

난 이렇게 테이블을 설정 해놨고, 이 테이블 구상도를 토대로 테이블 생성 쿼리문 작성하는걸 정리해보려고 한다.

 

사실 ERD에서 테이블 작성을 해놓으면 쿼리문도 자동으로 작성해주기에 복사해서 붙여넣어 쓸 수 있다.

 

1) 좌측 하단의 Export 클릭

 

2) 파란색 버튼 눌러서 원하는 DB 선택

 

3) 원하는 대로 체크박스 표시 해주고 ( PK, FK 제약조건도 필요한지 DROP 문구 필요한지 선택된 엔터티만 만들면 되는지 등등) SQL PREVIEW 눌러보면 미리 볼 수 있고 Download SQL 누르면 전체 쿼리문 다운이 된다. 하지만... 뭔가 2% 부족해서 제약조건도 제대로 안들어가 있고 시퀀스도 없으며 저 쌍따옴표가 있어서 수정작업이 필요하다.

 

 

 

 

 

 

2) 테이블 생성 쿼리문

일단 기본적으로 테이블 생성 하는 쿼리문은 아래와 같다.

CREATE TABLE (테이블명)(
	(컬럼명) (데이터타입) (기본값[필요시]) (제약조건[필요시])
);

컬럼명과 데이터타입만 적어도 가능하며 기본값이나 제약조건은 필요시 기입한다.

 

2-1) 테이블명, 컬럼명

테이블명은 문자와 숫자 특수문자에서는 ( _, $, # ) 사용 할 수 있지만 첫 글자는 반드시 문자로 와야한다.

SQL에서 사용하는 예약어 CREATE, SELECT, DELETE... 등은 테이블명으로 사용할 수 없다.

 

 

2-2) 데이터타입

내가 주로 쓰는 데이터 타입은 아래 4가지로 이 네개의 데이터 타입만 인지해도 사이트 제작이 충분했다.

CHAR(N)	고정길이로 괄호안에 쓴 길이를 초과한 데이터는 저장 할 수 없다. 예를 들면 Y,N등 한글자만 저장할 때 주로 사용. (데이터 구분용)
VARCHAR2(N)	가변길이로 최대 N만큼 쓸 수 있다. 아이디, 비밀번호, 컨텐츠 등 다양한 곳에 활용 된다.
NUMBER	숫자를 저장하는 데이터타입으로 십진수를 저장한다. 기본 값은 0며 최대 22Byte까지 저장된다.
DATE	날짜를 저장하는 데이터 타입이다.

 

 

2-3) 기본값

기본값은 말그대로 데이터 값이 넘어오지 않더라도 자동으로 입력하는 값을 설정해놓는거다.

예를 들면 회원가입시 회원 상태 구분 값을 미리 넣어둔다던가 (1. 활동 / 2. 휴면 / 3. 탈퇴) 이런식의 구분 말이다.

또는 날짜를 입력하지 않아도 데이터가 저장된 시점으로 기록하려고 쓰는 SYSDATE 등이 있다.

MEM_DATE DATE DEFAULT SYSDATE NOT NULL

위와 같은 형태로 컬럼명, 데이터 타입, 기본값, 제약조건 형식에서 기본값 지정은 값 앞에 DEFAULT를 표기해 적어주면 된다.

 

 

 

2-4) 제약조건

원하는 데이터 값만을 유지하기 위해서 특정 컬럼마다 설정하는 제약이다. (데이터 무결성 보장을 목적으로 한다.)

제약조건이 부여된 컬럼에 문제가 있는지 없는지 자동으로 검사하는 시스템이다.

NOT NULL	해당 컬럼이 비어있어도 되는 컬럼인지 아닌지 지정해주는 것이다. 예를들면 회원가입시 아이디는 절대 없으면 안되는 정보이다. 아이디가 비어있으면 컬럼에 데이터 값이 저장 될 수 없도록 NOT NULL로 표기를 해줘야 한다. 이때 아무것도 입력하지 않으면 기본값은 NULL로 들어간다.
PK (기본키)	엔터티를 특정 지을 수 있는 기본키이다. 사람으로 치면 주민번호가 될 수 있고, 회원으로 치면 아이디가 될 수 있는 키값을 기본키라고 한다. 기본키 지정시엔 제약조건을 적는 곳에 'PRIMARY KEY'라고 기재해주면 된다.
고유키	기본키가 되진 못했지만 고유해야 하는 값들에 설정한다. 예를 들면 회원가입시 기본키를 아이디로 설정했다면 ( 기본키도 유니크 제약조건성을 가지고 있어 겹칠 수 없다. ) 사이트에서 사용하는 명찰 같은 닉네임은 고유키로 설정할 수 있다. 고유키는 중복된 데이터가 없어야 할 때 사용하며 제약조건 적는 곳에 'UNIQUE'를 기재해주면 된다.
체크 제약조건	설정한 값들 중에서만 데이터 값이 입력되어야 할 경우 사용한다. 예를 들어 남자는 M, 여자는 W로 값을 지정시켰을 경우 해당 컬럼에는  지정한 값 제외한 값들이 입력될 시 제약조건 미  충족으로 오류가 발생한다.
FK (외래키)	해당 컬럼에 다른 테이블에 존재하는 값이 들어와야하는 경우에 부여하는 제약조건이다. 다른 테이블을 참조한다고 표현하며 참조된 다른 테이블이 제공하고 있는 값이 들어올 수 있다. 이 외래키를 통해 테이블끼리의 관계가 형성된다.

 

2-4-1) 제약조건 설정 방법 예시

https://hyerin-shin.tistory.com/32

[SQL] 제약조건 설정 방법과 사용 예시모음

 

 

 

 

 

 

3) 테이블 생성 예시

 

 

내가 만들어둔 MEMBER 테이블을 기준으로 테이블 생성 쿼리문을 작성하면 아래와 같다.

CREATE TABLE MEMBER(
    NICKNAME VARCHAR2(30)  NOT NULL PRIMARY KEY,
    PWD VARCHAR2(30) NOT NULL,
    EMAIL VARCHAR2(60) NOT NULL UNIQUE,
    CREATE_DATE DATE DEFAULT SYSDATE,
    STATUS CHAR(1) DEFAULT 'Y' CHECK (STATUS IN ('Y','N'))
);

MEMBER라는 테이블을 생성 후 컬럼으로는 NICKNAME, PWD, EMAIL, CREATE_DATE, STATUS를 넣어줬다.

모든 값은 null 값이 들어올 수 없어 NOT NULL을 넣어줬으며 가입일은 기본값으로 (SYSDATE : 현재 시간)을, 회원 상태는 기본값은 Y이면서도 Y나 N 이외의 다른 문자는 들어올 수 없도록 체크 제약 조건을 달아주었다.

 

 

 

 

 

CREATE TABLE PET(
    PET_NO NUMBER NOT NULL PRIMARY KEY,
    NICKNAME VARCHAR2(30) REFERENCES MEMBER(NICKNAME),
    PET_NAME VARCHAR2(60) NOT NULL,
    PET_TYPE VARCHAR2(100) NOT NULL,
    PET_DETAIL VARCHAR2(100),
    BIRTH DATE
);

 

 

마지막으로 관계설정을 예시로 들기위한 PET테이블을 추가로 만들어봤다. 이 테이블들은 회원가입한 유저(=사람)에 대한 정보 MEM테이블과 그 유저가 관리하고 있는 동물들을 저장하기 위한 PET테이블로 나뉘어진 것이다. 둘의 관계는 닉네임을 기준으로 관계를 형성했다는걸 위처럼 쿼리문으로 작성할 수 있다.

 

 

 

 

 

4) 생성된 테이블 구조 확인

DESC (테이블명);