[210520목] 관계형성(foreign key), 시퀀스 생성, on delete cascade

회사에 가서 테이블을 생성하고 데이터를 삽입하는 경우는 많지 않다. 

 

[테이블 생성 : create table 테이블명] 

primary key를 의미하는 컬럼명은 유일해야 한다(unique). 

constraint_name은 제약조건에 이름을 주는 것!

 

- 웹사이트 개설 : 회원을 관리할 테이블(mytable)
- 수집할 정보: 아이디(pk), 비밀번호(nn), 이름(nn), 전화번호(nn), 이메일, 성별, 주소 

전화번호가 입력되지 않았으면, 자바스크립트로 입력하라고 함. 

 

전체 데이터를 컬럼 개수와 타입에 맞게 순서대로 넣는다면 컬럼명 생략 가능하다.

전우치의 경우, 데이터가 컬럼 수만큼 들어가기 때문에 컬럼명을 생략해도 된다. 

 

자습시간에 오류난 곳부터 다시 하는 중

not null과 같은 제약조건이 있는 칼럼명의 데이터는 null로 두지 않고, 반드시 데이터를 삽입해야 한다. 

데이터를 생략한 경우에는 null이나 default의 값이 들어간다. 

 

 

 

[테이블 수정  UPDATE]

: 존재하는 레코드의 값을 수정할 때 사용 반드시 where절을 사용해야 한다. 

: 테이블 수정시 조건절없이 update 테이블명 set 컬럼명 = '93/02/02';라고 사용하게 되면 해당 table의 해당 컬럼의 모든 데이터가 동일하게 바뀐다. 그것을 방지하기 위해 반드시 where절을 사용해야 한다.  

update 테이블명 set (데이터를 수정하려는) 컬럼명  = '데이터' where 특징이 되는 컬럼명 = ' ';

데이터를 넣을 때도 '=', 값이 같냐고 물어볼 때도 '=' 이다 

 

업데이트를 잘못했을 때 되돌릴 수 있도록 commit,  rollback을 안했다면 데이터 삽입한 것이 전부 사라진다. 

rollback을 한 이후 다시 원래의 데이터로 돌아간 것을 확인할 수 있다. rollback의 위치는 데이터 삽입 후 저장이 완료된 commit 이후의 위치로 선택한다. 

데이터 update 수정 시에는 where조건절을 사용하지 않으면 전체 데이터가 변경되기때문에 조건절(where)을 꼭 사용해줘야 한다. 

 

[2교시]

[데이터 삭제 delete] 

데이터를 삭제하기 전에는 습관적으로 commit을 사용해주세요. 

 

delete 또한 where 조건과 항상 같이 사용해야 한다.

 

delete의 명령문은 간단하다. 

delete 테이블명

where 조건; 

delete하고 조건을 주지 않으면 데이터 전체가 삭제가 된다. 

이전에 데이터 삽입 후 commit을 입력했기때문에 데이터 삭제 후에도 rollback을 통해 기존의 데이터를 가져올 수 있다. 

 

조건절에 해당하는 데이터가 삭제되었다. 

rollback을 한 후 다시 commit해서 데이터 다시 가져와 저장하기 

 

 

[연습문제]

테이블 userinfo,  컬럼(userid, userpwd, username)을 만들어 주세요.

제약조건 이름을 설정할 때, constraint userinfo_id_pk, constraint와 userinfo 사이는 연결이 떨어져 있다. 자꾸 '_'를 넣으려는 실수를 한다. 

 

 

★[관계 형성]★

테이블명이 guestbook인 테이블을 생성해주세요. 컬럼(bookseqno(number), useid, text, regdate)

테이블 userinfo가 부모, 테이블 guestbook이 자식 

테이블 guestbook에 부모 테이블 userinfo와 같은 userid라는 foreign key를 줄 것이다. 

관계를 형성하려면 타입과 사이즈를 같게 하는 것이 좋다. 

 

▶ foreign key 관계형성

컬럼명 데이터타입 reference 부모테이블명(PK를 가지고 있는 컬럼명)

 

 userid varchar2(20) references userinfo(userid)

: 테이블 userinfo의 primary key인 userid를 foreign key로 가지고 있는 guestbook의 userid

