[kh정보교육원 42일차] DDL - Create 제약조건 /ALTER(add,modify,rename,drop)/TABLE RENAME

 

 

 

DDL

Data Definition Language 데이터 정의어

DDL은 Database 객체를 생성하는 기능, Alter 수정, Drop 삭제 하는 명령어이다.

실행 즉시 db에 반영되므로 TCL처리가 필요없다.

 

select object_type from all_objects;

 

위와 같은 코드를 실행하면 사용자가 소유한 객체, 사용권한을 위임받은 객체 모두 조회 가능한 Data Dictionary를 볼 수 있다.

 

수십개의 행이 계속해서 인출된다.

 

Create

객체를 생성하는 명령어

컬럼 작성시 컬럼명 자료형 [기본값] [제약조건] 순으로 작성한다.

create table member(
        id varchar2(20) primary key,
        password varchar2(20) not null,
        name varchar2(50) not null,
        reg_date date default sysdate
);

desc member;

 

맨마지막줄을 실행하면 다음과 같이 나온다.

 

+ 테이블, 컬럼에 대한 부가적인 설명 주석으로 추가하기

comment on table member is '회원 관리 테이블'; --테이블
comment on column member.id is '회원 아이디';--컬럼
comment on column member.password is '비밀번호';--컬럼
comment on column member.name is '회원 이름';--컬럼
comment on column member.reg_date is '회원 가입일';--컬럼

 

다음과 같은 방법으로 추가하면 된다.

select*from user_tab_comments where table_name = 'MEMBER'; --테이블명은 저장된 값이므로 대소문자를 구분
select*from user_col_comments where table_name='MEMBER';

 

select*from user_tab_comments where table_name = 'MEMBER'; --테이블명은 저장된 값이므로 대소문자를 구분
select*from user_col_comments where table_name='MEMBER';

 

테이블명과 컬럼명을 확인해보자.

 

 

 

CONSTRAINT

제약조건, 테이블의 컬럼에 대해서 데이터 무결성을 지키기 위해서 설정하는 조건

데이터 무결성 : 데이터가 일관되고, 정확하게 유지되는 것을 의미

 

1. not null (C) : 해당 컬럼에 null값을 허용하지 않는다.

2. unique (U) : 레코드별로 해당 컬럼에 중복을 허용하지 않는다.

3. primary key (P) : 식별자 컬럼을 지정. 레코드를 구별하기 위한 고유값을 가진 컬럼. 테이블당 하나만 허용.

4. foreign key (R) : 외래키. 부모테이블의 특정컬럼값만 사용가능하도록 제약.

5. check (C) : 해당 컬럼의 값의 범위/목록을 제한(도메인을 설정하는 제약조건)

6. default : 열이 생성될 때 값의 입력이 없으면 자동으로 입력되는 초기의 값을 지정하는 조건이다.

 

ex ) employee에 설정된 제약조건. user_constraints 데이터 딕셔너리에서 조회

 

 

제약조건 작성법 2가지

1. 테이블 레벨 - 테이블로 작성하는 것 처럼 아래에 똑같이 작성.

2. 컬럼 레벨 - 컬럼 설정시 옆에 작성

 

NOT NULL

제약조건을 컬럼레벨에만 작성할 수 있다.

insert into
        member
values('honggd','1234','홍길동',default);

insert into
        member
values('honggd',null,'홍길동',default);--ORA-01400: cannot insert NULL into ("KH"."MEMBER"."PASSWORD")

 

두번째는 --ORA-01400: cannot insert NULL into ("KH"."MEMBER"."PASSWORD") 와 같은 오류가 발생한다.

 

 

UNIQUE

create table member(
        id varchar2(20) primary key,--컬럼 레벨로 작성
        password varchar2(20) not null,
        name varchar2(50) not null,
--        email varchar2(150) constraint uq_member_email unique,-- 컬럼 레벨
        email varchar2(150) unique,
        reg_date date default sysdate
--        constraint uq_member_email unique(email) -- 테이블레벨 
);

 

