DAY-26. Oracle DDL

🔐 2022-04-06 -2

데이터베이스

❗ 수업을 듣고, 개인이 공부한 내용을 적은 것 이기에 오류가 많을 수도 있음

2022-04-06-2

🔔 DDL (Data Definition Language)

데이터 정의어

데이터베이스 객체 생성(create), 수정(alter), 삭제(drop) 하는 구문

제약조건 (constraints)

테이블을 생성할 때 구성하는 컬럼에 들어갈 데이터에 대해 제약조건을 설정하는 것

→ 데이터의 일관성과 정확성을 유지하기 위해서(데이터 무결성)

1. not null : 해당 컬럼에 null 값이 들어갈 수 없음 → null 값 허용 x
2. unique : 중복된 값을 허용 x → null값도 들어간다
3. primary key(기본키) : not null + nuique → null값 허용 x 중복 값 허용 x → 컬럼의 고유 식별자로 사용 → 하나의 테이블에서 한번 사용 가능
4. foreign key(외래키) : 두 테이블 간의 관계를 설정 → A테이블(id,pw…) B테이블(member_id, count…)가 존재한다고 가정 → B테이블의 member_id 컬럼에 들어갈 수 있는 값이 A테이블의 id에 있는 데이터여야하는 경우
5. check : 해당 컬럼에 저장 가능한 값의 범위 조건을 지정해서 설정한 값만 허용

---

🚀 제약조건을 거는 방식

• 컬럼 레벨 : 컬럼명 자료형 옆에 제약조건명을 명시
• 테이블 레벨 : 컬럼을 모두 정의한 후 마지막에 제약조건명(컬럼명) 형식으로 제약조건을 걸어주는 방식

🔎
create table user_cons (
    no number not null unique,   // not null 과 unique를 같이 사용
    ,pw varchar2(100) not null
    ,name varchar2(100) not null
    ,gender varchar2(10)
);

🚀 primary key

🔎
// 두 개의 컬럼을 primary key로 묶을 때
create table user_cons (
    no number
    ,id varchar(100)
    ,pw varchar2(100) not null
    ,gender varchar2(10)
    ,primary key (no,id)
);

insert into user_cons values(1,'11234','tom','남');
insert into user_cons values(1,'555aa','kacey','여');  // 가능
insert into user_cons values(1,'555aa','kacey','여'); // unique constraint 발생

• primary key를 하나의 컬럼이 아니라 여러개의 컬럼에 대한 복합키 형태로 적용을 하고 싶다면 → 컬럼레벨에서는 불가 → 테이블 레벨 사용
• primary key가 no, id 이다 → no,id를 하나의 셋트로 보고 → 둘다 같은 데이터를 insert를 하면 에러 발생

🚀 foreign key

참조된 다른 테이블이 제공하는 값만 사용할 수 있도록 제약하고 싶을 때

→ 참조하는 컬럼, 참조되는 컬럼을 통해 두 테이블간에 관계가 생성

→ 참조하는 컬럼이 참조되는 컬럼에 있는 값과 일치하거나, NULL 값만 가질 수 없음

// student 테이블
create table student (
    id varchar2(100) primary key
    ,name varchar2(100) not null
    ,age number not null
);

insert into student values('001','tom',20);
insert into student values('002','salley',30);
insert into student values('003','chole',25);

// borrow 테이블 1
create table borrow (
    book_id varchar2(100) primary key
    ,std_id varchar2(100)
    ,rent_date date
    ,foreign key (std_id) references student (id)  // foreign key (참조하는 컬럼) references 참조대상테이블 (참조대상 컬럼명)
);

insert into borrow values('500','005',sysdate);  // 에러 발생 -> 005가 문제
insert into borrow values('500','002',sysdate);  // 에러 발생 x 부모키에 있는 id만 입력가능

// borrow 테이블 2
create table borrow(
    book_id varchar2(100) primary key
    , std_id varchar2(100) references student(id) // 컬럼명 자료형 reference 참조대상 (참조대상 컬럼명)
    , rend_date date
)

insert into borrow values('500','002',sysdate); // 에러 발생 x

• 참조하는 컬럼 : 참조 대상을 참조하는 테이블의 컬럼 (borrow 테이블)
• 참조되는 컬럼 : 참조 대상이 되는 테이블 컬럼(student 테이블)

---

삭제

• 부모 테이블에서 데이터를 삭제 할 때 자식 테이블에 있는 데이터를 어떤 방식으로 처리할지 → 제약조건을 걸 때 삭제옵션도 함께 걸어줌
• 기본 삭제 옵션은 on delete no action → 자신을 참조하는 데이터가 있다면 삭제 할 수 없다.(default)