부모와 자식 테이블을 연결하면(관계가 형성되면) 부모의 데이터를 지우면 안된다. 

 

컴퓨터가 문제가 많다 오늘 자습시간에 다시 해보기 ... 화가 난다. ...  

지금은 자습시간.. 자습시간에 해도 안맞는다 오라클 나와 맞지 않아...!

 

일련번호를 만들어 사용해도 n번까지 데이터를 기억하는 것은 쉽지 않다. 그렇기 때문에 일련번호 발생기를 이용한다.

bookseqno 일종의 일련번호 

데이터 삽입 시 regdate를 생략할 것 

 

일련번호 발생기가 없는 경우, 데이터 넣기 ↓

insert into 테이블명

         (                             )

values 

         (                             ); 

 

 

 

[자주 발생하는 오류 살펴보기]

(일련번호 발생기를 생성 전이라 임의로 데이터 삽입) 

 

▶ 오류  ORA-0001

: UNIQUE의 특성(유일성 제약조건)에 맞게 유일해야 하는 PK가 중복이 되어서 발생하는 오류 

 

해결방법 : UK, IK, PK, FK와 같이 테이블에 단 하나만 존재해야하는 key를 중복해서 넣을때 발생하는
에러이므로 db에 insert되는 데이터를 점검해서 key값이 중복되지 않게 해야 합니다.

 

▶ 오류 ORA-02291

: 패어런츠 키가 존재하지 않는다. 

: 관계 형성 후 부모테이블에 존재하지 않는 값을 자식테이블에 넣으려고 할 때 에러가 발생한다. 

 

부모테이블에 pk 2가 없어서 나는 오류이다. 

 

★ 요약해서 기존에 부모테이블에 존재하는 pk를 가진 데이터만 넣을 수 있고 pk는 유일해야하는데 중복되면 입력 오류, 입력되지 않는다.

 

해결방법: 

case1. 자식테이블에 있는 데이터가 부모테이블에 없는 경우→ 부모테이블에 자식테이블에 들어갈 데이터를 넣어준다. 

case2. 자식테이블에 있는 데이터가 부모테이블에 있으면 안되는 경우 → 관계조건인 부모테이블과 자식테이블의 연결되어 있는 외래키(FK)를 삭제한다. 

 

 

▶ 오류  ORA-02292

부모 테이블의 데이터 삭제시 child, 자식 테이블에 글이 존재하면 삭제할 수 없다.  

→ '홍길동'이 입력한  data(글)가 있기 때문에 삭제할 수 없다. 

자식테이블에서 글쓴 적이 없는(데이터를 삽입하지 않은) 'soony'는 삭제할 수 있다. 

→ child 테이블에 글이 없으면 삭제 가능 

 

부모 테이블 userinfo를 삭제하고 싶은 경우라도 drop table userinfo;로 삭제되지 않는다.

이런 경우 해결방법은? 2가지가 있다.  

 

 

▶ 1. 자식 데이터를 먼저 지우고, 부모테이블의 레코드를 삭제한다.

→ 자식을 먼저 drop시키고 부모를 drop시킨다.

 

①drop table guestbook 

②drop table userinfo

: 자식을 먼저 drop, 부모를 나중에 drop

 

 

▶ 2. 혹은 부모테이블의 글을 지우면 자식테이블의 모든 글이 전부 함께 삭제되도록 옵션on delete cascade을 생성한다.

[자식테이블에 on delete cascade]라는 옵션을 설정해준다. on delete cascade는 자식 테이블을 생성할 때 만든다. 

 

[on delete cascade 옵션이 걸리는 guestbook 테이블을 다시 만들어주세요.]

on delete cascade은 자식 테이블을 생성할 때 만들기 때문에 기존에 있던 테이블 guestbook를 삭제하고, on delete cascade가 있는 테이블 다시 생성한다. 

on delete cascade는 foreign key가 있는 곳에 설정해야한다.

foreign key는 데이터 타입과 references 사이에 직접 쓰기도 하지만 실제로 넣지 않는 경우가 많다. 

 

★ foreign key 설정

: foreign key에 해당하는 컬럼에 컬럼명 테이터타입(크기) references 부모테이블명(primary key)

 

자식테이블의 foreign key에 해당하는 코드의 마지막에 on delete cascade를 넣어준다. 

