DDL 데이터정의어

● 데이터정의어: DDL - 테이블생성 및 구조, 제약조건 등을 설정

CREATE TABLE MEMBER3 (
    ID VARCHAR2(30),
    PW VARCHAR2(200),
    AGE NUMBER(3),
    PHONE VARCHAR2(15),
    GENDER VARCHAR2(1),
    REGDATE DATE,
    REGDATE1 TIMESTAMP,
    CONSTRAINT PK_MEMBER3_ID PRIMARY KEY(ID),
    CONSTRAINT CK_MEMBER3_GENDER CHECK (GENDER IN ('M', 'F'))
);

● 주소 컬럼 추가

ALTER TABLE MEMBER3 ADD ADDR VARCHAR2(100);

● 주소 타입의 길이 100 -> 120

ALTER TABLE MEMBER3 MODIFY ADDR VARCHAR(12);

● 주소 컬럼 삭제

ALTER TABLE MEMBER3 DROP COLUMN ADDR;

● 기본키 제약조건 설정

ALTER TABLE MEMBER3 ADD CONSTRAINT PK_MEMBER3_ID PRIMARY KEY(ID);

● 성별에 NOT NULL 추가(편집에서 바로 설정 가능)

ALTER TABLE MEMBER3 MODIFY GENDER NOT NULL;

● 연락처(PHONE)에 고유값(UNIQUE) 설정(제약조건)

ALTER TABLE MEMBER3 ADD CONSTRAINT UQ_MEMBER3_PHONE UNIQUE(PHONE);

● 제약조건 삭제

ALTER TABLE MEMBER3 DROP CONSTRAINT UQ_MEMBER3_PHONE;

---

● 실습

● 부서(DEPARTMENT): NO(PK), NAME.
● AREA VARCHAR(20)(지역) 추가. 지역컬럼의 체크값을 부산, 서울, 대전만 가능하게. 부서명의 길이를 30으로 변경
● 부서명에 NOT NULL 추가, 부서명에 UNIQUE 제약조건 설정 UQ_DEPT_NAME

CREATE TABLE DEPARTMENT (
    NO NUMBER,
    NAME VARCHAR2(3),
    REGDATE DATE
);

ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPT_NO PRIMARY KEY(NO);
ALTER TABLE DEPARTMENT ADD AREA VARCHAR2(20);
ALTER TABLE DEPARTMENT ADD CONSTRAINT CK_DEPARTMENT_AREA CHECK (AREA IN ('부산', '서울', '대전'));
ALTER TABLE DEPARTMENT MODIFY NAME VARCHAR(30);
ALTER TABLE DEPARTMENT MODIFY NAME NOT NULL;
ALTER TABLE DEPARTMENT ADD CONSTRAINT UQ_DEPT_NAME UNIQUE(NAME);

DROP TABLE DEPARTMENT CASCADE CONSTRAINTS;

● 회사원(EMPLOYEE): NO(PK), NAME, DEPTNO(FK)
● PEMPNO(상사번호), POSITION VARCHAR2(10)(상사. CK-대리, 과장, 부장, 차장), PAY(급여), REGDATE

CREATE TABLE EMPLOYEE (
    NO NUMBER PRIMARY KEY,
    NAME VARCHAR2(20),
    DEPTNO NUMBER,
    PEMPNO NUMBER,
    POSITION VARCHAR2(10),
    PAY NUMBER,
    REGDATE DATE,
    CONSTRAINT CK_EMPLOYEE_POSITION CHECK (POSITION IN ('대리', '과장', '부장', '차장')),
    CONSTRAINT FK_EMPLOYEE_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(NO)
);
-- ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(NO);