Oracle SQL(8)

YangJiWon·2021년 1월 14일

db oracle sql

DB

목록 보기

8/12

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;

YangJiWon

데이터데이터데이터!!

이전 포스트

Oracle SQL(7)

다음 포스트

Oracle SQL(8)

DB

DDL

CREATE

오라클의 데이터 타입

DEFALUT 옵션

서브쿼리를 이요한 테이블 생성

제약 조건

PRIMARY KEY

USER_CONS_COLUMNS 데이터 사전

UNIQUE

NOT NULL

CHECK

FOREIGN KEY

FOREIGN KEY 제약조건의 추가 옵션

DROP TABLE

Oracle SQL(7)

Oracle SQL(9)

0개의 댓글