포스트

08. DDL(CREATE)

게시
By Kim Jung Wook 17 분읽는 시간

데이터 딕셔너리

  • 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블.
  • 데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블

DDL(CREATE)

  • 데이터 정의 언어
  • 객체(OBJECT)를 만들고(CREATE), 수정(ALTER), 삭제(DROP) 등 데이터의 전체 구조를 정의하는 언어로 주로 DB관리자, 설계자가 사용함
  • 오라클에서 객체 : 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), 패키지(PACKAGE), 트리거(TRIGGER), 프로시져(PROCEDURE), 함수(FUNCTION), 동의어(SYNOMYM), 사용자(USER)

1) CREATE

  • 테이블이나 인덱스, 뷰 등 다양한 데이터베이스 객체를 생성하는 구문
  • 테이블로 생성된 객체는 DROP 구문을 통해 제거할 수 있음
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- < 테이블 생성하기 >
-- ✔ 테이블이란? 행과 열로 구성되는 가장 기본적인 데이터베이스 객체
-- ✔ 데이터베이스 내에서 모든 데이터는 테이블을 통해서 저장된다

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

CREATE TABLE MEMBER(
  MEMEBER_ID VARCHAR2(20),
  MEMBER_PWD VARCHAR2(20),
  MEMBER_NAME VARCHAR2(30),
  MEMBER_SSN CHAR(14),
  ENROLL_DATE DEFAULT SYSDATE
);

1
2
3
4
5
6
7
8
9
10
11
12
13

< `자료형` >
NUMBER : `숫자형`(`정수, 실수`)

CHAR(`크기`) : `고정길이 문자형`(2000BYTE)
➡ CHAR(10) `컬럼에 'ABC' 3BYTE 문자열만 저장해도 10BYTE 저장공간을 모두 사용`

VARCHAR2(`크기`) : `가변길이 문자형`(4000BYTE)
➡ VARCHAR2(10) `컬럼에 'ABC' 3BYTE 문자열만 저장하면 나머지 7BYTE를 반환`

DATE : `날짜 타입`

BLOB : `대용량 이진 데이터`(4GB)
CLOB : `대용량 문자 데이터`(4GB)

2) 컬럼에 주석 달기

1
2
3
4
5
6
7
8

[ `표현식` ] 
COMMENT ON COLUMN `테이블명`.`컬럼명` IS '주석내용';

COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원 아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원 비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원 이름';
COMMENT ON COLUMN MEMBER.MEBER_SSN IS '회원 주민번호';
COMMENT ON COLUMN MEMBER.ENROLL_DATE IS '회원 가입일';

1
2

USER_TABLES : `사용자가 작성한 테이블을 확인 하는 뷰, 데이터 딕셔너리에 정의되어 있음`
SELECT * FROM USER_TABLES;

1
2
3
4
5
6
7

INSERT INTO `테이블명` VALUES(`값1`, `값2`, ...);
INSERT INTO MEMEBER VALUES('MEM01', '12345ABC', '홍길동', '991213-1234567', DEFAULT);
-- INSERT / UPDATE 시 컬럼값으로 DEFAULT를 작성하면 해당 컬럼에 DEFAULT 값 입력

INSERT INTO MEMEBER(MEMBER_ID, MEMBER_PWD, MEMBER_NAME)
VALUES('MEM04', 'PW04', '이지연');
-- INSERT 시 미작성 하는 경우, 컬럼에 DEFAULT 값이 정해져 있으면 DEFAULT 값이 반영된다

1
2
3
4
5
6
7

NUMBER `사용시 문제점`
INSERT INTO MEMBER VALUES('MEM03', 'PW03', '아무개', 7712341234);
INSERT INTO MEMBER VALUES('MEM04', 'PW04', '개똥이', 01012341234);
➡ NUMBER `타입 컬럼에 데이터 삽입시`
-- ✔ 제일 앞에 0이 있으면 자동으로 제거함
-- ✔ 주민번호 또는 전화번호 같이 '-' 있으면 연산함
-- ✔ 숫자 표현시 자리수에 맞춰 ',' 발생

