[SQL] DDL (Data Definition Language, 데이터 정의어)

DDL(Data Definition Language)은 데이터베이스의 구조를 생성하거나 수정 및 삭제하는데 사용되는 SQL문이다. Oracle 에는 여러가지 Oracle 객체를 가지고 있다.

[DB] Oracle의 5가지 객체 - Table, Index, View, Sequence, Synonym

---

 

1. CREATE

CREATE문은 데이터베이스에서 테이블을 생성하는데 사용된다.

CREATE TABLE [스키마].테이블명 (
    컬럼명1 데이터타입,
    컬럼명2 데이터타입,
    컬럼명3 데이터타입,
    ..
);

💡 스키마(schema)는 사용자가 데이터베이스에 접근하여 생성한 객체들의 대표 이름을 의미하며 기본적으로 사용자의 계정명과 동일하게 부여된다. 접근 권한을 가진 객체에 접근할 때는 항상 '스키마.객체' 형식으로 사용한다.

1.1 Oracle의 데이터형

-- 💡 Oracle에서 한글 한글자 AL16UTF8이면 2byte AL32UTF8이면 3byte
-- 한글 byte 확인코드
select * 
from NLS_DATABASE_PARAMETERS
where parameter = 'NLS_CHARACTERSET';

1.2 DEFAULT 옵션

• 컬럼에 값을 지정하지 않아도 자동으로 기본값이 입력되어 널(null)값 저장 방지
• 주로 현재 날짜 및 성별 같은 고정된 값만 가지는 컬럼에 대해 사용

CREATE TABLE employee2
( eno NUMBER(4),
  ename VARCHAR2(20),
  hiredate DATE DEFAULT SYSDATE, -- 자동으로 현재 날짜값으로 저장
  salary NUMBER(7,2));

1.3 제약조건 (CONSTRAINT)

제약 조건은 테이블에 부적절한 데이터가 저장되는 것을 방지하기 위해서 테이블을 생성할 때 각 컬럼에 대해서 정의하는 규칙을 의미한다. 데이터의 무결성을 보장 받기 위한 방법이다.

[DB] 데이터베이스 제약조건(constraints)

 

제약조건을 지정하는 방식
✅ 컬럼 레벨(Column Level) : 테이블을 생성할 때 각각의 컬럼을 정의하면서 같이 제약조건을 지정
✅ 테이블 레벨(Table Level) : 모든 컬럼을 정의하고 맨 마지막에 제약조건을 지정
NOT NULL 을 제외한 나머지 4개의 제약조건은 컬럼/테이블 레벨 방식 모두 지원한다.

🔎 PIRMARY KEY 제약 조건

① 중복 저장불가
② null값 저장불가

-- 컬럼 레벨
CREATE TABLE [스키마].테이블명( 
    컬럼명 데이터타입 [CONSTRAINT 제약조건명] PRIMARY KEY,
    ...
);

-- 테이블 레벨
CREATE TABLE [스키마].테이블명(
    컬럼명 데이터타입,
    ...
 CONSTRAINT 제약조건명 PRIMARY KEY(컬럼명[, 컬럼명2]) -- 복합컬럼도 가능
);

-- 생성된 제약조건 확인
SELECT * 
FROM USER_CONSTRAINTS
WHERE table_name='테이블명';

🔎 UNIQUE 제약 조건

① 중복 저장불가
② null값 저장가능

-- 컬럼 레벨
CREATE TABLE [스키마].테이블명( 
    컬럼명 데이터타입 [CONSTRAINT 제약조건명] UNIQUE,
    ...
);

-- 테이블 레벨
CREATE TABLE [스키마].테이블명(
    컬럼명 데이터타입,
    ...
 CONSTRAINT 제약조건명 UNIQUE (컬럼명[, 컬럼명2])
);

🔎 NOT NULL 제약 조건

① null값 저장불가

-- 컬럼 레벨
CREATE TABLE [스키마].테이블명( 
    컬럼명 데이터타입 [CONSTRAINT 제약조건명] NOT NULL,
    ...
);