insert into
        member(id,password,name,email)
values('honggd','1234','홍길동','honggd@gmail.com');

insert into
        member(id,password,name,email)
values('hgd','1234','홍가당','honggd@gmail.com');--ORA-00001: unique constraint (KH.SYS_C008473) violated

insert into
        member(id,password,name,email)
values('hgd','1234','홍가당',null); --null값을 허용한다. not null붙으면 허용안함

insert into
        member(id,password,name,email)
values('sinsa','1234','신사임당',null); --오라클은 복수의 null값을 허용. Mssql은 null값을 하나만 허용.

 

PRIMARY KEY

기본키. 레코드를 식별할 수 있는 식별자컬럼에 지정하는 제약조건이다.

테이블당 하나의 PK만 지정할 수 있다.

해당컬럼은 null을 허용하지 않고 레코드별로 고유해야 한다.

단일컬럼PK, 복합컬럼PK가 있다.

 

단일컬럼PK

create table member(
    id varchar2(20),
    password varchar2(20) not null,
    name varchar2(50) not null,
    email varchar2(150) not null,
    reg_date date default sysdate,
    constraint pk_member_id primary key(id),
    constraint uq_member_email unique(email)
);

 

insert into
        member(id,password,name,email)
values('honggd','1234','홍길동','honggd@gmail.com');

insert into
    member (id, password, name, email)
values ('honggd', '1234', '홍길동', 'honggd@gmail.com'); -- ORA-00001: unique constraint (KH.PK_MEMBER_ID) violated

insert into
    member (id, password, name, email)
values (null, '1234', '홍길동', 'honggd@gmail.com'); -- ORA-01400: cannot insert NULL into ("KH"."MEMBER"."ID")

 

둘다 오류가 발생하면서 되지 않는다. honggd는 primary key인 id로 중복이 불가능하기 때문에 00001 오류가 발생했고,

primary key를 null값으로 주어지면 01400 오류가 발생한다.

 

그렇다면 primary 값을 넣은 컬럼은 바꾸는 방법은?

update member set name = '홍길똥' where id = 'honggd';

 

홍길동 이었던 것이 홍길똥으로 바뀌었다!

 

 

복합컬럼PK(Composite PK)

create table tb_order (
    product_no number, -- 상품 식별자
    member_id varchar2(20), -- 회원 식별자
    order_date date default sysdate, -- 주문일자
    cnt number default 1,
    constraint pk_tb_order primary key(product_no, member_id, order_date)
);

 

insert into 
    tb_order
values (1000, 'honggd', default, 5);

insert into 
    tb_order
values (1000, null, default, 5);  -- ORA-01400: cannot insert NULL into ("KH"."TB_ORDER"."MEMBER_ID") 

 

처음에는 값이 입력되었으나, product_no, member_id, order_date이기 때문에 한개라도 빠지면 오류가 발생한다.

select 
    product_no,
    member_id,
    to_char(order_date, 'yyyy/mm/dd hh24:mi:ss') order_date,
    cnt
from 
    tb_order;

 

맨처음에 입력한 값만 들어간 것을 확인할 수 있다.

 

 

 

FORIEGN KEY

외래키. 참조무결성을 위한 제약조건

부모테이블(참조되는 테이블)의 존재하는 값만 자식테이블(참조하는 테이블. 외래키)에서 사용이 가능하도록 제한한다.

부모테이블의 참조컬럼은 pk/uq(primary,unique) 제약조건이 걸려있어야 한다.

* 중복값이 있는 컬럼은 참고할 수 없다

자식테이블에 null 값 허용

 

부모테이블

create table shop_member(
    id varchar2(20),
    name varchar2(50) not null,
    constraint pk_shop_member_id primary key(id)
);

insert into shop_member values('honggd', '홍길동');
insert into shop_member values('sinsa', '신사임당');
insert into shop_member values('sejong', '세종대왕');

 

 

