Oracle - CREATE(생성, 제약조건), INSERT

오라클 객체 종류

테이블, 뷰, 시퀀스. 인덱스, 패키지, 프로시저, 함수, 트리거, 동의어, 사용자

 

CREATE

테이블이나 인덱스, 뷰 등 데이터베이스 객체를 생성하는 구문

아래의 형식으로 테이블을 생성한다.

-- 표현식
CREATE TABLE 테이블명 (
    컬럼명 자료형(크기),
    컬럼명 자료형(크기)
    ...
);

 

 

오라클의 자료형

자료형	설명
CHAR(크기)	고정길이 문자 데이터(최대 2000Byte)
VARCHAR2(크기)	가변길이 문자 데이터(최대 4000Byte)
NUMBER	숫자 데이터(최대 40자리)
NUMBER(길이)	숫자데이터로, 길이 지정 가능
DATE	날짜 데이터
LONG	가변 길이 문자형 데이터(최대2GB)
LOB	2GB까지의 가변길이 바이너리 데이터 저장 가능

 

CHAR

CHAR(SIZE)

- SIZE : 포함될 문자열의 크기

- 지정한 크기보다 작은 문자(열)가 입력되면 남는 공간은 공백으로 채움

- 데이터는 "를 사용하여 표기하고 대·소문자를 구분함

 

VARCHAR2

VARCHAR(SIZE)

- SIZE : 포함될 문자열의 크기

- 지정한 크기보다 작은 문자(열)가 입력되면 남는 공간은 공백으로 채움

- 데이터는 "를 사용하여 표기하고 대·소문자를 구분함

 

NUMBER

NUMBER(P [,S])

- P : 표현할 수 있는 전체 숫자 자리 수(1 ~ 38)

- S : 소수점 이하 자리 수 (-84 ~ 127)

 

DATE

날짜 관련

- 일자 및 시간 정보 관리

- 기본적으로 화면에 년/월/일 정보만 표기

- 날짜 연산 및 비교 가능

 

실습

1. 테이블 생성

CREATE TABLE MEMBER( --테이블명은 MEMBER 인 테이블생성
    -- 테이블안에 넣을 자료형
    -- 아이디, 비밀번호, 이름, 가입일 순으로 생성
    MEMBER_ID VARCHAR2(30),
    MEMBER_PW VARCHAR2(30),
    MEMBER_NAME VARCHAR2(10),
    MEMBER_DATE DATE
);

작성후 스크립트 실행하면 아래와같은 스크립트출력 됩니다.

그후에 테이블확인해보면 생성시 명시했던 자료형 추가완료!

 

2. 테이블에 값 추가

-- 테이블에 회원정보 추가하기
-- 홍길동 회원추가하기
INSERT INTO MEMBER 
VALUES('id01', 'pass01', '홍길동', '210703'); 

-- 이순신 회원추가하기
INSERT INTO MEMBER 
VALUES('id02', 'pass02', '이순신', '210704'); 

 

회원 추가후 테이블 추가된값 확인하기

 

컬럼에 주석 표시하기

테이블에 알아보기 쉽게 주석을 다는 구문 

상당히 유용함!..

 

테이블의 COMMENTS컬럼에 주석이없다면 아래와같은화면을확인할수있다.

-- 컬럼 주석 달기
-- COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';
COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PW IS '회원비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원이름';
COMMENT ON COLUMN MEMBER.MEMBER_DATE IS '가입일자';

다시 MEMBER테이블 COMMENTS를 확인해보자!

 

 

 

제약조건

테이블 작성 시 각 컬럼에 기록될 데이터에 대해 제약조건을 설정할 수 있는데

이는 데이터 무결성 보장을 주 목적으로 함

입력 데이터에 문제가 없는지에 대한 검사와 데이터의 수정/삭제 가능 여부 검사 등을 위해 사용

 

제약조건 종류

NOT NULL - 데이터에 NULL 허용하지 않음

UNIQUE - 중복된 값을 허용 하지않음

PRIMARY KEY - NULL과 중복 값을 허용하지 않음(컬럼의 고유 식별자로 사용하기 위해

FOREIGN KEY - 참조되는 테이블의 컬럼의 값이 존재하면 허용

CHECK - 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 허용

 

 

NOT NULL

해당 컬럼에 반드시 값이 입력되어야함

특정 컬럼에 저장하거나 수정할때 값이 NULL값을 저장하지 못하게 제약을둠

 

제약조건을 사용할땐 컬럼레벨과 테이블레벨이 존재하는데

NOT NULL 조건은 무조건 컬럼레벨에만 작성해야함.

 

제약조건을 사용하지않을시

-- 제약조건 NOTNULL
-- 제약조건 사용하지 않을시
-- 아래 테이블 사용
CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(30),
    MEMBER_PW VARCHAR2(30),
    MEMBER_NAME VARCHAR2(10),
    MEMBER_DATE DATE
);

