ORACLE SQL DDL_01

 

DDL이란 

 

DATE 

Definition 

Language  약자로 

 

데이터 정의 언어로 객체(Object)를 만들고 (Create) 수정(Alter), 삭제(Drop)하는 구문이다 

 

표현식 : 

CREATE TABLE 테이블명

 

CREATE TABLE MEMBER (

 

MEMBER_ID VARCHAR2(20),

MEMBER_PWD VARCHAR2(20),

MEMBER_NAME VARCHAR2(20)

 

);

 

자료형 :

 

CHAR(크기) : 고정길이 문자 데이터 (최대 2,000 Byte)

VARCHAR2(크기) : 가변길이 문자 데이터(최대 4,000Byte)

NUMBER

DATE

 

CHAR : SIZE 포함될 문자(열)의 크기 

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

 

VARCHAR2 : 가변 길이형이다 

 

 

컬럼 주석 :

테이블의 컬럼에 주석을 다는 구문이다 

 

표현식 : 

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

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

 

---

제약 조건 (CONSTRAINTS) : 

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

이는 데이터 무결성 보장을 주 목적으로 한다. 

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

 

 

NOTNULL : 데이터에 NULL을 허용하지 않음 

UNIQUE : 중복된 값을 허용하지 않는다. 

PRIMARY KEY : NULL과 중복 값을 허용하지 않는다.

(컬럼의 고유 식별자로 사용하기 위해 사용한다. )

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

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

 

 

 

 

NOT NULL  --> :

 

해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용 

특정 컬럼에 값을 저장하거나 수정할 때

NULL값을 허용하지 않도록 컬럼 레벨에서 제한 

 

예시 

CREAT TABLE USER_NOTNULL(

                     USER_NO NUMBER NOT NULL             

 

 

); 

NOT NULL 제약조건이 설정된 컬럼에 NULL 값이 입력되면

행 자체를 삽입하지 않는다.

 

UNIQUE : 

컬럼 입력 값에 대해 중복을 제한하는 제약조건으로

컬럼 레벨과 테이블 레벨에 설정 가능 

 

CREATE TABLE USER_UNIQUE(

 

USER_ID VARCHAR2(20) UNIQUE, -- 컬럼 레벨 방식 

 

UNIQUE (USER_NO, USER_ID) -- > 두 개의 컬럼을 묶어 하나의

UNIQUE 제약 조건 설정 가능하다 

 

UNIQUE(USER_ID) -- 테이블 레벨   

);

 

UNIQUE 제약 조건은 NULL 값 중복은 가능하다 - > 테이블 생성 시 컬럼 레벨에 

NOT NULL 지정하면 해결 

 

CHECK : 

 

해당 컬럼에 입력 되거나 수정되는 값을 체크하여 

설정된 값 이외의 값이면 에러가 발생한다 

비교 연산자를 이용하여 조건을 설정하며 비교 값을 리터럴만 사용 가능하고 

변하는 값이나 함수 사용은 불가능하다 .

 

CREATE TABLE USER_CHECK(

 

GENDER VARCHAR2(10) CHECK(GENDER IN('남','여'))

 

);

 

INSERT INTO USER_CHECK VALUES('남');

 

 

 

PRIMARY KEY  : 

 

PRIMARY KEY  : 

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

NOT NULL의 의미와 UNIQUE의 의미를 둘 다 가지고 있으며 

한 테이블 당 컬럼 레벨과 테이블 레벨 둘 다 지정이 가능하다 

 

 

PRIMARY KEY 예시 ) 

컬럼 레벨 방식

CREATE TABLE USER_PK (

 

USER_NO NUMBER PRIMARY KEY 

USER_ID VARCHAR2(20) UNIQUE 

 

 

);

 

INSERT INTO USER_PK VALUES(1,'user01')

 

 

 

PRIMARY KEY 예시 ) 

테이블 레벨 방식

CREATE TABLE USER_PK (

 

USER_NO NUMBER , 

USER_ID VARCHAR2(20) UNIQUE ,

 

PRIMARY KEY(USER_NO)

 

--

PRIMARY KEY(USER_NO, USER_ID)

-- 두 개의 컬럼을 묶어서 하나의 

PRIMARY KEY 제약 조건 설정 가능 

);

 

 

---

 

FOREIGN KEY :

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

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

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

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

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

 

 

: 

다른 테이블에 존재하는 값을 저장하고자 할 때 사용되는 제약조건 

- >  다른 테이블을 참조한다.

- >  주로 외래키를 통해 테이블간의 관계가 형성된다 

 

-- 회원 등급 정보를 저장할 테이블 
CREATE TABLE MEMBER_GRADE(
    GRADE_NO NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(20) NOT NULL
);

INSERT INTO MEMBER_GRADE VALUES(100, '일반회원');
INSERT INTO MEMBER_GRADE VALUES(200, 'VIP회원');
INSERT INTO MEMBER_GRADE VALUES(300, 'VVIP회원');

 

 

 