자식테이블

create table shop_buy (
    buy_no number,
    member_id varchar2(20),
    product_id varchar2(20),
    cnt number default 1,
    buy_date date default sysdate,
    constraint pk_shop_buy_no primary key (buy_no),
    constraint fk_shop_buy_member_id foreign key(member_id) references shop_member(id) on delete cascade
);

insert into
    shop_buy(buy_no, member_id, product_id)
values (1, 'honggd', '축구화001');
insert into
    shop_buy(buy_no, member_id, product_id)
values (2, 'sinsa', '볼링화123');
insert into
    shop_buy(buy_no, member_id, product_id)
values (3, 'sejo', '등산화123'); -- ORA-02291: integrity constraint (KH.FK_SHOP_BUY_MEMBER_ID) violated - parent key not found
insert into
    shop_buy(buy_no, member_id, product_id)
values (3, null, '등산화123'); -- fk컬럼에 null값은 허용한다. 

 

3번째에 pk아이디가 없는 value를 추가했더니 -- ORA-02291: integrity constraint (KH.FK_SHOP_BUY_MEMBER_ID) violated - parent key not found 와 같은 오류가 발생했다.

 

부모테이블의 레코드 삭제 (honggd 삭제해보기)

delete from
    shop_buy
where
    member_id = 'honggd'; 

delete from
    shop_member
where
    id = 'honggd'; -- ORA-02292: integrity constraint (KH.FK_SHOP_BUY_MEMBER_ID) violated - child record found
-- honggd행을 참조하는 자식레코드가 발견되어서 처리불가

 

-- 자식레코드 삭제 -> 부모레코드 삭제 순으로 삭제해야 한다.
select * from shop_buy;
select * from shop_member;
-- drop table shop_member; -- ORA-02449: unique/primary keys in table referenced by foreign keys

 

 

 

fk 제약조건 삭제 옵션

1. on delete restricted (기본값) 부모레코드 먼저 삭제 불가

2. on delete set null 부모레코드 삭제시 자식 레코드의 fk컬럼을 null로 변환

3. on delete cascade 부모레코드 삭제시 자식 레코드 삭제

 

delete from
    shop_member
where
    id = 'honggd'; 

 

 

식별관계, 비식별관계

1. 식별관계 : fk컬럼을 다시 pk컬럼으로 지정. 부모/자식 테이블간의 1:1관계

2. 비식별관계 : fk컬럼을 pk컬럼으로 사용하지 않는것. 부모/자식 테이블간의 1:N 관계

 

1. 식별관계

create table person(
    id varchar2(20),
    name varchar2(50),
    constraint pk_person_id primary key(id)
);

insert into 
    person
values ('yoogs', '유관순');

 

2. 비식별관계

create table person_address(
    person_id varchar2(20),
    addr varchar2(200) not null,
    constraint fk_person_address_id foreign key(person_id) references person(id),
    constraint pk_person_address_id primary key(person_id, addr)
);

insert into 
    person_address
values('yoogs', '서울시 강남구 역삼동');
insert into 
    person_address
values('yoogs', '서울시 강동구 천호동'); -- ORA-00001: unique constraint (KH.PK_PERSON_ADDRESS_ID) violated

 

아이디가 같은 yoogs를 쓰면 -- ORA-00001: unique constraint (KH.PK_PERSON_ADDRESS_ID) violated 와 같은 오류가 발생한다.

 

 

CHECK

해당컬럼의 값의 범위, 목록을 제한하는 제약조건

create table member(
    id varchar2(20),
    password varchar2(20) not null,
    name varchar2(50) not null,
    email varchar2(150) not null,
    gender char(1), 
    point number default 0,
    reg_date date default sysdate,
    constraint pk_member_id primary key(id),
    constraint uq_member_email unique(email),
    constraint ck_member_gender check(gender in ('M', 'F')), 
    constraint ck_member_point check(point >= 0)
);

 