INSERT INTO MEMBER 
VALUES(null, null, '유관순', '210703');

 

아이디와 비밀번호가 null인 유관순 회원추가

만약 로그인하였을때 값이 null이면 로그인할수가없으니깐 회원추가할때 notnull 이용해서 추가를막아보자!

 

아이디, 비밀번호에 NOT NULL 을 추가하였을시

-- 제약조건 NOT NULL 사용시
-- 아래 테이블 사용
CREATE TABLE NOT_MEMBER(
    -- NOT NULL 구문은 항상 컬럼 레벨에 사용하여야한다.
    MEMBER_ID VARCHAR2(30) NOT NULL,
    MEMBER_PW VARCHAR2(30) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_DATE DATE
);

-- 테이블에 회원정보 추가하기
-- 홍길동 회원추가하기
INSERT INTO NOT_MEMBER 
VALUES('id01', 'pass01', '홍길동', '210703'); 

-- 이순신 회원추가하기
INSERT INTO NOT_MEMBER 
VALUES('id02', 'pass02', '이순신', '210704'); 

-- 아이디 와 비밀번호가 NULL인 유관순 회원추가
INSERT INTO NOT_MEMBER
VALUES(null, NULL, '유관순', '210705');

유관순의 회원을 추가하였을시 나오는오류입니다.

NULL값을 추가할수없습니다.

NOT_MEMBER 테이블의 MEMBER_ID컬럼 라는 오류를 확인할수있다 .

그리고 null뿐만 아니라 '' < 빈문자열을 넣었을시 에도 똑같은 오류를확인할수있다.

오라클은 ''값이 null로 인식한다고 그랬던거같다 .

사용처는 꼭 값이 필요한 부분에 제약을두면 편리하게 사용될것같다!

 

UNIQUE

컬럼 입력 값에 대해 중복을 제한하는 제약조건 컬럼레벨과 테이블레벨에 사용가능

 

UNIQUE 제약조건을 사용하지않았을시

-- UNIQUE
-- UNIQUE를 사용하지 않았을시
CREATE TABLE NOT_UNI_MEMBER(
    MEMBER_ID VARCHAR2(30),
    MEMBER_PW VARCHAR2(30),
    MEMBER_NAME VARCHAR2(10),
    MEMBER_DATE DATE
);
-- 홍길동 회원추가하기
INSERT INTO NOT_UNI_MEMBER 
VALUES('id01', 'pass01', '홍길동', '210703'); 

-- 이순신 회원추가하기
INSERT INTO NOT_UNI_MEMBER 
VALUES('id02', 'pass02', '이순신', '210704'); 

-- 유관순 회원추가하기
INSERT INTO NOT_UNI_MEMBER 
-- ID값 홍길동 아이디와 값 똑같게 함
VALUES('id01', 'pass03', '유관순', '210704'); 

홍길동 아이디와 유관순 아이디가 똑같은것을 확인할수있다.

만약 홈페이지를 로그인하면 아이디는 무조건 하나만 존재해야하는데

여러개가존재하기때문에 정상적인 행동은아니다 .

이것을 막기위해 UNIQUE 제약조건을 사용하자!

 

UNIQUE 제약조건을 사용할시

-- UNIQUE를 사용할시
-- 컬럼레벨에 명시할시
CREATE TABLE UNI_MEMBER(
    MEMBER_ID VARCHAR2(30) UNIQUE,
    MEMBER_PW VARCHAR2(30),
    MEMBER_NAME VARCHAR2(10),
    MEMBER_DATE DATE
);

-- 테이블 레벨에 명시할시
CREATE TABLE UNI_MEMBER(
    MEMBER_ID VARCHAR2(30),
    MEMBER_PW VARCHAR2(30),
    MEMBER_NAME VARCHAR2(10),
    MEMBER_DATE DATE,
    UNIQUE(MEMBER_ID)
);

-- 홍길동 회원추가하기
INSERT INTO UNI_MEMBER 
VALUES('id01', 'pass01', '홍길동', '210703'); 

-- 이순신 회원추가하기
INSERT INTO UNI_MEMBER 
VALUES('id02', 'pass02', '이순신', '210704'); 

-- 유관순 회원추가하기
INSERT INTO UNI_MEMBER 
-- ID값 홍길동 아이디와 값 똑같게 함
VALUES('id01', 'pass03', '유관순', '210704'); 

 

