[오라클] DDL & DML

DDL 예제를 돌려보자.

기본 생성된 베이스테이블과 데이터는 http://pjh3749.tistory.com/156 여기서 sql파일을 받아서 developer에 때려넣자.

create table history1 as select * from emp

데이터도 이렇게 똑같이 복사된다.

그렇다면 안의 데이터는 없이 그냥 테이블만 복사하려면 어떻게 해야할까?

create table history2 as select * from emp where 1=0

이렇게 칼럼만 있고 데이터는 복사가 되지 않음을 알 수 있다.

# 테이블 구조 살펴보기

desc history1

# 제약 조건

Constraint 

Database에 테이블 레벨에서 특정한 규칙을 설정해둠

예상치 못한 데이터의 손실이나 일관성을 어기는 데이터의 추가, 변경 등을 예방함

NOT NULL

NULL값은 들어올 수 없음

UNIQUE

중복된 값을 허용하지 않음 NULL은 들어올 수 있음. NULL이 unique인지 아닌지 DBMS마다 다르지만 안보는 경우도 있다. (NULL은 다 다른값으로 보는 경우이다)

PRIMARY KEY

NOT NULL + UNIQUE (인덱스자동생성)

CHECK

임의의 조건 검사, 조건식이 참이이어야 변경 가능

CREATE TABLE book (

rate NUMBER (rate IN (1,2,3,4,5))

);

FOREIGN KEY

참조 무결성 제약

일반적으로 REFERENCE 테이블의 PK를 참조

CREATE TABLE book 

( … author_id NUMBER(10), 

CONSTRAINT c_book_fk FOREIGN KEY (author_id)

 REFERENCE author(id) 

ON DELETE SET NULL 

);

author(id)는 원본테이블의 id칼럼을 의미한다.

# 제약조건 생성

CREATE TABLE book(

 id NUMBER(5) CONSTRAINT book_id_pk PRIMARY KEY,

 name VARCHAR2(20) CONSTRAINT book_name_not_null NOT NULL,

 price NUMBER(12,2) CONSTRAINT book_price_check CHECK (price > 0),

 isbn VARCHAR2(14) CONSTRAINT book_isbn_unique UNIQUE,

 pub_date DATE DEFAULT SYSDATE

 );

constraint 다음에 나오는 것이 제약조건의 이름이다.

# 여러 질의들 테스트

CREATE TABLE job( id NUMBER(3) CONSTRAINT job_id_pk PRIMARY KEY, name VARCHAR(5) NOT NULL );

create table author (

  id number(5) constraint author_id_pk primary key,

  name varchar(20) constraint autor_name_not_null not null,

  gender char(1) default 'm',

  age number(2),

  job_id number(3),

  constraint author_gender_check CHECK (gender in('M','F')),

  constraint author_job_id_fk FOREIGN KEY (job_id) references job(id)

);

create table auther_book(

  author_id number(5),

  book_id number(5),

  author_order number(2) default 1,

  constraint authorbook_author_id_fk foreign key (author_id) references author(id) on delete cascade,

  constraint authorbook_book_id_if foreign key(book_id) references book(id) on delete cascade,

  constraint authorbook_pk primary key(book_id, author_id, author_order)

  );

 

on delete cascade는 해당하는 fk를 가진 참조행도 삭제하는 것이다. 부모 테이블의 레코드가 날아가면 이에 상응하는 자식 테이블의 내용도 같이 날아가는 것을 말한다.

모델구조는 이렇게 나온다.

# 제약조건 확인

select constraint_name, constraint_type, search_condition

from user_constraints

where table_name = 'BOOK'

테이블이름을 항상 대문자로해야한다. 그렇지 않으면 결과값이 나오지 않는다.

## 생성한 테이블들의 동작 테스트

INSERT INTO book VALUES (1, 'C++ INTRO', 20000, '15-222-22222', '07/01/02');

INSERT INTO book VALUES (2, 'JAVA PRIMER', 50000, '11-111-1111', DEFAULT); 

# UNIQUE컬럼에 NULL은 여러행이 있을 수 있나? 

INSERT INTO book VALUES (4, 'TEST BOOK', 30000, NULL, DEFAULT);

INSERT INTO book VALUES (5, 'TEST BOOK', 30000, NULL, DEFAULT); 

있을 수 있다.

# NULL체크?

INSERT INTO author VALUES (3, 'CHOI', NULL, 40, 3);

세 번째 칼럼은 NULLABLE 이 true이고 디폴트값으로 아까 m을 넣었었다. 하지만 null로 넣으면 디폴트값이 들어가지않고 진짜 null이 들어간다.

# FK의 reference값이 없다면?

INSERT INTO author VALUES (5, 'KWON', 'M', 33, 5);  

마지막 5는 job_id로 job의 id를 참조한다. 하지만 job테이블에는 아이디가 5인것이 없다.

오류 보고 -

SQL 오류: ORA-02291: 무결성 제약조건(PARK.AUTHOR_JOB_ID_FK)이 위배되었습니다- 부모 키가 없습니다

# 제약조건 추가

alter table auther_book

add constraint authorbook_bookorder_unique UNIQUE(book_id, author_order);

이렇게 제약조건탭에서 constraint_type이 추가된것을 볼 수 있다.

# BOOK이나 AUTHOR 테이블의 행이 삭제될때 author_book 테이블은 어떻게 변하는가? 

delete from author where id = 4;

cascade로 인해 자식테이블의 레코드도 지워졌다.

# Truncate table

truncate table job;

오류 보고 -

SQL 오류: ORA-02266: 사용으로 설정된 외래 키에 의해 참조되는 테이블의 고유/기본 키

02266. 00000 -  "unique/primary keys in table referenced by enabled foreign keys"

다른 테이블과 관계가 맺어졌기 때문에 얘만 삭제할 수 없다.

도움이 되셨다면 공감눌러주시고 궁금한 점 댓글 남겨주세요~