insert into 
    member
values ('honggd', '1234', '홍길동', 'honggd@naver.com', 'M', 1000, default);
insert into 
    member
values ('sinsa', '1234', '신사임당', 'sinsa@naver.com', 'f', 1000, default); -- ORA-02290: check constraint (KH.CK_MEMBER_GENDER) violated
insert into 
    member
values ('sinsa', '1234', '신사임당', 'sinsa@naver.com', 'F', -1000, default); -- ORA-02290: check constraint (KH.CK_MEMBER_POINT) violated
insert into 
    member
values ('sinsa', '1234', '신사임당', 'sinsa@naver.com', null, null, default);

 

check ) M,F 가 아닌경우 / point >=0 가 아닌 경우 -- ORA-02290: 오류가 발생한다.

 

 

 

 

ALTER

table객체에 대한 alter구문은 컬럼, 제약조건에 대한 수정시 사용한다.

 

sub명령어

1. add 컬럼 / 제약조건 추가

2. modify 컬럼 (자료형, default값, not null) 변경

3. rename 컬럼 / 제약조건 이름 변경

4. drop 컬럼 / 제약조건 삭제

 

제약조건은 이름변경 외에는 변경이 불가능하므로 삭제후 다시 생성

 

 

ADD

컬럼추가하는 기능. 테이블의 마지막 컬럼순서로 추가 가능하다.

 

alter table 
    tb_user
add 
    name varchar2(50) default '홍길동' not null;

desc tb_user;

 

제약조건 추가

alter table 
    tb_user
add constraint pk_tb_user_no primary key(no);

select * from user_constraints where table_name = 'TB_USER';

 

 

MODIFY

컬럼에 대해서만 수정한다. (자료형, default, not null)

not null : 기본적으로 column에 대해 null

default null, default '' : 기본값 제거

not null -> null

alter table
    tb_user
modify
    name varchar2(150) default '' null; 

desc tb_user;

 

name에 notnull이 사라졌다.

 

insert into
    tb_user(no, id, password)
values (1, 'honggd', '1234');

insert into
    tb_user(no, id, password)
values (2, 'sinsa', '1234');

select * from tb_user;

 

이름 없이 입력된 것을 확인할 수 있다.

 

 

RENAME

컬럼명변경 , 제약조건명 변경

alter table
    tb_user
rename column password to pw;

desc tb_user;

select * from user_constraints where table_name = 'TB_USER';

alter table
    tb_user
rename constraint PK_TB_USER_NO to pk_tb_user;

 

 

DROP

컬럼 삭제, 제약조건 삭제 : 대상컬럼변경, 제약조건타입 변경 등은 불가능하다. 삭제 후 다시 생성해야 한다.

 

alter table
    tb_user
drop constraint CK_TB_USER_GENDER;

alter table
    tb_user
drop column gender;

desc tb_user;

 

 

TABLE RENAME

테이블명 변경 가능

alter table
    tb_user
rename to tb_userrr;    
    
select * from tb_userrr;

 

 

테이블이름이 변경된 상태로 출력되었다.

 

컬럼값이 varchar2(10)이고, 10byte데이터가 있는 상황에서 varchar2(5)로 변경이 가능? X

컬럼값이 varchar2(10)이고, 10byte데이터가 있는 상황에서 varchar2(15)로 변경이 가능? O

 

 

DROP

참조되고 있는 부모테이블을 drop할 수 있을까?

select * from shop_member; -- 부모
select * from shop_buy; -- 자식
select * from user_constraints where table_name = 'SHOP_BUY';

 

부모 테이블

 

자식 테이블

 

대표사진 삭제

select * from user_constraints where table_name = 'SHOP_BUY';

drop table shop_member; 실행시,

 

다음과 같은 오류가 발생한다.

drop table shop_member cascade constraint;
drop table shop_member;

 

자식테이블의 fk제약조건 제거 후에 부모테이블을 삭제해야 삭제된다.