3) CONSTRAINTS(제약조건)

  • 사용자가 원하는 조건의 데이터만 유지하기 위해서 특정 컬럼에 설정하는 제약
  • 데이터 무결성 보장을 목적으로 함 ➡️ 중복 데이터가 없다
  • 입력 데이터에 문제가 없는지 자동으로 검사하는 목적
  • 데이터의 수정/삭제 가능 여부 검사등을 목적으로 한다 ➡️ 제약조건을 위배하는 DML 구문은 수행할 수 없음
  • PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY, CHECK

  • USER_CONSTRAINTS : 사용자가 작성한 제약조건을 확인하는 딕셔너리 뷰

  • (1) NOT NULL
    • 해당 컬럼에 반드시 값이 기록되어야하는 경우 사용
    • 삽입/수정 시 NULL 값을 허용하지 않도록 컬럼레벨에서 제한
    • 컬럼레벨? 테이블 생성 시 컬럼을 정의하는 부분에서 작성하는 것
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

    CREATE TABLE USER_USED_NN(
  USER_NO NUMBER NOT NULL,
  USER_ID VARCHAR2(20),
  USER_PWD VARCHAR2(20),
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50)
);
  
INSERT INTO USER_USED_NN
VALUES(1, 'USER01', 'PASS01', '홍길동', '남', '010-1234-1234',
'Hong@kh.or.kr');
  
INSERT INTO USER_USED_NN
VALUES(NULL, NULL, NULL, NULL, '남', '010-1234-1234', 'Hong@kh.or.kr');
-- ORA-01400: NULL을 ("KH"."USER_USED_NN"."USER_NO") 안에 삽입할 수 
-- 없습니다 ➡️ NOT NULL 제약조건에 위배되어 오류 발생
  • (2) UNIQUE
    • 컬럼에 입력값에 대해서 중복을 제한하는 제약조건
    • 컬럼레벨과 테이블 레벨에서 설정 가능
    • 단, UNIQUE 제약조건이 설정된 컬럼에 NULL 값은 중복 삽입 가능
    • 테이블 레벨 : 테이블 생성시 컬럼 정의가 끝난 후 마지막에 작성
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

    -- < 컬럼레벨 : [CONSTRAINT 제약조건명] 제약조건 >
-- < 테이블레벨 : [CONSTRAINT 제약조건명] 제약조건(컬럼명) >
  
CREATE TABLE USER_USED_UQ(
  USER_NO NUMBER,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(20)
  /* 테이블 레벨 */
  -- CONSTARINT USER_ID_UQ UNIQUE(USER_ID)
);
  
INSERT INT USER_USED_UQ
VALUES(1, 'USER01', 'PW01');
INSERT INT USER_USED_UQ
VALUES(2, 'USER01', 'PW02');
-- ORA-00001: 무결성 제약 조건(KH.SYS_C008405)에 위배됩니다
  
INSERT INTO USER_USED_UQ
VALUES(1, NULL, 'PW01');
-- 아이디에 NULL 값 입력 가능. NULL은 중복 가능

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

    -- < UNIQUE 복합키 >
-- 두개 이상의 컬럼을 묶어서 하나의 UNIQUE 제약 조건을 설정함
-- 복합키 지정은 테이블 레벨에서만 가능하다
-- 복합키는 지정된 모든 컬럼의 값이 같을 때 위배된다
  
CREATE TABLE USER_USED_UQ2(
  USER_NO NUMBER,
  USER-ID VARCHAR2(20),
  USER_PWD VARCHAR2(20) NOT NULL,
  USER NAME VARCHAR2(30),
  CONSTRAINT USER_ID_NAME_UQ UNIQUE(USER_ID, USER_NAME)
);    
  
INSERT INTO USER_USED_UQ2 
VALUES(1, 'USER01', 'PW01', '홍길동');
  
-- ID가 다르다
NSERT INTO USER_USED_UQ2
VALUES(2, 'USER02', 'PW01', '홍길동');
  
-- NAME이 다르다
INSERT INTO USER_USED_UQ2 
VALUES(1, 'USER01', 'PW01', '고길동');
  
