DDL

데이터 정의 언어로 객체를 만들고 수정, 삭제하는 구문을 말한다. CREATE, ALTER, DROP을 사용한다.

오라클에서 객체는 테이블, 뷰, 시퀀스, 인덱스, 함수, 유저 등이 존재한다.

CREATE

데이터베이스 객체를 생성하는 구문으로 테이블을 추가하는 경우 CREATE TABLE 테이블명(컬럼명 자료형(크기), …); 형태로 작성한다. 테이블명은 대소문자를 구분하지 않는다.

테이블 생성은 데이터를 저장할 수 있는 공간을 생성하는 것이다. 테이블을 생성하기 위해 저장 공간을 확보하는데 이때 데이터의 타입이 필요하다.

문자형 타입 : CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB
숫자형 타입 : NUMBER
날짜형 타입 : DATE, TIMESTEMP

문자형 타입

CHAR(길이) : 고정형 문자열 저장타입으로 길이만큼 공간을 확보하고 저장한다. 최대 2000바이트까지 가능하다.
VARCHAR2(길이) : 가변형 문자열 저장타입. 저장되는 데이터만큼 공간을 확보해서 저장한다. 2000바이트까지 가능.

CREATE TABLE TBL_STR(
    A CHAR(6),
    B VARCHAR2(6),
    C NCHAR(6),
    D NVARCHAR2(6)
);

INSERT INTO TBL_STR VALUES('ABC','ABC','ABC','ABC');
INSERT INTO TBL_STR VALUES('가나','가나','가나','가나');
--⚠️INSERT INTO TBL_STR VALUES('가나','가나다','가나','가나');

💡 마지막 코드에서는 데이터 길이에 대한 오류가 발생한다. 바이트 단위로 크기 체크하기 때문이다.
⚠️ORA-12899: "BS"."TBL_STR"."B" 열에 대한 값이 너무 큼(실제: 9, 최대값: 6)

숫자형 타입

NUNBER : 실수, 정수 모두 저장 가능
NUMBER(PRECISIONE,SCALE): 저장할 범위를 설정

PERCISION : 표현할 수 있는 전체 자리수(1~38)
SCALE : 소수점 이하의 자리수(-84~127)

CREATE TABLE TBL_NUM(
    A NUMBER,
    B NUMBER(5),
    C NUMBER(5,1),
    D NUMBER(5,-2)
);
SELECT * FROM TBL_NUM;
INSERT INTO TBL_NUM VALUES(1234.567,1234.567,1234.567,1234.567);
--⚠️INSERT INTO TBL_NUM VALUES(123456.123,12345.123,12345.123,0);
-->ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.
-->B컬럼에는 소숫점자리는 자르고 정수 자리만 저장한다.
-->C컬럼은 소수점 자리 길이까지 지정했기 때문에 해당 길이도 지켜서 저장해야한다.
INSERT INTO TBL_NUM VALUES(123456.123,12345.123,1234.123,1234567);
-->-2하면 정한 자릿수+마이너스한 자릿수 만큼 길이를 작성할 수 있다.
INSERT INTO TBL_NUM VALUES('1234.567','1234.567','1234.567','1234.567');
-->문자열로 작성해도 자동 형변환

💡 자릿수 지정은 해당 자릿수를 초과하는 범위는 절대 받지 않되 C 컬럼처럼 정수 자릿수를 5, 소수 자릿수를 1로 정한 경우 1234.123을 입력했을 때는 소수는 반올림해서 한자리까지만 끊어내고 정수 자리는 길이를 초과하지 않았기 때문에 제대로 출력할 수 있다.

날짜형 자료형

DATE, TIMESTAMP

CREATE TABLE TBL_DATE(
    BIRTHDAY DATE,
    TODAY TIMESTAMP
);
INSERT INTO TBL_DATE VALUES('98/08/03','98/01/26 15:30:30');
INSERT INTO TBL_DATE VALUES(TO_DATE('98/08/03','RR/MM/DD')
				,TO_TIMESTAMP('98/01/26 15:30:30','RR/MM/DD HH24:MI:SS'));
SELECT * FROM TBL_DATE;

테이블 작성

--회원을 저장하는 테이블
--이름(문자), 회원번호(숫자||문자), 아이디(문자), 패스워드(문자),
--이메일(문자), 나이(숫자), 가입날짜(날짜)
CREATE TABLE MEMBER (
        MEMBER_NAME VARCHAR(20),
        MEMBER_NO NUMBER,
        MEMBER_ID VARCHAR(15),
        MEMBER_PW VARCHAR2(20),
        EMAIL VARCHAR2(30),
        AGE NUMBER,
        ENROLL_DATE DATE
);

