[ORACLE] DDL / 데이터타입 / 무결성 제약조건 설정, 조회, 변경

 

※ DDL

      - 데이터베이스 오브젝트 ( = 테이블 등 ) 들을 생성, 변경, 삭제, 관리하는 명령어

         : create, drop, truncate, delete

         

 

▶ create

  - 오브젝트의 생성명령어

create 오브젝트타입  오브젝트명...

  ㄴ  ex ) create table 테이블명...
            create sequence 시퀀스명..

 

▶ drop

  - 오브젝트 제거(영구 소멸)

drop 오브젝트타입 오브젝트명;

  ㄴ ex ) drop table 테이블명;
            drop sequence 시퀀스명;

 

▶ alter

  - 오브젝트 구조 변경

alter 오브젝트타입 오브젝트명..

  ㄴ ex ) alter table dept5 add loc varchar2(20);

 

▶ truncate 

  - truncate table : 테이블의 데이터 삭제 (구조는 유지됨)

truncate table 테이블명;

 

 

▷ drop, truncate, delete 비교
 

    1) delete - 메모리상의 데이터를 삭제, rollback으로 되돌릴 수 있다
    2) truncate - 메모리상의 데이터와 데이터 파일까지 삭제, 자동커밋됨
                      delete보다 수행속도가 빠름
       => delete, truncate는 데이터만 삭제, 테이블 구조는 살아있다          
    3) drop - 테이블의 구조까지 영구히 소멸시킴

 

---

 

※ 테이블 생성 create

문자는 char 2000 varchar2 4000 CLOB 4GB

숫자는 number

날짜는 date

 

create table 테이블명
    (
        컬럼명1    데이터타입,
        컬럼명2    데이터타입,
        컬럼명3    데이터타입,
        ....
    )

create table tbl_test1
(
    name    varchar2(30),   --이름 (최대 4000byte)     
    ssn     char(13),       --주민번호 (최대 2000byte)
    gender  char,           --성별 (M/F)
    age     number(3),      --나이
    intro   clob,           --자기소개 (최대 4GB)
    regdate date            --등록일
);

 

▶ 데이터 타입 - 문자형, 숫자형, 날짜형

 

[1] 문자형

char - 고정길이 문자형, 최대 2000byte 까지 저장

varchar2 - 가변길이 문자형, 최대 4000byte 까지저장

CLOB - (Character Large Object)

        - 크기가 큰 문자열이나 문서의 저장이 가능

        - 4GB까지 저장

 

create table char_exam1
(
    name1 char(6 byte)     // 한글 2글자 입력가능
    name2 char(3)             // 생략하면 자동 byte
    name3 char(6 char)    // 한글 6글자 입력가능
);

- 인코딩에 따라 한글 1 글자는 2byte or 3byte 이다

- char, varchar2 에서 생략하면 byte

 

 

[2] 숫자형

 

▷ number

  - 자릿수 지정 X

 

▷ number(전체 자리수)

  - number(9) : 전체 9자리의 수 표현 가능, 소수이하 자리수는 표현하지 않음

     입력 1234567.89 => 출력 1234568

 

▷ number(전체 자리수, 소수이하 자리수)

  - number(9,1) : 전체 9자리의 수 중 소수이하 1자리까지 표현 // 입력1234567.89 => 출력1234567.9

  - number(9,2) : 전체 9자리의 수 중 소수이하 2자리까지 표현

  - number(7,-2) : 전체 7자리의 표현, 소수이상 둘째자리(십의 자리)에서 반올림

     => 입력 1234567.89 => 출력 1234600

 

 ▷ number(작은숫자, 큰숫자)

   - 전체 자리수가 소수이하 자리수보다 적은 경우

   - 1보다 작은 실수 표현

 

     ex ) number(3,5)

       - 전체 자리수 3개, 소수이하 자리수 5개

       - 5-3 => 소수이하 자리수에 2개의 0이 붙게 됨

 

    =>      입력        출력

               0.00123    0.00123

               0.01234    0.0012

               0.000123   0.00012

 

 

 

 

[3] 날짜형

date - 년월일 시분초 까지 표현
timestamp - 밀리초까지도 표현

create table date_exam1
(
    d1  date,
    d2  timestamp
);

 

d1 에 sysdate 넣었을 때 : 2022-03-26 19:43:19

d2 에 sysdate 넣었을 때 : 22/03/26 19:43:20.284000000

 

---

※ 무결성 제약조건 (Integrity Constraint)

 - 제대로된 데이터들이 올바르게 저장될 수 있도록 하기 위해 데이터베이스 측에서 제공하는 기능들

 