홍길동, 이순신 데이터는 잘들어갔는데 유관순의 데이터가 들어가질않았다. 

오류내용을 확인해보자

 

PRIMARY KEY

테이블에서 한 행의 정보를 구분하기 위한 고유 식별자 역할

NOT NULL의 의미와 UNIQUE의 의미를 둘 다 가지고 있으며 한테이블 당 하나만 설정 가능

컬럼 레벨과 테이블 레벨 둘  다  지정 가능함

 

무조건 중복되는값이 없고 그 정보를 대표하는 ? 값일때 설정하면 될것같다.

 

컬럼 레벨에 명시할시

--PRIMARY KEY
-- 컬럼 레벨에 명시할시
CREATE TABLE PRI_MEMBER(
    MEMBER_ID VARCHAR2(30) PRIMARY KEY,
    MEMBER_PW VARCHAR2(30) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_DATE DATE
);

-- 테이블 레벨에 명시할시
CREATE TABLE PRI_MEMBER(
    MEMBER_ID VARCHAR2(30),
    MEMBER_PW VARCHAR2(30) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_DATE DATE,
    PRIMARY KEY(MEMBER_ID)
);

-- 홍길동 회원추가하기
INSERT INTO PRI_MEMBER 
VALUES('id01', 'pass01', '홍길동', '210703'); 

-- 이순신 회원추가하기
INSERT INTO PRI_MEMBER 
VALUES('id02', 'pass02', '이순신', '210704'); 

-- 유관순 회원추가하기
INSERT INTO PRI_MEMBER 
VALUES('id01', 'pass03', '유관순', '210704'); 

 

이미 존재하는 아이디를 추가할시 아래같은 오류발생

 

DEFAULT

DEFAULT는 아무 값도 입력하지 않아도 NULL 값이 아니라 설정한 값이 기본값으로 자동 입력되도록 하는 제약조건 입니다.

-- DEFAULT 값 설정하기
-- 날짜 입력안했을시 오늘날짜로 설정하기
CREATE TABLE DF_MEMBER(
    MEMBER_ID VARCHAR2(30),
    MEMBER_PW VARCHAR2(30) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_DATE DATE DEFAULT SYSDATE
);

-- 홍길동 회원추가하기
INSERT INTO DF_MEMBER 
VALUES('id01', 'pass01', '홍길동', '210703'); 

-- 이순신 회원추가하기
INSERT INTO DF_MEMBER 
VALUES('id02', 'pass02', '이순신', '210704'); 

-- 유관순 회원추가하기
INSERT INTO DF_MEMBER(MEMBER_ID, MEMBER_PW, MEMBER_NAME)
VALUES('id03', 'pass03', '유관순'); 

유관순 회원 추가할때 DATE 값은 입력하지않았는데

DEFAULT 값이 SYSDATE 이기때문에 오늘날짜로 자동입력된것을 확인할수 있다 .

FOREIGN KEY

참조 무결성을 위한 제약조건으로 참조된 다른 테이블이 제공한 값만 사용하도록 제한을 거는 것

참조되는 컬럼과 참조된 컬럼을 통해 테이블 간에 관계가 형성 되는데

참조되는 값은 제공되는 값 외에 NULL을 사용 가능하며

참조할 테이블의 참조할 컬럼명을 생략할 경우

PRIMARY KEY로 설정된 컬럼이 자동으로 참조할 컬럼이 됨

-- FOREIGN KEY
-- 회원 등급을 설정하는 테이블
DROP TABLE GEADE_USER;
CREATE TABLE GRADE_USER(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(20) NOT NULL
);

INSERT INTO GRADE_USER
VALUES(10, '일반회원');

INSERT INTO GRADE_USER
VALUES(20, '고급회원');

INSERT INTO GRADE_USER
VALUES(50, '관리자');

-- USER_FOREIGNKEY 테이블 생성
-- GRADE_USER 테이블의 GRADE_CODE를 외래키로 설정
CREATE TABLE USER_FOREIGNKEY(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(30) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30) NOT NULL,
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(20),
    GRADE_CODE NUMBER,
    FOREIGN KEY(GRADE_CODE) REFERENCES GRADE_USER(GRADE_CODE) 
);

