[DB] 제약 사항, ERD(Entity Relationship Diaglam)

* 제약 사항

- 컬럼에 원하는 값을 받기위해서 설정하는 것이다.

- primary key(P), foreign key(R), unique(U), [not null, check](C), default가 있다.

- 제약사항은 user_constraints테이블에서 확일할 수 있다.

- 제약사항은 테이블 생성시 컬럼 단위 제약사항 또는 테이블 단위로 설정 할 수 있다.

- alter를 사용하면 제약사항의 추가, 삭제, 활성화, 비활성화를 할 수 있다.

 

+ primary key(기본키)

- 중복값을 허용하지 않고, null을 혀용하지 않는다.

- 유일한 값으로 관리해야 할 때 사용한다.                                                                                                             - - 다른 테이블에서 참조할 수 있다.(관계)

- Oracle은 자동으로 index를(user_indexes) 생성한다.

- 테이블에 하나를 설정한다.(하나 또는 여러개의 컬럼이 조합될 수 있다.)

- 문법

+ column 단위

create table 테이블명(

컬러명 데이터형(크기) [constraint 제약사항명](생략할 경우 oracle이 명명) primary key,,,

);

+ table 단위

create table 테이블명(

컬럼명, 데이터형(크기),,,,

constraint 제약사항명 primary key(적용 컬럼,,,),,,

);

[참고] 제약사항명은 PK_테이블명 형식으로 지정해준다.

select * from user_constraints;

select * from user_indexes;

 

--------------------------------------------------------------

-- primary key

--------------------------------------------------------------

 

-- *컬럼단위 제약사항 ----------------------------------------

 

-- **primary key만 명시 했을 때,----

create table column_primary(

name varchar2(15 byte),

id varchar2(16 byte) primary key,

address varchar2(100 byte)

);

-- *위 테이블에 데이터 추가 실패 예제

insert into column_primary(id) values('kim');

-- └insert ok

insert into column_primary(id) values('kim');

-- └error msg1 : 무결성 제약 조건(SCOTT.SYS_C0011146)에 위배됩니다.(중복 배제)

insert into dept(deptno) values(10);

-- └error msg2 : 무결성 제약 조건(SCOTT.PK_DEPT)에 위배됩니다

/*

위 예제 결론 : primary key만 사용하면 제약사항명이 자동생성되므로 제약사항에 위배가 되었을때 어떤 제약사항인지 판단이 어렵다.

이름을 명시 하지 않은 error msg1보다 제약사항의 이름을 명시해준 error msg2가 문제에 대해 직관적으로 쉽게 알 수 있다.

*/

 

drop table column_primary;

purge recyclebin;

 

-- **제약사항명을 명시 했을 때,----

create table column_primary(

name varchar2(15 byte),

id varchar2(16 byte) constraint pk_column_primary primary key,

address varchar2(100 byte)

);

-- *위 테이블에 데이터 추가 성공 예제

-- 1. 최초로 데이터를 입력하는 경우

insert into column_primary(name, id, address) values ('김기범', 'bumkey', '대구');

select * from column_primary;

-- 2. 아이디가 다른 경우(다른 값은 다 같아도 무관하다.)

insert into column_primary(name, id, address) values ('김기범', 'little freak', '대구');

select * from column_primary;

 

-- *위 테이블에 데이터 추가 실패 예제

-- 1. 아이디가 같은 경우

insert into column_primary(name, id, address) values ('김똑띨', 'bumkey', '프랑스 대구');

select * from column_primary;

-- └error msg : 무결성 제약 조건(SCOTT.PK_COLUMN_PRIMARY)에 위배됩니다

-- 2. null이 들어가는 경우

-- (컬럼의 데이터형이 정수인 경우에는 컬럼을 생략하여 추가 할 때 null이 들어간다.)

-- (컬럼의 데이터형이 문자열인 경우에는 컬럼을 생략하거나 ''을 사용하면 null이 입력된다.)

insert into column_primary(name, id, address) values ('이진기', '', '광명시');

insert into column_primary(name, address) values ('이진기', '광명시');

-- └error msg : NULL을 ("SCOTT"."COLUMN_PRIMARY"."ID") 안에 삽입할 수 없습니다

 

--------------------------------------------------------------

 

 

 

-- *테이블 단위 제약사항 -----------------------------------------

create table table_primary(

name varchar2(15 byte),

id varchar2(16 byte),

address varchar2(100 byte),

constraint pk_table_primary primary key(id)

);

select * from table_primary;

 

-- *여러개의 컬럼이 primary key인 경우

create table multi_primary(

item varchar2(30 byte),

lot char(5 byte),

p_date char(10 byte),

constraint pk_multi_primary primary key(lot,p_date)

);

-- *위 테이블에 데이터 추가 성공 예제

-- 1. 날짜가 다르다면 lot번호는 중복될 수 있다.

insert into multi_primary(item, lot, p_date) values('미사일', 'L-001', '2014-09-26');

select * from multi_primary;

insert into multi_primary(item, lot, p_date) values('미사일', 'L-001', '2014-09-25');

select * from multi_primary;

-- 2. lot번호가 다르다면 날짜는 중복될 수 있다.

insert into multi_primary(item, lot, p_date) values('핵폭탄', 'L-002', '2014-09-26');