▷ 데이터 무결성

  - 오라클 서버에서 데이터를 흠 없이 안정되게 지켜주는 것

  - 무결성을 지키기위해 제약조건들을 제공함

  - 제약조건들은 테이블의 컬럼에 적용됨

 

 

자 들어간 다 !!!!!!!! 정신 똑바로 차려라 !!!!!  존나헷갈린다 !!!!!!!!!!

 

 

 

▶ NULL/NOT NULL

 - NOT NULL => C

 

 - NULL : 데이터가 없음을 의미

 - 컬럼의 속성 중 하나로 해당 컬럼이 NULL 값을 허용하는지, 허용하지 않는지 지정

 - 데이터타입 다음에 명시함 (= name1 char(3) not null  // 컬럼명 데이터타입 제약조건 )

 - null 을 허용하면 null, 허용하지 않으면 not null 명시 ( = 반드시 값을 입력해야 함 )

 - 따라서 필수입력항목에서는 not null 을 명시해야한다

 - 명시하지 않으면 디폴트값 : null  // null 을 허용함

 - null 을 직접 입력할 수 있다 values(null) <= 이런식으로 

 

 

▶ unique (U)

 

 - 각 레코드를 유일하게 식별할 수 있는 속성

 - 복합키를 unique 제약조건으로 사용할 수 있다

 - 한 테이블에 여러개의 unique 제약조건이 올 수 있다.

 - 중복된 값 허용 X

 - null을 허용하나, unique 제약조건에는 not null을 지정하는 것이 일반적임

 

 

▶ primary key (P)

 - 각 레코드를 유일하게 식별할 수 있는 속성

 - 테이블당 하나만 올 수 있다

 - not null + unique

 - 복합키도 가능하다 : inline 으로는 불가하고, outline 으로만 가능

create table pk_exam2
(
    col1    char(3),
    col2    varchar2(4),
    col3    number,
    constraint pk1_col1 primary key(col1, col2)
);

 

 

▶ foreign key(R) 

 - 외래키 제약조건

 - 다른 테이블을 참조하기 위해 사용되는 속성들

 - 테이블간의 관계를 설정할 때 사용되는 키

 - 부모테이블의 primary key 나 unique 는 자식테이블의 foreign key 로 전이된다

     (= 부모테이블의 기본키는 자식테이블의 외래키로 전이됨 )

 - 부모테이블에 있는 참조컬럼의 값만 자식테이블에서 사용 가능

 

    ▷ 입력 : 부모테이블을 먼저 insert 하고 그 후에 자식테이블 insert

           - 따라서 부모테이블에 없는 값을 자식테이블에서 사용하는 것은 불가능

    ▷ 삭제 : 자식테이블을 먼저 delete 하고 그 후에 부모테이블 delete

           - 부모테이블을 먼저 삭제하고 싶으면

             foreign key 지정 시 자식에 on delete cascade 옵션주기

 

★ on delete cascade ★

- 자식에 on delete cascade 를 주면 부모를 지우면 자식도 지워진다

 ex ) 게시글을 지우면 댓글이 지워짐

      직원 데이터를 지우면 직원가족 데이터도 지워짐

CONSTRAINT 제약조건명 PRIMARY KEY (PK컬럼명);

CONSTRAINT 제약조건명
FOREIGN KEY (CHILD_TABLE의 FK로 사용할 컬럼명)
REFERENCES 연결시킬_테이블(연결시킬_테이블의_PK_컬럼명)
ON DELETE CASCADE

▽

create table mother(
	col1 varchar2(20)
    constraint mother_pk primary key (col1)
); -- 부모테이블 생성, col1 을 기본기로 설정 후 mother_pk 로 명명

create table child(
	col2 varchar2(20),
    
    constraint child_pk  
    foreign key (col2)
    references mother(col1)
    on delete cascade
); -- 자식테이블 생성 col2 를 mother 테이블의 col1 컬럼을 참조한 외래키로 설정 후 child_pk 로 명명
   -- on delete cascade : mother 테이블의 값 삭제 시 연결된 값 삭제됨

 

▷ alter로 테이블에 cascade 설정

  = outline 에서 cascade 설정

ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명
  FOREIGN KEY (CHILD_테이블의_FK값)
  REFERENCES MOTHER_테이블명(MOTHER_테이블의_PK)
  ON DELETE CASCADE;

 

▷ 부모테이블 삭제 cascade constraint

 - 자식 테이블이 참조하고 있는 부모테이블은 drop 할 수 없지만,

   참조제약조건까지 같이 삭제하고 싶으면 drop시 cascade constraint 옵션을 준다

drop table 부모테이블명 cascade constraint;

 - 이렇게 하면 자식의 foreign key를 삭제하고 부모 테이블도 삭제됨

 

 

 

 