-- USER_FOREIGNKEY COMMENT 설정
COMMENT ON COLUMN USER_FOREIGNKEY.USER_NO IS '회원번호';
COMMENT ON COLUMN USER_FOREIGNKEY.USER_ID IS '아이디';
COMMENT ON COLUMN USER_FOREIGNKEY.USER_PWD IS '비밀번호';
COMMENT ON COLUMN USER_FOREIGNKEY.USER_NAME IS '이름';
COMMENT ON COLUMN USER_FOREIGNKEY.GENDER IS '성별';
COMMENT ON COLUMN USER_FOREIGNKEY.PHONE IS '핸드폰번호';
COMMENT ON COLUMN USER_FOREIGNKEY.EMAIL IS '이메일';
COMMENT ON COLUMN USER_FOREIGNKEY.GRADE_CODE IS '계급번호';

-- 회원정보 추가
INSERT INTO USER_FOREIGNKEY
VALUES(1, 'test01', 'test01', '홍길동', '남', '010-1111-1111', 'hong@naver.com', 10);
INSERT INTO USER_FOREIGNKEY
VALUES(2, 'test02', 'test02', '이순신', '남', '010-2222-2222', 'lee@naver.com', 20);
INSERT INTO USER_FOREIGNKEY
VALUES(3, 'test03', 'test03', '유관순', '여', '010-3333-3333', 'yoo@naver.com', 20);
INSERT INTO USER_FOREIGNKEY
VALUES(4, 'test04', 'test04', '다모', '여', '010-4444-4444', 'damo@naver.com', 50);

-- 만약 GRADE_USER 테이블의 GRADE_CODE값이 저장되어있지않은 값을 추가한다면 ? 
-- 30의 값은 설정되어있지않다.
INSERT INTO USER_FOREIGNKEY
VALUES(5, 'test05', 'test05', '사명대사', '남', '010-5555-5555', 'sa@naver.com', 30);

5개의 값을 추가했는데 4개의값만 추가가되었다 . 오류를 확인해보자

네이버 파파고 출처 - 상위 키를 찾을 수 없음

상위키인 GRADE_USER 테이블의 GRADE_CODE에 30이라는값을 추가한적이없어서에러가나는것이다.

 

갑자기 GRADE_CODE의 값을 하나 삭제해야 한다 삭제를진행해보자!...

-- GRADE_USER 테이블의 GRADE_CODE = 10(일반회원) 값 삭제
-- GRADE_USER테이블의 GRADE_CODE 값이 10인 값을 삭제하라 라는 구문임
DELETE FROM GRADE_USER WHERE GRADE_CODE = 10;

아래와같은 오류로인해 삭제가안된다.

대충 뜻은 자식에서 사용중인 값이기떄문에 삭제를 할수가없다는 이야기임 

삭제를할려면 다른 옵션을 사용해야한다.

 

 

FOREIGN KEY 옵션(ON DELETE SET NULL)

부모 테이블의 데이터 삭제 시 참조하고 있는 테이블의 컬럼 값이 NULL로 변경된다

테이블생성 할때 옵션 설정하는것이다.

--
-- FOREIGN KEY
-- 회원 등급을 설정하는 테이블
DROP TABLE GEADE_USER2;
CREATE TABLE GRADE_USER2(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(20) NOT NULL
);

INSERT INTO GRADE_USER2
VALUES(10, '일반회원');

INSERT INTO GRADE_USER2
VALUES(20, '고급회원');

INSERT INTO GRADE_USER2
VALUES(50, '관리자');

-- USER_FOREIGNKEY 테이블 생성
-- GRADE_USER 테이블의 GRADE_CODE를 외래키로 설정
CREATE TABLE USER_FOREIGNKEY2(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(30) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30) NOT NULL,
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(20),
    GRADE_CODE NUMBER,
    --옵션은 아래에서 적용한다 ON DELETE SET NULL
    FOREIGN KEY(GRADE_CODE) REFERENCES GRADE_USER2(GRADE_CODE) ON DELETE SET NULL
);

-- USER_FOREIGNKEY COMMENT 설정
COMMENT ON COLUMN USER_FOREIGNKEY2.USER_NO IS '회원번호';
COMMENT ON COLUMN USER_FOREIGNKEY2.USER_ID IS '아이디';
COMMENT ON COLUMN USER_FOREIGNKEY2.USER_PWD IS '비밀번호';
COMMENT ON COLUMN USER_FOREIGNKEY2.USER_NAME IS '이름';
COMMENT ON COLUMN USER_FOREIGNKEY2.GENDER IS '성별';
COMMENT ON COLUMN USER_FOREIGNKEY2.PHONE IS '핸드폰번호';
COMMENT ON COLUMN USER_FOREIGNKEY2.EMAIL IS '이메일';
COMMENT ON COLUMN USER_FOREIGNKEY2.GRADE_CODE IS '계급번호';

