[29일 차] : Oracle - DDL(CREATE)

서하루·2022년 11월 22일
0

(2) Oracle 공부기록

목록 보기
7/11

[학습목표]

  • 객체생성구문 < CREATE >
  • 제약조건 < CONSTRAINTS >
    - NOT NULL / UNIQUE / CHECK / PRIMARY KEY / FOREIGN KEY
  • 기본값 < DEFAULT >

    ✅ DDL (DATA DEFINTION LANGUAGE)

    - 데이터 정의 언어

    - 오라클에서 제공하는 객체를 새롭게 만들고(CREATE)

    - 구조를 변경하고 (ALTER)

    - 구조 자체를 삭제(DROP)하는 언어

    => 📢 실제 데이터 값이 아닌, 구조 자체를 정의하는 언어

    📒1. CREATE [테이블생성]

    CREATE : 객체를 새로이 생성하는 구문
    테이블이란 행(ROW)과 열(COLUMN)로 구성되는 가장 기본적인 데이터베이스 객체
[표현법]

CREATE TABLE 테이블명 (
	컬럼명 자료형(크기)
    컬럼명 자료형(크기)
    ....
);

📢 * 자료형

  • 문자 (CHAR(바이트크기) | VARCHAR2(바이트크기))
CHAR : 최대 2000바이트까지 지정 가능 / 고정길이 / 고정된 글자수의 데이터만이 담길 경우 사용함 EX) 주민등록번호
((지정한 크기보다 더 적은 값이 들어오면 빈 공간으로 공백을 채움))
VARCHAR2 : 최대 4000바이트까지 지정 가능 / 가변길이 / 몇글자의 데이터가 들어올지 모를 경우 사용 EX) 이메일
((담긴 값에 따라서 공간의 크기가 맞춰짐))

  • 숫자 (NUMBER)

  • 날짜 (DATE)

회원에 대한 데이터를 담기위한 테이블 MEMBER 생성구문

📒 1-2. 컬럼에 주석달기 (컬럼에 대한 설명)

[표현법]

COMMENT ON COLUMN 테이블명.컬럼명 IS '내용';
---------------------------------------------

COMMENT ON COLUMN MEMBER.MEM_NO IS '회원번호';
COMMENT ON COLUMN MEMBER.MEM_ID IS '아이디';
COMMENT ON COLUME MEMBER.GENDER IS '성별(남/여)';

📒 1-3. 생성한 테이블에 데이터 추가 [INSERT]

[표현법]