▶ check (C)

 - 입력되는 값을 체크하여 일정한 조건에 해당되는 값만 입력될 수 있게 하는 제약조건

 - ex ) 성별컬럼 => 남자, 여자만 입력되고 다른 값은 입력될 수 없도록

 

▷ inline

create table check_exam1(
	gender  char(6) check(gender in('남자','여자'))
    );

▷ outline

create table check_exam1 (
	pay     number(10),
   	 age     number(3),
    constraint  ck_check_exam1_pay check(pay>=0),
    constraint  ck_check_exam1_age check(age>=0 and age<=120)
);

 

 

 

▶ default (C)

 - 기본값

 - 컬럼에 특정값을 디폴트값으로 설정하면 테이블에 데이터를 입력할 때 해당 컬럼에 값을 입력하지 않을 경우

   디폴트로 설정한 값이 자동으로 입력됨

 - 컬럼타입 다음에 'default' 을 명시  ex ) score  number(3)  default 0

  default 위치 : 데이터타입 + default

                    default + not null , null

 

 

---

 

▶ outline 제약조건설정 constraint 

 - 열을 먼저 정의한 후에 제약조건을 지정할때 사용

 - 제약조건에 이름을 지정하는 경우사용

 - 보통 테이블_컬럼_제약조건이니셜 로 이름을 정한다 

 - not null 을 제외한 제약조건 지정이 가능하다

 

 

▷ 제약조건에 이름붙이기 constraint

create table test (
		AA number(5) constraint test_AA_pk primary key,
   		 BB varchar(10) constraint test_BB_nk unique,
);

 

▷ 열을 먼저 정의한 후 제약조건지정 constraint

create table test (
		AA number(5),
   		 BB varchar(10)
         
         constraint test_AA_pk primary key (AA)
         constraint test_BB_uk unique (BB)
);

 

▷ 두개컬럼 동시에 제약조건주고 제약조건 이름부여

create table test (
		AA number(5),
   		 BB varchar(10)
         
         constraint test_nuique unique(AA,BB)
);

 

▶ alter ★

▷ 테이블 생성 이후에 제약조건 설정

alter table 테이블명
add constraint 제약조건이름  제약조건종류(컬럼);

1) 기본키 primary key 설정

create table test (
		AA number(5),
   		 BB varchar(10)
);
alter table test
add constraints "number_pk" primary key(AA); // "" : 기본키이름

2) 외래키 foreign key 추가

alter table employee2
add constraint fk_employee2_empno foreign key(dcode)
    references depart(dept_cd);

 

 

▷ 테이블 변경하기

 

1) 새로운 컬럼 추가하기

alter table depart
add pdept char(3);

 - 추가될 때 값은 null or default 값이 들어감

 

 

▷ 컬럼의 데이터크기 변경하기

alter table depart
modify country varchar2(70);

 

▷ 컬럼 이름 변경하기

alter table depart
rename column loc to area;

 - loc => area로 변경

 

 

▷ 테이블 이름 변경하기

rename depart_temp1 to depart_temp10;

 

▷ 컬럼 삭제하기

alter table depart
drop column pdept;

 

 

 

 

▶ 제약조건 조회하기

 

user_constraints

user_cons_columns

 

select a.TABLE_NAME, a.CONSTRAINT_NAME, a.CONSTRAINT_TYPE, a.INDEX_NAME,
    b.COLUMN_NAME, b.POSITION
from user_constraints a join user_cons_columns b
on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
and  a.table_name = 'CHECK_EXAM1';

select * from user_constraints
where table_name='PD2';

 

 

▶ 제약조건 변경하기

1. 제약조건 이름변경

alter table employee2
rename constraint fk_employee2_empno to fk_employee2_dcode;

2.  not null , null 제약조건 변경

alter table employee2
modify name null; // -name컬럼이 not null이었는데 null로 변경

3. defaoult 제약조건 변경

alter table employee2
modify sal default 1000;  //  sal 컬럼의 default값이 0 이었는데 1000으로 변경

 

 

 

---

※ 각종 참조 시스템 뷰

 

USER_TABLES : 해당 사용자가 생성한 테이블 내역

USER_CONSTRAINTS : `` 제약조건 내역

USER_INDEXES : `` 인덱스 내역

select * from user_tables;
select * from user_indexes;
select * from user_objects;
select * from user_constraints;

 

▶ 테이블 A 복사해서 테이블 B 만들기

create table 테이블명
as
select문

 

을 이용해서 테이블을 만들면 null, not null을 제외한 제약조건은 복사되지 않음

 

 

▶ pd 테이블에서 컬럼 틀만 가져와서 pd_temp1 테이블 만들기

create table pd_temp1
as
select * from pd
where 0=1;

▷desc depart; : 틀보기