🔎
// 참조되고 있는 id -> 002 -> 삭제
// 참조되고 있는 컬럼의 데이터를 가지고 있는 자식 컬럼이 있다면 원본 테이블의 데이터 삭제
// borrow에도 id가 002가 존재

delete from student where id = '002';  // integrity constraint violated - child record found

1) on delete set null

🔎
create table student (
    id varchar2(100) primary key
    ,name varchar2(100) not null
    ,age number not null
);

insert into student values('001','tom',20);
insert into student values('002','salley',30);
insert into student values('003','chole',25);

create table borrow(
    book_id varchar2(100) primary key
    , std_id varchar2(100) references student(id) on delete set null // 컬럼명 자료형 reference 참조대상 (참조대상 컬럼명)
    , rend_date date
);
insert into borrow values('500','002',sysdate);

delete from student where id = '002';  // student에서 002행 사라진다
select * from borrow; // 기존에 가지고 있던 002 값이 사라진다

• 부모의 데이터가 삭제되면 자식의 데이터가 null로 바뀜 → 기존의 borrow 테이블의 std_id 가 null로 바뀜

2) on delete cascade

• 부모 데이터가 삭제되면 해당 데이터를 참조하고 있는 자식 데이터 역시도 함께 삭제 → on delete set null과 다르게 아예 행 자체가 다 삭제 되버림
• 예시) 게시글 하나에 3개의 댓글이 달렸다 → 1번 게시글 삭제 → 3개의 댓글도 모두 삭제

drop table borrow;
create table borrow(
    book_id varchar2(100) primary key
    , std_id varchar2(100) references student(id) on delete cascade
    , rend_date date
);

3) check

• 값 입력시 조건을 준다

drop table user_cons;
create table user_cons(
    no number
    ,id varchar2(100)
    ,pw varchar2(100)
    ,gender varchar2(100) check(gender in ('남','여'))  -- 남 아니면 여만 입력 가능하게 조건
    ,primary key (no,id)
);
insert into user_cons values(1,'ab123','sdasd','망고'); // check constraint 에러
insert into user_cons values(1,'ab123','sdasd','남');
select * from user_cons;

4) default

• 테이블 생성시 값을 입력하지 않았을 때 자동으로 들어갈 값 설정

create table temp(
    date_one date
    ,date_two date default sysdate // 값을 설정 해주지 않으면 default로 sysdate로 준다
);

insert into temp values(sysdate); // 컬럼의 총 개수와 일치하지 않는 데이터를 넣고 있어 에러 발생
insert into temp values(sysdate, default);
select * from temp;

---

alter

• 테이블에 정의된 내용을 수정하고자 할 때 사용하는 데이터 정의어
• 컬럼 추가/삭제, 제약조건 추가/삭제, 컬럼의 자료형 변경, defualt값 변경
• 테이블명/컬럼명/제약조건의 이름 변경

// 테이블 생성
create table member(
    no number primary key
    ,id varchar2(100)
    ,pw varchar2(100)
);

// desc member; -- 테이블의 모든 정보를 보여줌

// 테이블 수정

// 이미 존재하는 member 테이블에 새로운 컬럼 추가(name)
alter table member add (name varchar2(100));

// 새로운 컬럼 추가(age) + default
alter table member add (age number default 0 );

// 제약 조건 추가 -> id 컬럼에 unique 제약조건 추가
alter table member add constraint id_unq unique(id);

// 제약 조건 추가 -> pw컬럼에 not null 제약조건 추가 -> add가 아닌 modify
// alter table member add constraint pw_nn not null(pw); 에러
alter table member modify pw constraint pw_nn not null;

// 컬럼명 수정 -> pw 컬럼을 password 라고 변경
alter table member rename column pw to password;

// 컬럼의 데이터 타입 수정 - name varchar2(100) -> char(100)
alter table member modify name char(100);

// 컬럼 삭제 -age 컬럼 삭제
alter table member drop column age;

// 제약조건 삭제 -- passwrod 컬럼의 제약조건 삭제
// 제약조건의 이름을 먼저 알아야 함
select constraint_name from user_constraints where table_name = 'MEMBER';
alter table member drop constraint pw_nn;

// 제약조건명 수정
alter table member rename constraint SYS_C007105 to no_pk;
select constraint_name from user_constraints where table_name = 'MEMBER';

// 테이블명 변경
alter table member rename to tbl_member;
select * from member; -- 에러 member에서 tbl_member로 변경
select * from tbl_member;