INSERT INTO 테이블명 VALUES(값,값,값 ''')

--------------------------------------------

INSERT INTO MEMBER VALUES(1, 'user01', 'pass01', '홍길동', '남', 
'010-1234-5678, 'asd@naver.com, '22/11/22');

오류구문
- NOT enough values : 만든 컬럼 수보다 적게 입력했을 경우
- too many values : 만든 컬럼 수보다 많이 입력했을 경우

📒 2. 제약조건 [CONSTRAINTS]

📢 내가 원하는 데이터값만 유지하기 위해서 특정 컬럼에 부여하는 제약
종류 : NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY

🖊️ 2-1. NOT NULL 제약조건

- 해당 컬럼에 반드시 값이 존재해야만 하는 경우(즉, 해당컬럼에 절대 NULL이 들어와서는 안되는 경우)
- 삽입/수정시 NULL값을 허용하지 않도록 제한함
- 제약조건을 부여하는 방식은 2가지 [컬럼레벨방식 / 테이블레벨방식]

=> NOT NULL 제약조건은 컬럼레벨방식만 가능 !

CREATE TABLE MEM_NOTNULL (
    MEM_NO NUMBER NOT NULL,
    MBM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3), 
    PHONE VARCHAR(13),
    EMAIL VARCHAR (50)
);

✨ 회원번호, 회원아이디, 비밀번호가 NULL을 허용하지 않게 NOT NULL 제약조건 작성함 

INSERT INTO MEM_NOTNULL VALUES(1, 'user01','pass01','홍길동','남',null,null);

INSERT INTO MEM_NOTNULL VALUES(2, 'user02', NULL, 
'김말순', '여', NULL, 'AAA@NAVER.COM');
--> 의도했던대로 오류남 ! (NOT NULL 제약조건에 위배되어 오류발생)

✨ 해당 INSERT구문은 비밀번호의 값을 NULL로 작성하여 제약조건 위배되어 오류발생

INSERT INTO MEM_NOTNULL VALUES(2, 'user01', 'pass02', '김말순', '여', NULL, 'AAA@NAVER.COM');
--> 아이디가 중복됐지만 잘 추가됨..

✨ 아이디가 중복됐지만 오류없이 데이터가 잘 추가됐다. 이런경우 해결하는 방법은 UNIAUE 제약조건을 사용해야한다. 밑에서 계속 !

🖊️ 2-2. UNIQUE 제약조건

- 해당 컬럼에 중복된 값이 들어가서는 안될 경우
- 컬럼값에 중복을 제한하는 제약조건
- 삽입/수정시 기존에 있는 데이터값 중 중복값이 있을 경우 오류발생@
📢 컬럼레벨방식으로 NOT NULL구문 작성

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, -- 컬럼레벨방식
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50)    
);

📢 테이블레벨방식으로 UNIQUE구문 작성
CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    UNIQUE(MEM_ID) -- 테이블레벨방식

INSERT INTO MEM_UNIQUE VALUES(1, 'user01', 'pass01', 
'홍길동', null, null, null);
INSERT INTO MEM_UNIQUE VALUES(2, 'user01', 'pass02', 
'강개똥', null, null, null);
✨ 아이디에 UNIQUE 제약조건을 부여했는데, 중복값이므로 오류뜸 !

☝️ 제약조건 부여시, 제약조건명까지 지어주는 방법

  • 컬럼레벨방식
    CREATE TALBLE 테이블명 (
    컬럼명 자료형 [CONSTRAINT 제약조건명] 제약조건,
    컬럼명 자료형
    ''''
    );
  • 테이블레벨방식
    CREATE TABLE 테이블명 (
    컬럼명 자료형,
    컬럼명 자료형,
    '''
    [CONSTRAINT 제약조건명] 제약조건(컬럼명)
    );
CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER CONSTRAINT MEMNO_NN NOT NULL,
    MEM_ID VARCHAR2(20) CONSTRAINT MEMID_NN NOT NULL,
    MEM_PWD VARCHAR2(20) CONSTRAINT MEMPWD_NN NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
   CONSTRAINT MEMID_UK UNIQUE(MEM_ID) -- 테이블레벨방식
  );
  
INSERT INTO MEM_UNIQUE VALUES(2, 'user02', 'pass02', 
'강개똥', null, null, null);
INSERT INTO MEM_UNIQUE VALUES(3, 'user03', 'pass03', 
'홍길녀', 'ㅇ',null, null);
✨ 성별에 유효한 값이 아닌 데이터가 입력되도 INSERT가 됨 . 이를 해결하기 위해서는? 아래에서 알아보자 ! CHECK

🖊️ 2-3. CHECK(조건식) 제약조건

- 해당 컬럼에 들어올 수 있는 값에 대한 조건 제시
- 해당 조건에 만족하는 데이터값만 담길 수 있음

CHECK(컬럼명 IN (조건식))

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    PEP_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남','여')), -- 컬럼레벨방식
    PHONE VARCHAR2(13)<,
    EMAIL VARCHAR2(50)
    -- , CHECK (GENDER IN ('남','여')) -- 테이블레벨방식
    );
    
✨ GENDER 컬럼에 남 또는 여 라는 CHECK 조건식 제약조건을 걸어놨다.
INSERT INTO MEM_CHECK
VALUES (2,'user02', 'pass01' ,'홍길녀', 'o', null, null);
--> check 제약조건에 위배됐기에 오류 발생 

INSERT INTO MEM_CHECK
VALUES (2,'user03', 'pass03' ,'강개순', '여', null, null);
--> 회원번호가 동일해서 성공적으로 insert 됨
--> 회원번호가 동일하면 안됨 ~ ~ ~

🖊️ 2-4. PRIMARY KEY 제약조건

- 테이블에서 각 행들을 식별하기 위해 사용될 컬럼에 부여(식별자 역할)
- PRIMARY KEY 제약조건을 부여하면 그 컬럼은 자동으로 NOT NULL + UNIQUE를 의미함
- 한 테이블당 한 개만 설정 가능함 !

컬럼레벨방식 : 컬럼명 자료형 PRIMARY KEY
테이블컬럼방식 : PRIMARY KEY(컬럼명)

<CREATE TABLE MEM_PRI(
    MEM_NO NUMBER CONSTRAINT MEMNO_PK PRIMARY KEY, -- 컬럼레벨방식
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    PEP_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남','여')),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50)
    --,CONSTRAINT MEMNO_PK PRIMARY KEY(MEE_NO) 테이블컬럼방식
    );
    
 INSERT INTO MEM_PRI
VALUES(1, 'user01', 'pass01', '강개순', '여', '010-1234-5678', null);
--> 입력 완료

INSERT INTO MEM_PRI
VALUES(1, 'user02', 'pass02', '이순신', '남', null, null);
--> 기본키 컬럼에 중복값을 담으려고 할 때 (unique 제약조건 위배)

INSERT INTO MEM_PRI
VALUES(null, 'user02', 'pass02', '이순신', '남', null, null);
--> 기본키 컬럼에 null을 담으려고 할 때 오류발생 (not null 제약조건 위배)

INSERT INTO MEM_PRI
VALUES(2, 'user02', 'pass02', '이순신', '남', null, null);
--> 입력 완료

🖊️ 2-5. FOREIGN KEY 제약조건

- 다른 테이블에 존재하는 값만 들어와야하는 특정 컬럼에 부여하는 제약조건 (= 다른 테이블을 참조한다고도 표현함)

컬럼레벨방식 : 컬럼명 자료형 REFERENCES 참조할테이블명 [(참조할컬럼명)]
테이블레벨방식 : FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명 [(참조할컬럼명)]

☝️ 참조할 컬럼명 생략시, 참조할테이블의 PRIMARY KEY로 지정된 컬럼으로 자동 매칭됨 !

CREATE TABLE MEM_GRADE(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(30) NOT NULL
    );
    
INSERT INTO MEM_GRADE VALUES(10, '일반회원');
INSERT INTO MEM_GRADE VALUES(20, '우수회원');
INSERT INTO MEM_GRADE VALUES(30, '특별회원');


CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남','여')),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    GRADE_ID NUMBER REFERENCES MEM_GRADE(GRADE_CODE) --> 컬럼레벨방식
    -- , FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE)-> 테이블레벨방식
    
 
 INSERT INTO MEM 
VALUES(1,'user01', 'pass01', '홍길순', '여', null, null, null);
--> 외래키 제약조건이 부여된 컬럼에 기본적으로 NULL 허용됨

INSERT INTO MEM 
VALUES(2,'user02', 'pass02', '김말똥', null , null, null, 10);

INSERT INTO MEM 
VALUES(3,'user03', 'pass03', '강개순', null , null, null, 40);
-- 오류발생!  parent key not found
✨ 외래키 제약조건 설정 시, 참조하는 테이블이 부모테이블(MEM_GRADE) / 참조받는 테이블이 자식테이블(MEM)이 됨
-- 즉, 부모테이블에 40이라는 값이 없기에 찾을 수 없다는 오류문구가 뜨는 것 ~!

🖊️ 2-6. 외래키 제약조건 부여시 "삭제옵션" 지정

- 삭제옵션 : 부모테이블의 데이터 삭제시, 그 데이터를 사용하고 있는 자식테이블의 값을 어떻게 처리할것인지
  • ON DELETE RESTRICTED(기본값) : 삭제제한옵션, 자식데이터로 쓰이는 부모데이터는 아예 삭제 불가
  • ON DELETE SET NULL : 부모데이터 삭제시 해당 데이터를 쓰고있는 자식데이터의 값 NULL로 변경
  • ON DELETE CASCADE : 부모데이터 삭제시 새당 데이터를 쓰고있는 자식데이터도 같이 삭제시킴

ON DELETE SET NULL 사용

결과값은 김말똥, 홍길동의 GRADE_ID가 NULL로 조회됨

ON DELETE CASCADE 사용

결과값은 등급이 10번인 김말똥, 홍길동이 데이터에서 삭제됨

🖊️ 3. 기본값 [DEFAULT] 제약조건 아님

- 컬럼을 선정하지 않고 INSERT시 NULL이 아닌 기본값을 INSERT하고자 할 때, 셋팅해줄 수 있는 값

컬럼명 자료형 DEFAULT 기본값 [제약조건]

CREATE TABLE MEMBER(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_NAME VARCHAR2(20) NOT NULL,
    MEM_AGE NUMBER,
    HOBBY VARCHAR2(20) DEFAULT '없음',
    ENROLL_DATE DATE DEFAULT SYSDATE
);


-- 방법1. 테이블의 모든 컬럼값을 다 제시하여 INSERT하는 방법
--       INSERT INTO 테이블명 VALUES(값, 값 ''');
INSERT INTO MEMBER VALUES(1, '강길동', 20, '운동', '22/10/30');
INSERT INTO MEMBER VALUES(2, '홍길순', NULL,NULL,NULL);
INSERT INTO MEMBER VALUES(3, '김말똥', DEFAULT ,DEFAULT, DEFAULT);