테이블 코멘트

생성된 테이블 컬럼에는 설명을 작성할 수 있다.

COMMENT ON COLUMN 테이블명.컬럼명 IS '테이블 코멘트 내용';

COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원 이름 최소 2글자 이상 저장';
COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원 아이디 최소 4글자 이상';
COMMENT ON COLUMN MEMBER.MEMBER_PW IS '회원 비밀번호 최소 8글자 이상';

--컬럼 코멘트 확인 방법
SELECT *
FROM USER_COL_COMMENTS
WHERE TABLE_NAME='MEMBER';
-->원하는 테이블의 코멘트만 보고싶을 때 WHERE절 작성. 작성하지 않으면 전체 테이블이 조회.

--테이블에 코멘트 작성
COMMENT ON TABLE MEMBER IS '회원 정보 저장';

테이블 코멘트 확인 명령어 실행 후 화면

제약 조건(CONTSRAINTS)

테이블 생성 시 각 컬럼에 기록될 데이터에 대해서 제약 조건을 설정할 수 있다. 데이터의 무결성 보장을 주 목적으로 하기 때문에 입력 데이터에 문제가 없는지에 대한 검사나 데이터 수정, 삭제 가능 여부 검사 등을 위해서 사용된다.

제약 조건은 해당 명령어를 CREATE문 안에 생성하면서 컬럼 뒤에 바로 작성하는 방법과 ALTER 명령어를 사용해서 생성된 테이블에 추가하는 방법이 있다. 오라클이 제공하는 제약 조건은 아래와 같은 것들이 있다.

NOT NULL(C)

지정된 컬럼에 NULL값을 허용하지 않는다. 제약 조건을 설정하지 않고 만들어진 컬럼은 기본적으로 NULL값을 저장하게 되어있다.

--ID, PWD는 NULL을 허용하면 안 되는 컬럼
CREATE TABLE NN_MEMBER(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(20) NOT NULL,
    MEMBER_PWD VARCHAR2(20) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER
);

--INSERT INTO NN_MEMBER VALUES(NULL,NULL,NULL,NULL,NULL);
--⚠️ORA-01400: NULL을 ("BS"."NN_MEMBER"."MEMBER_ID") 안에 삽입할 수 없습니다
INSERT INTO NN_MEMBER VALUES(NULL,'ADMIN','1234',NULL,NULL);
-->NOT NULL 제약 조건이 없는 컬럼은 NULL값을 허용한다.

💡 NOT NULL 제약 조건은 해당 컬럼 뒤에만 작성할 수 있고 컬럼 맨 마지막인 테이블 레벨에서는 작성할 수 없다.

UNIQUE(U)

지정된 컬럼에 중복 값을 허용하지 않는다. 컬럼이 유일한 값을 유지해야 될 때 사용한다.

CREATE TABLE NQ_MEMBER(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(20) UNIQUE,
    MEMBER_PWD VARCHAR2(20) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER
);
SELECT * FROM NQ_MEMBER;
INSERT INTO NQ_MEMBER VALUES('1','ADMIN','1234','관리자',44);
--INSERT INTO NQ_MEMBER VALUES('2','ADMIN','1234','유저1',33);
--⚠️ORA-00001: 무결성 제약 조건(BS.SYS_C007320)에 위배됩니다

💡 UNIQUE 제약 조건에서 NULL값 처리
NULL값에 대한 제약 조건을 따로 정하지 않았기 때문에 값이 들어간다. NULL은 동등 비교 자체가 불가능하기 때문에 중복 처리를 따로 구해내지 못한다.
ID를 중복되지 않고 NULL값을 허용하지 않게 하려면 MEMBER_ID VARCHAR2(20) UNIQUE NOT NULL 처럼 제약 조건을 이어서 작성해주면 된다.

테이블 레벨에서 UNIQUE 활용

UNIQUE는 테이블 레벨에서 작성 가능하다. 때문에 테이블 레벨에서 다수 컬럼에 한 번에 제약 조건 설정할 수 있다.
단, 다수 컬럼의 값이 모두 일치해야 중복 값으로 인식한다. ID와 NO에 같이 제약 조건을 주는 경우 ID, NO이 다 같아야 중복 값으로 인식하고 하나라도 다르면 중복 값이 아닌 걸로 인식하게 된다.