select * from multi_primary;

insert into multi_primary(item, lot, p_date) values('나무방패', 'L-003', '2014-09-25');

select * from multi_primary;

-- *위 테이블에 데이터 추가 실패 예제

-- 날짜와 lot가 같을 때

insert into multi_primary(item, lot, p_date) values('나무칼', 'L-004', '2014-09-26');

select * from multi_primary;

insert into multi_primary(item, lot, p_date) values('나무칼', 'L-004', '2014-09-26');

select * from multi_primary;

-- └error msg : 무결성 제약 조건(SCOTT.PK_MULTI_PRIMARY)에 EMP위배됩니다

 

select * from user_indexes;

 

-----------------------------------------------------------------

 

 

+ foreign key(참조키, 외래키)

- 다른 테이블(부모 테이블)의 컬럼값을 참조하여 현재 테이블(자식 테이블)에 컬럼값을 넣을 때

- 테이블간의 관계가 설정된다.

- 부모 테이블의 PK 또는 Unique로 설정된 컬럼만 참조할 수 있다.

- null을 허용한다. 따라서 1 : 0관계가 될 수도 있다.

- 자식 테이블의 컬럼명은 부모 테이블과 같은 필요는 없다. 데이터형과 크기만 같으면 된다.

- 문법

+ column 단위

create table 테이블명(

컬럼명 데이터형(크기) constraint 키명 references 부모 테이블명 (참조할 컬럼명),,,

...

);

+ table 단위

create table 테이블명(

컬럼명 데이터형(크기),

...

constraint 키명 foreign key(적용할 컬럼) references 부모 테이블명(참조할 컬럼명),,,

...

);

[참고] 제약사항명은 FK_컬럼명 형식으로 지정해준다.

--------------------------------------------------------------

-- foreign key

--------------------------------------------------------------

 

-- *컬럼단위 제약사항 ----------------------------------------

 

-- 문) column_primary 테이블의 id컬럼을 외래키로 참조하는 테이블을 생성하라.

create table column_foreign(

id varchar2(16 byte) constraint fk_id references column_primary(id),

subject varchar2(30 byte),

score number(3)

);

select * from column_foreign;

select * from user_constraints;

 

-- *위 테이블에 데이터 추가 성공 예제

-- 1. 같은 id로 추가 가능

insert into column_foreign(id, subject, score) values ('bumkey', 'oracle', 55);

select * from COLUMN_PRIMARY;

select * from COLUMN_FOREIGN;

insert into column_foreign(id, subject, score) values ('bumkey', 'oracle', 84);

-- 2. 다른 id로 추가 가능

insert into column_foreign(id, subject, score) values ('little freak', 'oracle', 84);

-- 3. null도 추가 가능

insert into column_foreign(id, subject, score) values ('', 'oracle', 73);

insert into column_foreign(subject, score) values ('oracle', 74);

 

-- *위 테이블에 데이터 추가 실패 예제

-- 1. 부모 테이블에 없는 값이 들어갔을 때

insert into column_foreign(id, subject, score) values ('Key', 'oracle', 90);

-- └error msg : 무결성 제약조건(SCOTT.FK_ID)이 위배되었습니다- 부모 키가 없습니다

 

-- *FK설정해둔 값 삭제 예제

-- 1. 부모 자식 관계가 있는 경우

-- (부모테이블의 값을 참조하는 컬럼이 있다면 부모테이블에서 해당 값은 삭제되지 않는다.)

delete from column_primary where id = 'bumkey';

-- └error msg : 무결성 제약조건(SCOTT.FK_ID)이 위배되었습니다- 자식 레코드가 발견되었습니다

 

-- 2. 부모 자식 관계가 없는 경우

delete from column_primary where id = '밍뭉';

 

-- 3. 부모 자식 관계가 있을 때 자식레코드를 모두 삭제한 후 부모 테이블에서 삭제할 때는 삭제가 가능하다.

delete from column_foreign where id = 'bumkey';

delete from column_primary where id = 'bumkey';

select * from COLUMN_PRIMARY;

select * from COLUMN_FOREIGN;

--------------------------------------------------------------

 

 

 

 

-- *테이블단위 제약사항 ----------------------------------------

 

-- *제약사항명은 테이블이 다르더라도 중복될 수 없다.

create table table_foreign(

id varchar2(16 byte),

subject varchar2(30 byte),

score number(3),

constraint fk_id foreign key(id) references column_primary(id)

);

-- └error msg : 기존의 제약에 사용된 이름입니다

create table table_foreign(

id varchar2(16 byte),

subject varchar2(30 byte),

score number(3),

constraint fk_f_id foreign key(id) references column_primary(id)

);

select * from user_constraints;

 

--------------------------------------------------------------

 

 

* ERD(Entity Relationship Diaglam)

- 테이블 도식화

- 도형과 선으로 테이블의 구조와 관계를 보여준다.

- 논리 모델(글)과 물리 모델(컬럼명, 데이터형, 크기 설정) 두 가지로 그린다.

──── 식별관계 : PK인 컬럼을 다른 테이블에서 PK로 참조하여 사용

- - - - - 비식별관계 : PK인 컬럼을 다른 테이블에서 일반 컬럼이 참조할 때