-- 회원정보 추가
INSERT INTO USER_FOREIGNKEY2
VALUES(1, 'test01', 'test01', '홍길동', '남', '010-1111-1111', 'hong@naver.com', 10);
INSERT INTO USER_FOREIGNKEY2
VALUES(2, 'test02', 'test02', '이순신', '남', '010-2222-2222', 'lee@naver.com', 20);
INSERT INTO USER_FOREIGNKEY2
VALUES(3, 'test03', 'test03', '유관순', '여', '010-3333-3333', 'yoo@naver.com', 20);
INSERT INTO USER_FOREIGNKEY2
VALUES(4, 'test04', 'test04', '다모', '여', '010-4444-4444', 'damo@naver.com', 50);

-- GRADE_USER 테이블의 GRADE_CODE 10을 삭제하자!
DELETE FROM GRADE_USER WHERE GRADE_CODE = 10;

DELETE FROM 구문을 실행하지않았을때 나오는 테이블값

 

 

DELETE FROM 구문을 실행시킨후 나오는 구문

10 - 일반회원값 정상 삭제되었습니다.

1번 홍길동 회원의 GRADE_CODE 값은 10에서 NULL값으로 변경된것을 확인할수 있다.

 

FOREIGN KEY 옵션(ON DELETE CASCADE)

부모 테이블의 데이터 삭제 시 참조하고 있는 테이블의 컬럼 값이 존재하던 행 전체 삭제

 

-- CASCADE
-- FOREIGN KEY
-- 회원 등급을 설정하는 테이블
DROP TABLE GEADE_USER3;
CREATE TABLE GRADE_USER3(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(20) NOT NULL
);

INSERT INTO GRADE_USER3
VALUES(10, '일반회원');

INSERT INTO GRADE_USER3
VALUES(20, '고급회원');

INSERT INTO GRADE_USER3
VALUES(50, '관리자');

-- USER_FOREIGNKEY 테이블 생성
-- GRADE_USER 테이블의 GRADE_CODE를 외래키로 설정
CREATE TABLE USER_FOREIGNKEY3(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(30) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30) NOT NULL,
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(20),
    GRADE_CODE NUMBER,
    --옵션은 아래에서 적용한다 ON DELETE SET NULL
    FOREIGN KEY(GRADE_CODE) REFERENCES GRADE_USER3(GRADE_CODE) ON DELETE CASCADE
);

-- USER_FOREIGNKEY COMMENT 설정
COMMENT ON COLUMN USER_FOREIGNKEY3.USER_NO IS '회원번호';
COMMENT ON COLUMN USER_FOREIGNKEY3.USER_ID IS '아이디';
COMMENT ON COLUMN USER_FOREIGNKEY3.USER_PWD IS '비밀번호';
COMMENT ON COLUMN USER_FOREIGNKEY3.USER_NAME IS '이름';
COMMENT ON COLUMN USER_FOREIGNKEY3.GENDER IS '성별';
COMMENT ON COLUMN USER_FOREIGNKEY3.PHONE IS '핸드폰번호';
COMMENT ON COLUMN USER_FOREIGNKEY3.EMAIL IS '이메일';
COMMENT ON COLUMN USER_FOREIGNKEY3.GRADE_CODE IS '계급번호';

-- 회원정보 추가
INSERT INTO USER_FOREIGNKEY3
VALUES(1, 'test01', 'test01', '홍길동', '남', '010-1111-1111', 'hong@naver.com', 10);
INSERT INTO USER_FOREIGNKEY3
VALUES(2, 'test02', 'test02', '이순신', '남', '010-2222-2222', 'lee@naver.com', 20);
INSERT INTO USER_FOREIGNKEY3
VALUES(3, 'test03', 'test03', '유관순', '여', '010-3333-3333', 'yoo@naver.com', 20);
INSERT INTO USER_FOREIGNKEY3
VALUES(4, 'test04', 'test04', '다모', '여', '010-4444-4444', 'damo@naver.com', 50);

-- GRADE_USER 테이블의 GRADE_CODE 10을 삭제하자!
DELETE FROM GRADE_USER3 WHERE GRADE_CODE = 10;

 

DELETE 하기전 테이블 값

DELETE 하고난후 테이블 값

 

10번은 삭제후 테이블확인하니깐 이번엔 NULL이 아니고 행전체가 삭제된것을 확인할수있다 .

 

공부후기..

데이터베이스 쪽은진짜 쉽게봤었는데

생각보다 해야할것도많고 수학적인부분도많아서 너무너무너무 힘들지만

그래도 재미있다 .. 짜증은나는데 계속계속생각이난다 ㅋㅋㅋㅋ