Oracle DDL

2.1  테이블 명령

CREATE TABLE TEMP(
    ID CHAR(7),
    NAME VARCHAR2(100) DEFAULT 'TEST',
    IN_DATE DATE,
    SALARY NUMBER(10)
);

2.2 구조확인 및 구조확인(데이터 딕셔너리)

DESC TEMP;

SELECT OBJECT_NAME FROM USER_OBJECTS

--------------------------------------3.제약조건--------------------------------------
NOT NULL : 해당 컬럼에  NULL을 허용 안함
UNIQUE : 해당 컬럼에 값이 입력되면 유일한 값, NULL 그리고 자동으로 UNIQUE INDEX 생성
PRIMARY KEY : NOT NULL * UNIQUE 테이블에 하나만 존재
CHECK : 업무에서 정의한 값의 범위만 입력 가능
FOREIGN KEY : 참조 정의 참조하는 부모의 값 또는 NULL

CREATE TABLE DEPT(
    ID CHAR(10) PRIMARY KEY ,                -->제약조건 이름이 SYS_C 일련번호
    NAME VARCHAR(20) NOT NULL      
);

CREATE TABLE SAWON(
    IDNO CHAR(7)
        CONSTRAINT SAWON_IDNO_PK PRIMARY KEY,
    NAME VARCHAR2(20)
        CONSTRAINT SAWON_NAME_NM NOT NULL,
    DEPT_NO CHAR(20)
        CONSTRAINT SAWON_DEPT_NO_FK
            REFERENCES DEPT(ID)
);
-------------------------------------제약조건 확인------------------------------
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,R_CONSTRAINT_NAME,TABLE_NAME FROM USER_CONSTRAINTS;

------------------------------------제약 조건 테이블 생성-------------------
CREATE TABLE PLAYER_T(
    PLAYER_ID CHAR(7)
        CONSTRAINT PLAYER_ID_PK PRIMARY KEY,
    PLAYER_NAME VARCHAR2(20)
        CONSTRAINT PLAYER_NAME_NM NOT NULL,
    NICKNAME VARCHAR2(40),
    JOIN_YYYY CHAR(4),
    POSITION CHAR(2),
    BACK_NO NUMBER(2),
    NATION VARCHAR2(20),
    BIRTH DATE,
    SOLAR CHAR(1),
    HEIGHT NUMBER(3),
    WEIGHT NUMBER(3)
);



CREATE TABLE TEAM_T(
    TEAM_ID CHAR(3)
        CONSTRAINT TEAM_PK_ID PRIMARY KEY,
    REGION_NAME VARCHAR2(4) NOT NULL,
    STADIUM_ID CHAR(3) NOT NULL,
    TEAM_NAME VARCHAR2(20) NOT NULL,
    E_TEAM_NAME VARCHAR2(50),
    ORIG_YYYY CHAR(4),
    OWNER VARCHAR2(10),
    ZIP_CODE1 CHAR(3),
    ZIP_CODE2 CHAR(3),
    ADDRESS VARCHAR2(60),
    DDD VARCHAR2(3),
    TEL VARCHAR2(10),
    FAX VARCHAR2(10),
    HOMEPAGE VARCHAR2(50)
);

-------------------------복합 속성으로primary key 만들기
ALTER TABLE PLAYER_T
CONSTRAINT ADDS_PKs PRIMARY KEY(COLUMN1,COLUMN2)

----------------------테이블 구조 변경--------------------------------------------
1.ALTER TABLE 테이블 ADD( 컬럼 정의)
ALTER TABLE PLAYER_T
    ADD( COMMENT VARCHAR2(50));
    DESC PLAYER_T;

------------------------------------------컬럼삭제-----------------------------

ALTER TABLE PLAYER_T
    DROP COLUMN COMMENTS;

    DESC PLAYER_T


INSERT INTO  PLAYER_T (PLAYER_ID,PLAYER_NAME) VALUES('TEST','TEST')
---------------------------컬럼 추가(DEFAULT 값을 지정하면서)------------------------------------
--컬럼을 추가 하면서 DEFAULT  값을 설정하면 기존의 자료에도 컬럼의 값이 DEFAULT값이 됨
--예약어 사용시 나오는 에러 invalid identifier
ALTER TABLE PLAYER_T
    ADD( COMMENTS VARCHAR2(50) DEFAULT '테스트' );
SELECT * FROM PLAYER_T


-----------------컬럼 사용 억제 (ALTER TABLE 테이블명 SET UNUSED COLUMN 컬럼명)

ALTER TABLE PLAYER_T
    SET UNUSED COLUMN SOLAR;
    DESC PLAYER_T

