06_OracleDB SQL : DDL(CREATE TALBE) 및 제약조건_테이블만들기

---

DDL(DATA DEFINITION LANGUAGE)

 

데이터 정의 언어
  객체(OBJECT)를 만들고(CREATE), 수정(ALTER)하고, 삭제(DTOP)하는 구문

 

---

 

 

1 . 테이블 생성하기

CREATE TABLE 테이블명 (

         컬럼명 자료형(크기),

         컬럼명 자료형(크기),...);

--예시
CREATE TABLE MEMBER(
  MEMBER_ID VARCHAR2(20),
  MEMBER_PWD VARCHAR2(20),
  MEMBER_NAME VARCHAR2(20)
  );
  
--참고) 데이터 타입 : 숫자 NUMBER, 날짜  DATE, 문자 CHAR(고정길이), VARCHAR2(가변길이), LONG

 

 

 

 

---

 

2 . 컬럼에 주석 달기

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 '회원이름';

 

 

---

 

3 . 제약 조건

• 기본설정만으로 데이터 무결성을 보장할 수 없기 때문에 제약 조건을 만들어서 미리 방지해야 한다.
• 테이블 작성 시 컬럼의 값 기록에 대한 제약 조건을 설정할 수 있다.
• 데이터 무결성 보장과 입력 및 수정하는 데이터에 문제가 없는지 자동으로 검사하는 목적을 가진다.

3 - 1 제약 조건 종류

        (1). NOT NULL

        (2) . UNLQUE

        (3) . CHECK 

        (4) . PRIMARY KEY

        (5) . FOREIGN KEY

 

 

• NOT NULL  제약 조건 : 해당 컬럼에 값이 반드시 기록 되어야하는 경우 사용(NULL 값 허용x) , 

CREATE TABLE USER_NOCONS(
   USER_NO NUMBER NOT NULL, <- "컬럼 레벨"에서만 제한 
   USER_ID VARCHAR2(20) NOT NULL,
   USER_PWD VARCHAR2(30) NOT NULL,
   USER_NAME VARCHAR2(30),
   GENDER VARCHAR2(10),
   PHONE VARCHAR2(30),
   EMAIL VARCHAR2(50)
   "테이블 레벨"에서는 설정 안됨.
 );

 

• UNIQUE 제약 조건 :  컬럼의 입력 값에 대해 중복을 제한함

CREATE TABLE USER_UNIQUE(
   USER_NO NUMBER, 
   USER_ID VARCHAR2(20)UNIQUE NOT NULL, "컬럽 레벨"에서 설정 가능
   USER_PWD VARCHAR2(30) NOT NULL,
   USER_NAME VARCHAR2(30),
   GENDER VARCHAR2(10),
   PHONE VARCHAR2(30),
   EMAIL VARCHAR2(50),
   UNIQUE(USER_ID) "테이블 레벨"에서도 설정 가능
   UNIQUE(USER_ID, USER_PWD) 컬럼 여러 개를 묶어서도 가능. "테이블 레벨" 에서만 가능
   UNIQUE(컬럼1,컬럼2) 각각의 컬럼에 제약 조건 주는게 아니므로 헷갈리지 말기!
 );

 

 

 

• CHECK 제약 조건 : 범위의 값 조건을 설정 할 수 있음 

CHECK(컬럼명 비교연산자 비교값)
비교값은 리터럴만 사용 가능!

CREATE TABLE USER_CHECK(
   USER_NO NUMBER, 
   USER_ID VARCHAR2(20) UNIQUE,
   USER_PWD VARCHAR2(30) NOT NULL,
   USER_NAME VARCHAR2(30),
   GENDER VARCHAR2(10), CHECK(GENDER IN('남','여')), (젠더라는 값에 '남' 또는 '여' 외의 값 입력 불가)
   PHONE VARCHAR2(30),
   EMAIL VARCHAR2(50)
 );
 
 테이블 레벨에서도 설정 가능
 CREATE TABLE TEST_CHECK(
   TEST_NUMBER NUMBER,
   CONSTRAINT DK_TEST_NUMBER CHECK(TEST_NUMBER >0)
 );

 

 

 

• PRIMARY KEY(기본키) 제약 조건 : 테이블에 대한 식별자 역할, NOT NULL과 NUIQUE 조건이 내포되어 있음

(1) 한 테이블 당 한개만 설정 가능
(2) 컬럼 레벨, 테이블 레벨 둘다 설정 가능
(3) 여러 개의 컬럼을 묶어서 설정 가능(복합키)

CREATE TABLE USER_PRIMARYKEY(
   USER_NO NUMBER PRIMARY KEY, <- PK 설정
   USER_ID VARCHAR2(20)
   USER_PWD VARCHAR2(30) 
   USER_NAME VARCHAR2(30),
   GENDER VARCHAR2(10),
   PHONE VARCHAR2(30),
   EMAIL VARCHAR2(50)
    PRIMARY KEY(USER_NO, USER_ID) <- 복합키로 설정
 );

 

 

 

• 제약조건 이름 설정 방법 : 제약조건 앞에 CONSTRAINT 제약조건이름 

CREATE TABLE CONS_NAME(
  TEST_DATA1 VARCHAR2(20) CONSTRAINT NN_TEST_DATA1 NOT NULL,<- 제약조건 앞에 CONSTRAINT 제약조건이름 작성 
  TEST_DATA2 VARCHAR2(20) CONSTRAINT UN_TEST_DATA2 UNIQUE,
  TEST_DATA3 VARCHAR2(30),
  CONSTRAINT UN_TEST_DATA3 UNIQUE(TEST_DATA3) <- 테이블 레벨에서도 동일
 );
 
 ◇ 제약 조건 검색시 설정한 제약 조건명으로 검색 할 수 있다.
  SELECT
      UCC.TABLE_NAME
     ,UCC.COLUMN_NAME
     ,UC.CONSTRAINT_TYPE
  FROM USER_CONSTRAINTS UC 
      ,USER_CONS_COLUMNS UCC
 WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
   AND UCC.CONSTRAINT_NAME = 'UN_TEST_DATA2';

 

 