CREATE TABLE NQ_MEMBER4(
    MEMBER_NO NUMBER,
    MEMBER_ID VARCHAR2(20) NOT NULL,
    MEMBER_PWD VARCHAR2(20) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER,
    UNIQUE(MEMBER_ID,MEMBER_NAME)
);
INSERT INTO NQ_MEMBER4 VALUES(1,'ADMIN','1234','관리자',44);
INSERT INTO NQ_MEMBER4 VALUES(1,'ADMIN1','1234','관리자',44); -->ID이 다름
INSERT INTO NQ_MEMBER4 VALUES(3,'ADMIN','1234','유저',44); -->NO가 다름

PRIMARY KEY(P)/PK

데이터를 구분할 수 있는 컬럼에 설정하는 제약 조건으로 ROW를 구분해낼 수 있는 컬럼을 말한다. 각 ROW를 구분해야 하기 때문에 PRIMARY KEY를 설정한 컬럼의 값은 기본적으로 NOT NULL, UNIQUE 설정된다. 일반적으로 하나의 테이블에 하나의 PRIMARY KEY를 무조건 설정한다.

CREATE TABLE PK_MEMBER(
    MEMBER_NO NUMBER PRIMARY KEY,
    MEMBER_ID VARCHAR2(20) NOT NULL,
    MEMBER_PWD VARCHAR2(20) NOT NULL,
    MEMBER_NAME VARCHAR2(10),
    MEMBER_AGE NUMBER,
    UNIQUE(MEMBER_ID)
);
--INSERT INTO PK_MEMBER VALUES(NULL,'ADMIN','1234','관리자',40);
-->⚠️ORA-01400: NULL을 ("BS"."PK_MEMBER"."MEMBER_NO") 안에 삽입할 수 없습니다.
-->NOT NULL 제약조건이 포함되어있다.
INSERT INTO PK_MEMBER VALUES(1,'ADMIN','1234','관리자',40); -->정상 실행
--INSERT INTO PK_MEMBER VALUES(1,'USER01','1234','유저1',40);
-->⚠️ORA-00001: 무결성 제약 조건(BS.SYS_C007332)에 위배됩니다
INSERT INTO PK_MEMBER VALUES(2,'USER01','1234','유저1',40); -->정상 실행

FOREIGN KEY(R)

지정된 컬럼의 값을 다른 테이블의 지정된 컬럼에 있는 값만으로 지정하게 만드는 것으로 참조됐다고 한다. 다른 테이블에 지정된 컬럼은 해당 값에 맞춰서 지정 컬럼에 사용하기 때문에 중복 값이 없어야한다. 다른 테이블에 UNIQUE나 PK가 설정된 컬럼을 주로 쓴다.

CREATE TABLE BOARD(
    BOARD_NO NUMBER PRIMARY KEY,
    BOARD_TITLE VARCHAR2(200),
    BOARD_CONTENT VARCHAR2(3000),
    BOARD_WRITER VARCHAR2(10) NOT NULL,
    BOARD_DATE DATE
);

CREATE TABLE BOARD_COMMENT(
    COMMENT_NO NUMBER PRIMARY KEY,
    COMMENT_COMTENT VARCHAR2(800),
    COMMENT_WRITER VARCHAR2(10),
    COMMENT_DATE DATE,
    BOARD_REF NUMBER REFERENCES BOARD(BOARD_NO)
);

INSERT INTO BOARD VALUES(1,'내용없음',NULL,'관리자',SYSDATE);
SELECT * FROM BOARD;
INSERT INTO BOARD VALUES(2,'게시글 공지','공지 내용입니다.','관리자',SYSDATE);
INSERT INTO BOARD VALUES(3,'오늘 금요일','금요일인데 시간이 안가요','USER01',SYSDATE);

INSERT INTO BOARD_COMMENT VALUES(1,'확인','USER1',SYSDATE,2);
INSERT INTO BOARD_COMMENT VALUES(2,'인정합니다','USER2',SYSDATE,3);
--INSERT INTO BOARD_COMMENT VALUES(3,'확인','USER2',SYSDATE,4);
-->⚠️ORA-02291: 무결성 제약조건(BS.SYS_C007348)이 위배되었습니다- 부모 키가 없습니다
-->부모키가 현재 3번까지 밖에 없음
INSERT INTO BOARD_COMMENT VALUES(3,'확인했어요','USER2',SYSDATE,2);
SELECT * FROM BOARD JOIN BOARD_COMMENT ON BOARD_NO=BOARD_REF;

💡 FK가 설정된 컬럼에 NULL값 처리
제약 조건 처리를 따로 하지 않았기 때문에 들어가긴 하지만 JOIN된 결과를 출력했을 때 맞는 컬럼이 없어 따로 출력 되지 않는다.