-- 테이블 레벨 방식 사용X

🔎 CHECK 제약 조건

-- 컬럼 레벨
CREATE TABLE [스키마].테이블명( 
    컬럼명 데이터타입 [CONSTRAINT 제약조건명] CHECK(조건식),
    ...
);

-- 테이블 레벨
CREATE TABLE [스키마].테이블명(
    컬럼명 데이터타입,
    ...
 CONSTRAINT 제약조건명 CHECK (조건식)
);

-- ‘개발’과 ‘인사’ 만 저장 가능하도록 CHECK 제약조건을 지정
CREATE TABLE department( 
    deptno NUMBER(2) ,
    dname VARCHAR2(15) CONSTRAINT department_dname_ck CHECK(dname IN('개발','인사')) 
);

🔎  FOREIGN KEY 제약 조건

REFERENCES 키워드를 지정하여 참조하는 부모테이블과 컬럼명을 명시한다. 이때, 참조하는 부모 테이블의 컬럼은 반드시 기본키 PRIMARY KEY 또는 UNIQUE키로 제약조건이 설정된 컬럼이여야 한다.

-- 컬럼 레벨
CREATE TABLE [스키마].테이블명( 
    컬럼명 데이터타입 [CONSTRAINT 제약조건명] REFERENCES 부모테이블명(컬럼명),
    ...
);

-- 테이블 레벨
CREATE TABLE [스키마].테이블명(
    컬럼명 데이터타입,
    ...
 CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 부모테이블명(컬럼명)
);

💡 추가 옵션

• ON DELETE CASCADE : 부모 테이블의 행이 삭제되면 참조하는 자식 테이블의 행도 같이 삭제
• ON DELETE SET NULL : 부모 테이블의 행이 삭제되면 참조하는 자식 테이블의 행의 값은 NULL로 설정

---

2. DROP

DROP문은 테이블을 삭제하는데 사용한다.

DROP TABLE 테이블명;

💡 CASCADE CONSTRAINTS 옵션
자식테이블이 있는 부모테이블을 삭제할 때 자식테이블도 함께 삭제할 수 있는 옵션이다.

---

3. ALTER

생성된 테이블에 대한 구조를 변경하는데 사용한다. 테이블에 대한 구조 변경은 컬럼의 추가,삭제 및 컬럼의 타입이나 길이 변경, 제약조건 추가,삭제등이 가능하다.

3.1 컬럼 추가

ALTER TABLE 테이블명
ADD ( 컬럼명 데이터타입 [, 컬럼명2 데이터타입]);

-- employee 테이블에 문자 타입의 email 컬럼과 address 컬럼 추가
ALTER TABLE employee
ADD ( email VARCHAR2(10), address VARCHAR2(20) );

3.2 컬럼 변경

ALTER TABLE 테이블명
MODIFY ( 컬럼명 데이터타입 [, 컬럼명2 데이터타입]);

-- employee 테이블의 email 컬럼 크기를 40byte로 변경
ALTER TABLE employee
MODIFY ( email VARCHAR2(40) );

3.3 컬럼 삭제

ALTER TABLE 테이블명
DROP COLUMN ( 컬럼명 [, 컬럼명2] );

-- employee 테이블의 email 컬럼 삭제
ALTER TABLE emp04
DROP COLUMN ( email );

3.4 제약조건 추가

ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명] 제약조건타입(컬럼명);

-- employee 테이블 eno 컬럼 값이 중복되지 않도록 하기 위해 기본키 제약조건 추가
ALTER TABLE employee
ADD CONSTRAINT employee_eno_pk PRIMARY KEY(eno);

3.5 제약조건 삭제

ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명 [CASCADE];

-- 기본키 PRIMARY KEY와 UNIQUE는 제약조건명 없이 삭제 가능
ALTER TABLE 테이블명
DROP PRIMARY KEY | UNIQUE(컬럼)

3.5 제약조건 활성화/비활성화

ALTER TABLE 테이블명 DISABLE | ENABLE CONSTRAINT 제약조건명 [CASCADE];