-- 방법2. 테이블의 특정 컬럼만 지정해서 값을 제시하는 방법
-- 선택 되지 않은 컬럼에는 기본적으로 NULL이 들어감 (단, DEFALUT값이 부여되있는 경우 -> DEFAULT값이 들어감
-- INSERT INTO 테이블명 (컬럼명, 컬럼명) VALUES(값, 값);
INSERT INTO MEMBER(MEM_NO, MEM_NAME) VALUES(4,'강개순');


1. 테이블 생성구문

CREATE TABLE 테이블명 (
	컬럼명 자료형
    컬럼명 자료형
);

2. 컬럼명에 주석다는 구문

COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';

3. 제약조건에 이름부여 구문
CONSTRAINT 이름

4. 데이터 추가 구문

INSERT INTO 테이블명 VALUES();

5. CHECK 구문 EX) EMPLOYEE 테이블의 GENDER 컬럼의 데이터가 'Y'또는 'N'

CREATE TABLE EMPLOYEE (
	GENDER CHECK(GENDER IN 'Y','N'));
);

6. 외래키 구문 EX) EMPLOYEE테이블의 EMP_NO가 DEPT_CODE의 DEPT_NO를 참조하도록

CREATE TABLE EMPLOYEE (
	EMP_NO FO REFRENCES DEPT_CODE(DEPT_NO)

);

7. 6번 문제에 NULL값이 있으면 부모객체와 자식객체 모두 삭제하는 구문

CREATE TABLE EMPLOYEE (
	EMP_NO FO REFRENCES DEPT_CODE(DEPT_NO) ON DELETE CASCADE
    
    EMP_NO FO REFRENCES DEPT_CODE(DEPT_NO) ON DELETE SET NULL - NULL 값으로 바꾸는거

);
profile
서하루
이전 포스트

[27일 차] : Oracle - SELECT문(5), 서브쿼리

다음 포스트

[30일 차] : Oracle - DML(INSERT, UPDATE DELETE) , DDL(ALTER, DROP)

0개의 댓글