----------------사용 억제된 컬럼 삭제
ALTER TABLE PLAYER_T
    DROP UNUSED COLUMNS;

----------------컬럼 수정( ALTER TABLE 테이블명 MODIFY (컬럼 재 정의))
ALTER TABLE PLAYER_T
    MODIFY(COMMENTS CHAR(4)) -->자료가 입력되어 있는 경우 기존 자료보다 크게 설정해야된다 아래 구문 실행


ALTER TABLE PLAYER_T
    MODIFY(COMMENTS CHAR(6));

ALTER TABLE PLAYER_T
    MODIFY(JOIN_YYYY NUMBER(2)); -->기존의 자료가 모두 NULL이면 데이터 타입과 길이 모두 변경가능 자료가 있음 변경 불가

ALTER TABLE PLAYER_T
    MODIFY(JOIN_YYYY NUMBER(2) DEFAULT 19); -->DEFAULT 설정시 기존값에는 영향을 끼치지 않고
                                                    추가되는 자료에만 적용


-----------------제약조건 삭제-------------------
EX : (ALTER TABLE 테이블명 DROP CONSTRAINT  제약조건명 [CASCADE])


SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,R_CONSTRAINT_NAME,TABLE_NAME FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('DEPT','SAWON');


==>SYS_C0051050 제약조건 삭제
ALTER TABLE DEPT
DROP CONSTRAINT SYS_C0051050;


ALTER TABLE DEPT
DROP CONSTRAINT SYS_C0051050 CASCADE;
==>SAWON의 SAWON_DEPT_NO_FK 제약조겉도 삭제됨

---------------------------------7.제약조건 추가------------------------------

1)PLAYER_T에 TEAM_ID 컬럼을 CHAR(3)으로 추가
ALTER TABLE PLAYER_T
ADD TEAM_ID CHAR(3);

2)PLAYER_T에 TEAM_ID 컬럼에 TEAM_T의 TEAM_ID 컬럼을 참조하는 FK 제약조건 추가
ALTER TABLE PLAYER_T
ADD CONSTRAINT PLAYER_TEAM_ID_FK FOREIGN KEY (TEAM_ID)
REFERENCES TEAM_T(TEAM_ID);

3) PLAYER_T의 COMMENTS 컬럼에 NOT NULL 제약조건 추가
==>ERROR 발생
ALTER TABLE PLAYER_T
ADD CONSTRAINT PLAYER_COMMENTS_NM NOT NULL

==>MODIFY 절에서 추가 가능
ALTER TABLE PLAYER_T
MODIFY (COMMENTS CHAR(6) CONSTRAINT PLAYER_COMMENTS_NN NOT NULL);


---------------------6.제약조건 활성화 비활성화------------------
ex : ALTER TABLE 테이블명 DISABLE CONSTRAINT 제약조건 이름

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,R_CONSTRAINT_NAME,TABLE_NAME FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('PLAYER_T','TEAM_T');

ALTER TABLE TEAM_T
DISABLE CONSTRAINT TEAM_PK_ID;   =>참조하는 FK가 존재한다

ALTER TABLE TEAM_T
DISABLE CONSTRAINT TEAM_PK_ID CASCADE; ==>참조하는 FK도 DISABLE

==>제약조건의 ENABLE(활성화)은 각각 실행
ALTER TABLE TEAM_T
    ENABLE CONSTRAINT TEAM_PK_ID;

ALTER TABLE PLAYER_T
ENABLE CONSTRAINT PLAYER_TEAM_ID_FK;

-----------테이블 삭제 / 자료만 삭제 / 이름변경-------------
EX) TEAM_T 테이블을 삭제

DROP TABLE TEAM_T  ==>삭제가 안된다 PLAYER_T 테이블의 TEAM_ID가 참조하고 있기 때문에
DROP TABLE TEAM_T CASCADE CONSTRAINT ==>TEAM_T의 모든 정보 삭제

EX) PLAYER_T의 자료만 삭제 구조는 남김
TRUNCATE TABLE PLAYER_T
DESC PLAYER_T ==>구조는 남음

EX)TEMP 테이블의 이름을 TMP로 변경
RENAME TEMP TO TMP;

TRUNCATE  : 다이렉트로 삭제한다 버퍼를 안거치고 바로 삭제하기 때문에 속도가 빠르다
DELETE FROM 테이블 같은 경우는  버퍼를 거치면서 삭제를 하기 때문에 ROLLBACK이 가능하다 (안전) 단 속도는 조금 느리다

[출처] Oracle DDL 명령어 총 정리|작성자 시네루