• FOREIGN KEY(외부키/외래키) 제약 조건 : 참조 된 다른 테이블에서 제공하는 값과 NULL만 사용 가능, 참조 무결성을 위배하지 않기 위해서 사용하며 해당 조건에 의해서 테이블 간의 관계가 형성된다.

부모 테이블 생성 -> INSERT -> 자식테이블 생성 ->INSERT

1. 컬럼 레벨일 경우
컬럼명 자료형(크기) [CONSTRAINT 제약조건명] REFERENCES 참조할테이블명 [(참조할컬럼)] [삭제룰]
참조할 컬럼 미작성시 참조컬럼의 PK를 참조컬럼으로 사용
FK 제약 조건에 부모키가 없으면 삽입 실패 

EX) CONSTRAINT FK_GRADE_CODE FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE(GRADE_CODE)

2. 테이블 레벨인 경우
[CONSTARINT 제약조건명] FOREIGN KEY(적용할컬럼명) REFERENCES 참조할 테이블명 [(참조할컬럼)][삭제룰]

 

• 삭제 옵션 :  부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를 어떤 식으로 처리할 것인지에 대한 내용을 설정할 수 있다.

◇ 작성 방법
DELETE
   FROM 부모테이블명
  WHERE 삭제할 컬럼명 = 삭제할 컬럼값;


(1) 기본 삭제 옵션 : ON DELETE RESTRICT 로 삭제 룰이 기본 지정 되어 있기 때문에
    FOREIGN KEY로 지정 된 컬럼에서 사용 되고 있는 값일 경우 제공하는 컬럼의 값은 삭제하지 못함

(2) 자식 레코드로 사용되지 않는 값는 삭제 가능하다.

(3) ON DELETE SET NULL 삭제 옵션 : 부모 키를 삭제 시 자식 키를 NULL로 변경하는 옵션
    적용 방법 : FOREIGN KEY 작성시 맨 뒤에 ON DELETE SET NULL 작성
EX) FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE2(GRADE_CODE) ON DELETE SET NULL

(4) ON DELETE CASCADE  삭제 옵션: 부모 키 삭제 시 자식 키를 가진 행도 함께 삭제
    적용 방법 : FOREIGN KEY 작성시 맨 뒤에 ON DELETE CASCADE 작성

 

 

 

---

 

3 - 2 테이블의 제약 조건 조회 방법

(1) USER_CONSTRAINTS 이용하여 현재 계정에서 있는 제약 조건 테이블 조회
  SELECT
         UC.*
    FROM USER_CONSTRAINTS UC; 
 
 
(2) USER_CONS_COLUMNS 이용하여  제약 조건이 걸린 컴럼별로 검색할 수 있는 테이블 조회
   SELECT
         UCC.*
    FROM USER_CONS_COLUMNS UCC;
   
   
(3) JOIN해서 테이블의 제약 조건 조회
 SELECT
         UC.*
       , UCC.*
   FROM USER_CONSTRAINTS UC
   JOIN USER_CONS_COLUMNS UCC ON(UC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME)
  WHERE UC.TABLE_NAME = 'USER_NOTNULL';
  
  
(4) 제약 조건명을 이용해서 조회
SELECT
      UCC.TABLE_NAME
     ,UCC.COLUMN_NAME
     ,UC.CONSTRAINT_TYPE
  FROM USER_CONSTRAINTS UC 
      ,USER_CONS_COLUMNS UCC
 WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
   AND UCC.CONSTRAINT_NAME = 'SYS_C007353'; -> '무결성 제약조건명 쓰기 '

 

 

 

---

4 . 생성한 테이블에 데이터 삽입 (INSERT)

1. 테이블의 일부 컬럼에 INSERT할 때
INSERT 
   INTO 테이블명
 (
   칼럼명
 )
 VALUES
 (
   데이터
 );
 
 
테이블 생성
 CREATE TABLE USER_NOCONS(
   USER_NO NUMBER,
   USER_ID VARCHAR2(20),
   USER_PWD VARCHAR2(30),
   USER_NAME VARCHAR2(30),
   GENDER VARCHAR2(10),
   PHONE VARCHAR2(30),
   EMAIL VARCHAR2(50)
 );
 
데이터 삽입
 INSERT 
   INTO USER_NOCONS 
 (
   USER_NO
,  USER_ID
,  USER_PWD
,  USER_NAME
,  GENDER
,  PHONE
,  EMAIL
 )
 VALUES
 (
   1
,  'USERE01'
,  'PASS01'
,  '홍길동'
,  '남'
,  '010-1234-5678'
,  'hong123@greedy.com'
 );
 
 
2. 테이블의 모든 컬럼에 INSERT할 때
     INSERT
       INTO 테이블명
     VALUES (
              데이터
            , 데이터
            , 데이어
            );

 

5 . 서브쿼리를 이용한 테이블 생성

컬러명, 데이터 타입, 값이 복사되고, 제약조건은 NOT NULL만 복사 됨. JOIN 가능

CREATE TABLE EMPLOYEE_COPY
 AS
 SELECT
       E.*
   FROM EMPLOYEE E;
   
 
CREATE TABLE EMPLOYEE_COPY2
AS
SELECT
       E.EMP_ID
     , E.EMP_NAME
     , E.SALARY
     , D.DEPT_TITLE
     , J.JOB_NAME
  FROM EMPLOYEE E
  LEFT JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
  LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE);