DDL
-
DB의 구조를 생성하거나 수정 및 삭제하는데 사용되는 SQL문이다.
-
DDL은 자동으로 COMMIT되기 때문에 DB에 즉각 영향을 미치며 DB Dictionary에 정보가 저장된다.
-
오라클 객체
|객체명|설명|
|-|-|
|테이블(table)|기본적인 데이터 저장 단위로 행과 열로 구성된 객체|
|인덱스(index)|테이블에 저장된 데이터의 검색 성능 향상 목적을 위한 객체|
|뷰(view)|한 개 이상의 테이블의 논리적인 부분 집합을 표시할 수 있는 객체|
|시퀀스(sequence)|테이블의 특정 컬럼 값에 숫자 값 자동 생성 목적을 위한 객체|
|동의어(synonym)|객체에 대한 동의어를 설정하기 위한 객체|
- DDL문 종류
| SQL 종류 | 명령문 |
|---|---|
| Data Definition Language (DDL:데이터 정의어) | CREATE(DB 객체 생성) ALTER(DB 객체 변경) DROP(DB 객체 삭제) RENAME(DB 객체이름 변경) TRUNCATE(객체 정보 절삭) |
CREATE
- 스키마를 지정하거나 생략하고 테이블 명을 지정할 수 있다.
- 스키마는 사용자가 데이터베이스에 접근하여 생성한 객체들의 대표 이름을 의미하며 기본적으로 사용자의 계정명과 동일하게 부여된다.
- 즉, SCOTT 계정으로 접속한 사용자의 스키마는 SCOTT이 된다.
--문법
CREATE TABLE [스키마].테이블명
(컬럼명 데이터타입 [DEFAULT 값 | 제약조건][,...]);
CREATE TABLE DEPT_2
( deptno number(2),
dname varchar2(10),
loc varchar2(10) );- YKCB team님의 스키마의 개념 및 특징
- https://ykcb.tistory.com/entry/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EC%8A%A4%ED%82%A4%EB%A7%88%EC%9D%98-%EA%B0%9C%EB%85%90-%ED%8A%B9%EC%A7%95
오라클의 데이터 타입
DEFALUT 옵션
- 컬럼에 값을 지정하지 않아도 자동으로 기본값이 입력되어 NULL 값이 되는 것을 방지해준다.
CREATE TABLE emp2
(empno number(4),
ename varchar2(20),
hiredate date default sysdate,
sal number(7,2));서브쿼리를 이요한 테이블 생성
- CTAS (씨탁스)라고 한다.
- 지정된 컬럼의 개수와 서브쿼리에서 리턴된 컬럼의 개수가 일치해야 한다.
- NOT NULL을 제외한 제약 조건은 생성된 테이블에 복사되지 않음
CREATE TABLE table
[(col, col ....)]
AS subquery
CREATE TABLE deptA
AS
SELECT * FROM dept;
CREATE TABLE deptC
AS
SELECT *
FROM dept
WHERE 1=2;제약 조건
-
테이블에 올바르지 않은 부적절한 데이터가 저장되는 것을 방지하기 위해서 테이블을 생성할 때 각 컬럼에 대해서 정의하는 여러 가지 규칙을 의미한다.
-
이는 DB의 설계 단계에서 데이터의 무결성을 보장 받기 위한 방법이다.
-
[표]제약조건 타입
| 제약 조건 타입 | 설명 |
|---|---|
| NOT NULL | 해당 컬럼 값으로 NULL을 허용하지 않는다. 컬럼 레벨 방식만 지원한다. |
| UNIQUE | 테이블 내에서 해당 컬럼 값은 항상 유일한 값을 갖는다. 컬럼 레벨/테이블 레벨 방식 모두 지원한다. |
| PRIMARY KEY | 해당 컬럼 값은 반드시 존재해야 하고 유일해야 한다. 즉, NOT NULL과 UNIQUE조건을 결합한 형태이다. 컬럼 레벨/테이블 레벨 방식 모두 지원한다. |
| FOREIGN KEY | 해당 컬럼의 값이 다른 테이블의 컬럼의 값을 참조해야한다. 즉, 참조되는 컬럼에 없는 값은 저장이 불가능하다. 컬럼 레벨/테이블 레벨 방식 모두 지원한다. |
| CHECK | 해당 컬럼에 가능한 데이터 값의 범위나 사용자 조건을 지정한다. 컬럼 레벨/테이블 레벨 방식 모두 지원한다. |
- 제약 조건을 설정하는 방법은 테이블을 생성할 때 각각의 컬럼을 정의하면서 같이 제약조건을 지정하는 컬럼 레벨과 모든 컬럼을 정의하고 맨 마지막에 제약조건을 추가하는 형태인 테이블 레벨이 있다.
- 컬럼 레벨 제약조건은 한 개의 컬럼에 한 개의 제약조건만 정의가 가능하고 위의 5가지 모든 제약조건에 대해서 사용이 가능하다.
- 테이블 레벨 제약조건은 한 개 이상의 컬럼에 한 개의 제약조건을 정의할 수 있으며 NOT NULL 제약조건을 제외한 나머지 4개의 제약 조건을 사용할 수 있다.
PRIMARY KEY
- 테이블에서 해당 행을 다른 행과 구분할 수 있도록 식별 기능을 가진 컬럼이다.
- 테이블 당 반드시 하나의 기본 키만 가질 수 있으며 하나의 컬럼 또는 여러 컬럼(복합 컬럼)으로 기본키를 설정할 수 있다.
- 값이 유일해야 하고 NULL 값은 포함할 수 없음을 보증하며 자동으로 UNIQUE INDEX 객체가 생성된다.
- 인덱스가 자동으로 생성되기 때문에 기본키를 이용한 데이터 검색은 기본적으로 검색 속도가 빠르다.
-- 컬럼 레벨
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입, [CONSTRAINT 제약조건명] PRIMARY KEY,
컬럼명 데이터타입,
...
);
-- 테이블 레벨
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입,
컬럼명 데이터타입,
[CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명[, 컬럼명2])
);
--컬럼 레벨
CREATE TABLE depart(
deptno number(2) constraint depart_deptno_pk PRIMARY KEY,
dname varchar2(15),
loc varchar2(15));
--테이블 레벨
CREATE TABLE pk_tab2(
id number(2),
name varchar2(10),
CONSTRAINT pk_tab2_id_pk PRIMARY KEY(id));
CREATE TABLE depart3(
deptno number(2),
dname varchar2(15),
loc varchar2(15),
constraint depart3_deptno_pk PRIMARY KEY(deptno, loc)
);
-- 해당 테이블의 제약조건 보기(데이터 사전)에서
SELECT *
FROM USER_CONSTRAINTS
WHERE table_name = 'DEPART';- USER_CONSTRAINTS에서 제약 조건을 검색할 때 테이블명은 대문자로 비교해야 한다.
- CONSTRAINT_TYPE 컬럼에 저장되는 값의 의미는 다음과 같다.
[표] 제약조건 타입
| constraint_type 값 | 설명 |
|---|---|
| P | PRIMARY KEY |
| R | FOREIGN KEY |
| U | UNIQUE |
| C | NOT NULL, CHECK |
- 제약조건명을 사용하는 것을 권장한다. 그 이유는 제약조건을 삭제하거나 비활성 또는 활성화할 때 제약조건명을 사용하기 때문이다.
- 제약조건명을 지정하지 않으면 자동으로 오라클에서는
SYS_라는 접두사가 붙은 이름으로 제약조건명이 설정된다.
USER_CONS_COLUMNS 데이터 사전
- 어떤 컬럼에 제약조건이 설정되어 있는지를 확인할 수 없다.
- 어떤 컬럼에 제약조건이 정의되었는지를 확인하기 위해서는 USER_CONS_COLUMNS 데이터 사전을 이용해야 한다.
SELECT * FROM USER_CONS_COLUMNS WHERE table_name = 'DEPARTMENT';
UNIQUE
- 기본 키가 아닌 경우에도 컬럼의 모든 데이터가 유일해야 되는 경우에는 UNIQUE 제약조건을 사용할 수 있다.
- 기본 키와 마찬가지로 자동으로 UNIQUE INDEX가 생성되어 빠른 검색 효과를 볼 수 있다.
- 기본 키와 차이점은 제약조건을 여러 개 지정할 수 있고, NULL 값도 저장할 수 있다는 것이다.
--컬럼 레벨
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입, [CONSTRAINT 제약조건명] UNIQUE,
컬럼명 데이터타입,
...
);
-- 테이블 레벨
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입,
컬럼명 데이터타입,
[CONSTRAINT 제약조건명] UNIQUE(컬럼명[, 컬럼명2])
);
CREATE TABLE uni_tab1(
deptno number(2) constraint uni_tab1_deptno_uk UNIQUE,
dname varchar2(14),
loc varchar2(13));
CREATE TABLE uni_tab2(
deptno number(2),
dname varchar2(14),
loc varchar2(13),
Constraint uni_tab2_deptno_uk UNIQUE (deptno));
CREATE TABLE sawon_4
( s_no number(2),
s_name varchar2(10),
s_email varchar2(20),
constraint sawon_4_uk UNIQUE( s_name, s_email));NOT NULL
- 해당 컬럼에 NULL 값이 저장되는 것을 방지하는 제약조건이다.
- 테이블 레벨 방식으로는 사용할 수 없으며 반드시 컬럼 레벨 방식으로만 사용이 된다.
--컬럼 레벨
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입, [CONSTRAINT 제약조건명] UNIQUE,
컬럼명 데이터타입,
...
);CHECK
- 해당 컬럼에 저장되는 데이터를 검사하여 조건과 일치하는 데이터만 저장이 가능하도록 처리하는 제약조건이다.
- 조건으로는 데이터의 값의 범위나 특정 값과 일치하는 숫자 및 문자 데이터를 설정할 수 있으며 SELECT문의 WHERE절에서 사용했던 IN, AND/OR, 비교 연산자등과 함께 사용이 가능하다.
--컬럼 레벨
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입, [CONSTRAINT 제약조건명] CHECK(조건식),
컬럼명 데이터타입,
...
);
-- 테이블 레벨
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입,
컬럼명 데이터타입,
[CONSTRAINT 제약조건명] CHECK(조건식)
);
CREATE TABLE sawon_7
(s_no number(2),
s_name varchar2(10),
s_sal number(10) constraint sawon_7_s_sal_ck check(s_sal < 500));
CREATE TABLE sawon_7
(s_no number(2),
s_name varchar2(10),
s_sal number(10) constraint sawon_7_s_sal_ck check(s_sal < 500));FOREIGN KEY
- 외래키 또는 참조키라고 부르며, 해당 테이블에서 다른 테이블을 참조할 때 올바른 데이터 값만 참조 가능하도록 하는 제약방법이다.
--문법
--컬럼 레벨
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입, [CONSTRAINT 제약조건명] REFERENCES 부모테이블명(컬럼명),
컬럼명 데이터타입,
...
);
-- 테이블 레벨
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입,
컬럼명 데이터타입,
[CONSTRAINT 제약조건명] FOREIGN KEY(컬럼명)
REFERENCES 부모테이블명(컬럼명)
);
CREATE TABLE emp02
(
empno number(4) constraint emp02_empno_pk primary key,
ename varchar2(15),
deptno number(2) constraint emp02_deptno_fk references dept02(deptno)
);
CREATE TABLE emp03
(
empno number(4) constraint emp03_empno_pk primary key,
ename varchar2(15),
deptno number(2),
constraint emp03_deptno_fk foreign key(deptno) references dept02(deptno));
- 참조하는 부모 테이벌의 컬럼은 반드시 기본 키 또는 UNIQUE 키로 제약조건이 설정된 컬럼이어야 된다.
FOREIGN KEY 제약조건의 추가 옵션
- 자식테이블이 참조하는 있는 컬럼의 값을 부모테이블에서 삭제하려고 하면 삭제가 되지 않는다. 그 이유는 참조 무결성 때문이다.
- 이러한 종속적인 상황에서 해결 가능한 방법들이 있다.
1) ON DELETE CASCADE- 이 옵션은 참되는 부모 테이블의 행이 삭제되면, 해당 행을 참조하는 자식 테이블의 행도 같이 연쇄적으로 삭제되도록 한다.
CREATE TABLE emp02 ( empno number(4) constraint emp02_empno_pk primary key, ename varchar2(15), deptno number(2) constraint emp02_deptno_fk references dept02(deptno) on delete cascade );2) ON DELECT SET NULL
- 참조되는 부모 테이블의 행이 삭제되면, 해당 행이 참조하는 자식 테이블의 컬럼 값을 NULL로 설정한다.
CREATE TABLE emp02 ( empno number(4) constraint emp02_empno_pk primary key, ename varchar2(15), deptno number(2) constraint emp02_deptno_fk references dept02(deptno) on delete set null );
DROP TABLE
- DB에서 해당 테이블을 제거하는 방법이다.
- FOREIGN KEY 제약조건을 제외한 모든 제약조건이 같이 삭제된다.
- FOREIGN KEY 제약조건은 CASCADE CONSTRAINTS 옵션을 지정해서 삭제하면 연쇄적으로 제약조건도 삭제되기 때문에 부모 테이블을 삭제할 수 있다.
DROP TABLE 테이블명 [CASCADE CONSTRAINTS];
DROP TABLE DEPT02;
DROP TABLE DEPT02 CASCADE CONSTRAINTS;
데이터데이터데이터!!