userid varchaer2(20) references userinfo(userid) on delete cascade; 

→ on delete cascade를 설정시 부모테이블인 userid가 삭제되면 자식 테이블의 userid도 같이 삭제된다. 

on delete cascade 옵션에 걸리면 부모테이블의 userid가 삭제되면 자식테이블의 userid도 삭제된다. 부모가 삭제되면 자식테이블까지 모두 삭제된다. drop은 테이블 삭제, delete는 테이블 안에 존재하는 데이터 삭제 

 

selet * from guestbook;을 통해 데이터를 조회할 경우, 해당 데이터가 조회된다. 

자식테이블 foreign key이기 때문에 duplicate되어 있음 

 

DELETE USERINFO WHERE USERID ='HONG';와 같이 부모가 삭제되면 조건절에 해당되는 자식테이블도 삭제된다. 

일반 사이트의 경우, 회원 탈퇴하더라도 글은 남아있다. 이것이 의미하는 것은 관계형성이 되어 있지 않는 것이다. 회원탈퇴 후 삭제되는 정보는 알라딘 사이트로 예를 들자면 판매정보, 구매정보테이블, 장바구니 등등 부모-자식간의 관계를 맺고 있다.

 

 

[일련번호 발생기(sequence라는 객체)와 함께 데이터 입력하기] 

▶ 시퀀스 생성 : create sequence 객체명, ex) create sequence guestbookseq;

: 객체명에는 시퀀스라는 것을 알아보기 쉽게 이름을 짓는 것이 좋다. 보통 테이블명 + seq로, 1, 2, 3번 ....  

 

▶ 시퀀스 삭제 drop sequence  객체명

 

[참고로 알아만 두기] 

increment by 1 : 1씩 증가, 혹은 숫자를 바꿔 원하는 숫자부터 시작할 수 있다. 

start with 1 : 1부터 시작, 혹은 숫자를 바꿔 원하는 숫자부터 시작할 수 있다. 

max value 10000 

→ 이와 같은 것을 사용하지 않으면 dafault처럼 1씩 증가, 1부터 시작한다.  

create sequence guestbookseq;

sequence GUESTBOOKSEQ가 생성되었습니다. => 일련번호가 발생되었습니다. 

 

일련번호가 발생될 것을 테이블 guestbook에 bookseqno에 넣을 것이다. 그렇기 때문에 기존의 테이블 guestbook이 bookseqno의 값을 가지고 있으면 안되기 때문에 기존에 있던 테이블을 삭제해 주는 것이다. 

 

테이블을 삭제 후 아무것도 없는 상태에서 데이터를 넣어줄 것이다. 

일련번호 발생기를 사용한 후 데이터를 삽입할 때에는 다음과 같이 데이터를 삽입한다. 

insert into 테이블명

         (컬럼명1, 컬럼명2, 컬럼명3....   )

values 

         (시퀀스객체명.nextval, 컬럼명2의 데이터, 컬럼명3의 데이터...  ); 

 

시퀀스 객체명.nextval : 다음 값을 발생시켜서 넣어라! ex) guestbook.nextval

시퀀스의 단점 : nextval은 오류가 발생해도 seq(일련번호)를 발생시킨다. 

 

[연습문제]

번호발생을 다시 하고 싶으면 drop하고 다시 작성해야 한다. 

 

▶ 연습문제 조건

 

테이블 userinfo 생성:

 

테이블 bookinfo 생성:

 

테이블 saleinfo 생성:

위의 사진은 잘못된 것,  table saleinfo에는 table bookinfo와 관계를 형성할 isbn이 foreign key로 들어가야 한다. 

부모테이블의 데이터가 삭제될 때 자식 테이블의 데이터 역시 삭제될 수 있도록 on delete cascade를 넣어준다. 

 

 

<부모테이블과 자식테이블의 관계> 

[1: 다] 부모는 하나, 자식은 여러 개를 나눠 가질 수 있다.  

 

[다: 다]

- 이론적으로만 있고(개념적 모델), 실제로 사용되는 물리적 단계에서는 사용하지는 않는다.

- 설계 단계에서 다 : 다 관계로 만들었다면 1: 다 로 반드시 바꾸어 줘야 한다.