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"
다른 테이블과 관계가 맺어졌기 때문에 얘만 삭제할 수 없다.
도움이 되셨다면 공감눌러주시고 궁금한 점 댓글 남겨주세요~
'프로그래밍 > Database' 카테고리의 다른 글
[데이터베이스] 트랜잭션에 대해서 (0) | 2017.05.28 |
---|---|
IntelliJ에서 JDBC 테스트하기 (1) | 2017.04.17 |
[오라클] Grouping 과 Subquery (0) | 2017.03.31 |
[오라클] Join문과 Aggregation (2) | 2017.03.30 |
[ERD] eXERD 설치와 회원가입 모델링 하는 법 (0) | 2017.03.27 |