CREATE TABLE MEMBER(

    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('남','여')),
    JOIN_DATE DATE,
    MEM_GRADEID NUMBER REFERENCES MEMBER_GRADE(GRADE_NO) -- 컬럼 레벨 방식 
     * 컬럼명                자료형    REPERENCES    참조할 테이블명(참조할 컬럼명)

참조할 컬럼명 생략 시 기본키를 지정

 

 

   테이블 레벨 방식 
   FOREIGN KEY(MEM_GRADEID)  REFERENCES MEMBER_GRADE(GRADE_NO) 테이블 레벨 방식  

   FOREIGN KEY (컬럼명)  REPERENCES 참조할 테이블명 (참조할 컬럼명) 

       
);

INSERT INTO MEMBER
    VALUES(1, 'whdrns456', '0780', '최종군','남',sysdate, 100);
INSERT INTO MEMBER
    VALUES(2, 'jgw0928', '1234', '조건웅','남',sysdate, 200);    
INSERT INTO MEMBER
    VALUES(3, 'HH00', 'QWER', '임현호','남',sysdate, null);    
--> 외래키로 설정된 컬럼에는 기본적으로 NULL값은 저장 가능하다 

 

 

INSERT INTO MEMBER
    VALUES(4, 'jw33', '0987', '이주원','남',sysdate, 400); 

오류가 발생한다 부모키를 찾을 수 없다 :

회원등급 테이블에 저장되지 않은 값을 사용 

 

 

DELETE FROM MEMBER_GRADE 
WHERE GRADE_NO = 100;

--> 자식테이블에서 100이라는 값을 사용하고 있기 때문에 삭제가 불가능하다 

 

DELETE FROM MEMBER_GRADE
WHERE GRADE_NO = 400;
--> 자식테이블(MEMBER)에서 400이라는 값은 사용하지 않았기 때문에 삭제가 가능하다. 

 

* 자식테이블에서 이미 사용하고 있는 값이 있을 경우 

        부모테이블로부터 무조건 삭제가 되지 않는 삭제옵션이 있다 

 

 

외래키 제약 조건 삭제 옵션 : 

부모테이블의 데이터 삭제 시 해당 데이터를 사용하고 있는 자식 테이블의 값을 

어떻게 할 것인지에 대한 옵션 

 

(기본값) ON DELETE RESTRICTED : 자식 데이터로부터 사용중인 값이 있으면 

부모테이블에서 데이터 삭제 불가능하다. 

 

 

ON DELETE SET NULL : 부모테이블에 있는 데이터 삭제 시 해당 데이터를 사용 중인 

자식 테이블의 값을 NULL로 바꿔준다. 

 

ON DELETE CASCADE : 부모 테이블에 있는 데이터 삭제 시 해당 데이터를 사용 중인 

자식 테이블의 값도 삭제 

 

 

CREATE TABLE MEMBER(
    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('남','여')),
    JOIN_DATE DATE,
    MEM_GRADEID NUMBER REFERENCES MEMBER_GRADE(GRADE_NO) ON DELETE SET NULL-- 컬럼 레벨 방식     
);

 

DELETE FROM MEMBER_GRADE
WHERE GRADE_NO = 100;

 

자식테이블에서 100번 등급이 사용된 데이터가 NULL로 변경되면서 

부모테이블에서는 데이터가 잘 삭제 됨 

 

DEFAULT : 

         제약 조건은 아니다 

DEFAULT는 컬럼을 선정하지 않고 데이터 추가 시 NULL 값이 아닌 

기본적인 값을 값을 저장할 때 사용이 된다. 

 

 JOIN_DATE DATE DEFAULT SYSDATE

 

 

CREATE TABLE MEMBER(
    
    MEM_NO NUMBER PRIMARY KEY,-- 회원 번호 
    MEM_NAME VARCHAR2(20),
    AGE NUMBER, 
    HOBBY VARCHAR2(20),
    JOIN_DATE DATE DEFAULT SYSDATE
    -- 취미 
    -- 가입일 
);

INSERT INTO MEMBER VALUES(1, '짱구',5,'엉덩이춤','24/0701');
INSERT INTO MEMBER VALUES(2, '맹구',5,'코하기',null);
INSERT INTO MEMBER VALUES(3, '훈발놈',5,null,null);

SELECT *FROM MEMBER;

INSERT INTO MEMBER(MEM_NO, MEM_NAME) VALUES(4,'유리');

지정하지 않은 컬럼에 대한 값은 기본적으로 NULL값이 저장된다. 
단 해당 컬럼에 기본값이 설정되어 있으면 NULL이 아닌 기본값으로 저장됨 

 

DEFAULT : 

선정하지 않은 값은 기본값으로 저장이 되는데 

여기서 선정하지 않은 이란 NULL로 지정한 값이 아닌 말 그대로 공백을 둔 상태이다