-- ID와 NAME이 같다
INSERT INTO USER_USED_UQ2 
VALUES(1, 'USER01', 'PW01', '고길동');
-- SQL Error [1] [23000]: ORA-00001: 무결성 제약 조건
-- (KH.USER_ID_NAME_U)에 위배됩니다
  • (3) PRIMARY KEY
    • 테이블에서 한 행의 정보를 찾기 위해 사용할 컬럼을 의미
    • 테이블에 대한 식별자 역할을 한다
    • 한테이블당 한개만 설정 할 수 있다
    • 컬럼레벨, 테이블레벨 둘다 설정 가능
    • NOT NULL + UNIQUE 제약조건의 의미 ➡️ 중복되지 않는 값이 필수로 존재해야 한다
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

    CREATE TABLE USER_USED_PK(
  USER_NO NUMBER CONSTRAINT USER_NO_PK PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(20) NOT NULL,
  USER_NAME VARCHAR2(30)
  -- 테이블 레벨
  -- CONSTRAINT USER_NO_PK PRIMARY KEY(USER_NO)
);
  
INSERT INTO USER_USED_PK
VALUE(1, 'USER01', 'PW01', '홍길동');
  
INSERT INTO USER-USED_PK
VALUES(1,'USER02', 'PW02', '고길동');
--> ORA-00001: 무결성 제약 조건(KH.USER_NO_PK)에 위배됩니다
  
INSERT INTO USER-USED_PK
VALUES(NULL,'USER02', 'PW02', '고길동');
--> ORA-01400: NULL을 ("KH"."USER_USED_PK2"."USER_NO") 
-- 안에 삽입할 수 없습니다
  • (4) FOREIGN KEY
    • 참조된 다른 테이블의 컬럼이 제공하는 값만 사용할 수 있음
    • FOREIGN KEY 제약조건에 의해서 테이블간에 관계가 형성됨
    • 제공되는 값 외에는 NULL을 사용할 수 있음
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

    -- < 컬럼 레벨 >
`컬럼명 자료형`(`크기`) [CONSTRAINT `이름`] 
REFERENCES `참조할테이블명` [(`참조할컬럼`)][`삭제룰`] 
  
-- < 테이블 레벨 >
[CONSTRAINT `이름`] FOREIGN KEY(`적용할 컬럼명`) 
REFERENCES `참조할테이블명` [(`참조할 컬럼명`)] [`삭제룰`]
  
CREATE TABLE USER_GRADE(
  GRADE_CODE NUMBER PRIMARY KEY,
  GRADE_NAME VARCHAR2(30) NOT NULL
);
  
INSERT INTO USER_GRADE VALUES(10, '일반회원');
INSERT INTO USER_GRADE VALUES(20, '우수회원');
INSERT INTO USER_GRADE VALUES(30, '특별회원');
  
CREATE TABLE USER_USED_FK(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  GRADE_CODE NUMBER CONSTRAINT GRADE_CODE_FK REFERENCES USER_GRADE
  -- 컬럼명 미작성시 USER_GRADE 테이블의 PK를 자동 참조
);    
  
INSERT INTO USER_USED_FK 
VALUES(1, 'USER01', 'PW01', 10);
  
INSERT INTO USER_USED_FK 
VALUES(2, 'USER02', 'PW02', 20);
  
INSERT INTO USER_USED_FK 
VALUES(3, 'USER03', 'PW03', 30);
  
INSERT INTO USER_USED_FK 
VALUES(4, 'USER04', 'PW04', NULL);
  
INSERT INTO USER_USED_FK 
VALUES(5, 'USER05', 'PW05', 50);
-- ORA-02291:무결성 제약조건(KH.GRADE_CODE_FK)이 위배되었습니다 - 부모 키
-- 가 없습니다
--> 50이라는 값은 USER_GRADE 테이블의 GRADE_CODE 컬럼에서 제공하는
-- 값이 아니므로 외래키 제약 조건에 위배되어 오류 발생

    1
2

    < FROEIGN KEY 삭제 옵션 >
부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를 어떤식으로 처리할지에 대한 내용을 설정할 수 있다

    1
2
3
4
5
6
7
8
9

    1) ON DELETE RESTPICTED(`삭제 제한`)`로기본 지정되어 있음`
-- FOREIGN KEY로 지정된 컬럼에서 사용되고 있는 값일 경우 
-- 제공하는 컬럼의 값은 삭제하지 못한다
  
UPDATE USER_USED_FK SET GRADE_CODE = 30
WHERE GRADE_CODE = 20;
  