INSERT INTO BOARD_COMMENT VALUES(5,'NULL 들어갑니다','관리자',SYSDATE,NULL);
SELECT*FROM BOARD_COMMENT; -->COMMET테이블만 따로 확인했을때는 확인 가능

FOREIGN KEY가 있는 ROW 삭제

FK를 설정해서 테이블가 관계가 설정되면 참조되고 있는 부모 테이블의 ROW를 함부로 삭제할 수 없다. FK 설정할 때 삭제에 대한 옵션을 설정 가능하다.
ON DELETE SET NULL; : 부모가 삭제되면 참조 컬럼을 NULL값으로 수정한다. 이 때, NOT NULL 제약 조건이 있으면 안 된다. 제약 조건이 우선 되기 때문이다.
ON DELETE CASCADE; : 부모 데이터가 삭제되면 참조 컬럼도 같이 삭제한다. 참조 컬럼 자체가 삭제되기 때문에 NOT NULL 제약 조건과 상관 없이 삭제된다.

--설정 없이 NOT NULL 제약 조건이 있는 컬럼을 삭제
DELETE FROM BOARD WHERE BOARD_NO=3;
-->⚠️ORA-02292: 무결성 제약조건(BS.SYS_C007348)이 위배되었습니다.
--자식 레코드가 발견되었습니다

--DELETE CASCADE 이용
CREATE TABLE BOARD_COMMENT2(
    COMMENT_NO NUMBER PRIMARY KEY,
    COMMENT_COMTENT VARCHAR2(800),
    COMMENT_WRITER VARCHAR2(10),
    COMMENT_DATE DATE,
    BOARD_REF NUMBER REFERENCES BOARD(BOARD_NO)ON DELETE CASCADE
);

INSERT INTO BOARD VALUES(1,'내용없음',NULL,'관리자',SYSDATE);
INSERT INTO BOARD_COMMENT2 VALUES(1,'SET NULL','관리자',SYSDATE,1);
SELECT * FROM BOARD_COMMENT2;
DELETE FROM BOARD WHERE BOARD_NO=1;
-->정상 실행 된다.

CHECK(C)

지정된 컬럼에 지정된 값을 저장하기 위한 제약 조건이다. 동등값, 범위값 지정해서 설정한 값만 허용한다.

CREATE TABLE PERSON(
    NAME VARCHAR2(20),
    AGE NUMBER CHECK(AGE>0) NOT NULL,
    GENDER VARCHAR2(5) CHECK(GENDER IN('남','여'))
);
INSERT INTO PERSON VALUES('LEE',-10,'여');
-->⚠️ORA-02290: 체크 제약조건(BS.SYS_C007357)이 위배되었습니다
INSERT INTO PERSON VALUES('LEE',20,'여');
INSERT INTO PERSON VALUES('LEE',26,'Y');
-->⚠️ORA-02290: 체크 제약조건(BS.SYS_C007358)이 위배되었습니다.
-->지정 크기 이상의 문자열을 입력해도 입력 크기 오류가 발생한다.

DEFAULT 값 지정

테이블 생성 시 데이터가 안 들어왔을 때 대체 값으로 기본 값을 지정해줄 수 있다.

CREATE TABLE DEFAULT_TEST(
    TEST_NO NUMBER PRIMARY KEY,
    TEST_DATE DATE DEFAULT SYSDATE,
    TEST_DATA VARCHAR2(20) DEFAULT '기본값'
);
INSERT INTO DEFAULT_TEST VALUES(1,DEFAULT,DEFAULT);
INSERT INTO DEFAULT_TEST VALUES(2,'23/08/17','종강날');
INSERT INTO DEFAULT_TEST(TEST_NO) VALUES(3);
SELECT * FROM DEFAULT_TEST;

입력하지 않은 값은 기본 값을 대체 값으로 넣어준다.

제약 조건 설정 시 이름 설정

기본 방식으로 선언하면 SYSY00000으로 이름이 설정된다. 이름을 설정하면 나중에 제약조건 이름으로 삭제도 가능하다.

CREATE TABLE MEMBER_TEST(
    MEMBER_NO NUMBER CONSTRAINT MEMBUER_NO_PK PRIMARY KEY,
    MEMBER_ID VARCHAR2(20) CONSTRAINT MEMBER_ID_UQ UNIQUE NOT NULL,
    MEMBER_PWD VARCHAR2(20) CONSTRAINT MEMBER_PWD_NN NOT NULL,
    CONSTRAINT COMPOSE_UQ UNIQUE(MEMBER_ID, MEMBER_NO)
);