-- GRADE_CODE 중 20은 외래키로 참조되고 있지 않으므로 삭제 가능함
DELETE FROM USER_GRADE WHERE GRADE_CODE = 20;

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

    2) ON DELETE SET NULL : `부모키 삭제 시 자식키를 NULL로 변경하는 옵션`
  
CREATE TABLE USER_GRADE2(
  GRADE_CODE NUMBER PRIMARY KEY,
  GRADE_NAME VARCHAR2(30) NOT NULL
);    
  
CREATE TABLE USER_USED_FK2(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  GRADE_CODE NUMBER CONSTRAINT GRADE_CODE_FK REFERENCES USER_GRADE
  ON DELETE SET NULL
); 
  
INSERT INTO USER_USED_FK2
VALUES(1, 'USER01' 'PW01', 10);
  
INSERT INTO USER_USED_FK2
VALUES(2, 'USER02' 'PW02', 10);
  
INSERT INTO USER_USED_FK2
VALUES(3, 'USER03' 'PW03', 30);
  
INSERT INTO USER_USED_FK2
VALUES(4, 'USER04' 'PW04', NULL);
  
DELETE FROM USER_GRADE2
WHERE GRADE_CODE = 10;

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

    3) ON DELETE CASCADE : `부모키 삭제시 자식키도 함께 삭제됨`
-- 부모키 삭제시 값을 사용하는 자식 테이블의 컬럼에 해당하는 삭제됨
  
CREATE TABLE USER_GRADE3(
  GRADE_CODE NUMBER PRIMARY KEY,
  GRADE_NAME VARCHAR2(30) NOT NULL
);
  
INSERT INTO USER_GRADE3 VALUES(10, '일반회원');
INSERT INTO USER_GRADE3 VALUES(20, '우수회원');
INSERT INTO USER_GRADE3 VALUES(30, '특별회원');
  
CREATE TABLE USER_USED_FK3(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(20) NOT NULL,
  GRADE_CODE NUMBER
  CONSTRAINT GRADE_CODE_FK3 FOREIGN KEY(GRADE_CODE) 
  REFERENCES USER_GRADE3 (GRADE_CODE) ON DELETE CASCADE
);  
  
INSERT INTO USER_USED_FK3
VALUES(1, 'USER01' 'PW01', 10);
  
INSERT INTO USER_USED_FK3
VALUES(2, 'USER02' 'PW02', 10);
  
INSERT INTO USER_USED_FK3
VALUES(3, 'USER03' 'PW03', 30);
  
INSERT INTO USER_USED_FK3
VALUES(4, 'USER04' PW04', NULL);
  
DELETE FROM USER_GRADE3
WHERE GRADE_CODE = 10;
  • (5) CHECK
    • 컬럼에 기록되는 값에 조건을 설정할 수 있음
    • CHECK(컬럼명 비교연산자 비교값)
    • 주의할 점 : 비교값은 리터럴만 사용할 수 있다. 변하는 값 or 함수 사용 못함
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

    CREATE TABLE USER_USED_CHECK(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(20) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10) CONSTRAINT GENDER_CHECK 
  CHECK ( GENDER IN ('남', '여') )
);
  
INSERT INTO USER_USED_CHECK
VALUES(1, 'USER01', 'PW01', '홍길동', '남');
  
INSERT INTO USER_USED_CHECK
VALUES(2, 'USER02', 'PW02', '고길동', '남자');
-- ORA-02290: 체크 제약조건(KH.GENDER_CHECK)이 위배되었습니다
-- CHECK 조건에 의해 GENDER에 남, 여만 기록 가능
  • (6) SUBQUERY를 이용한 테이블 생성법
    • 컬럼명, 데이터 타입, 값이 복사되고, 제약조건은 NOT NULL만 복사된다
    1
2
3
4
5
6
7
8
9
10
11
12
13

    1) `테이블 전체 복사`
CREATE TABLE EMP_COPY
AS SELECT * FROM EMPLOYEE;
  
2) JOIN `후 원하는 컬럼만 테이블로 복사`
CREATE TABLE EMP_COPY
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING (JOB_CODE);
--> 서브쿼리로 테이블 생성시
-- 테이블의 형태(컬럼명, 데이터 타입) + NOT NULL 제약조건만 복사
-- 다른 제약조건, COMMENT는 복사되지 않는다.
KH 학원 공부, DB
DB ORACLE