DDL, 테이블 제약조건

create, alter 구분 위주

[ Creat Table ] 테이블(행(레코드)), 열(=필드(컬럼)

1. 테이블 설계 -> 작성(필드 누락, 필드 자료 유형 체크) -> 이 확인 기간이 오래 걸림

   -> 설계 완료 후 데이터 입력해야 편리함

Q10. 데이터를 저장하는 방법 : DDL(create, alter, drop~)

1) 필드를 누락하고 테이블을 만든 경우(alter, 수정, 추가)

형식) alter table 기존에 존재하는 테이블명(수정할 테이블) add (추가할 필드명 자료형 제약조건)

* 제약조건 : 올바른 값만 입력을 맏을 수 있도록 규칙을 정하는 것

EX)

alter table b_prtest add (tel varchar2(15) not null); <- not null을 생략할 경우 무조건 null

* 계산하고 상관 없는 숫자(전화번호, 우편번호 등)는 문자!

SQL> desc b_prtest

이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
TEL NOT NULL VARCHAR2(15)

* 제약 조건을 줄 때 오류가 나는 경우 : 이미 데이터가 들어가 있는 경우

즉, not null을 하기 위해서는 데이터를 입력 전에 해야함..?

* 데이터가 이미 들어가 있는 경우

1) 제약 조건 설정 안됨(not null)

2) 자료형 변경 불가(number -> varchar2 X)

3) varchar(10) -> varchar(20) 가능 : 데이터 입력 공간 확장 가능

4) varchar(10) -> varchar(7) 불가능 : 데이터가 있기에 축소를 불가능

Q11. 변경

형식) alter table 수정할 테이블명 modify (수정할필드명 자료형 제약조건)

1) 자료형 확장

alter table b_prtest modify (name varchar2(29));

이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(29)
TEL NOT NULL VARCHAR2(15)

2) 자로형 유형 변경

alter table b_prtest modify (name number not null);

이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME NOT NULL NUMBER
TEL NOT NULL VARCHAR2(15)

Q12. 불필요한 컬럼의 삭제 : 데이터가 들어있는 경우 삭제가 어려움

형식) alter table 수정할 테이블명 drop column 삭제할 필드명(=컬럼)

EX) alter table b_prtest drop column tel;

이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(30)

Q13. 필드명 변경하기

1) 데이터 없는 테이블의 필드명

형식) alter table 수정할 테이블명 rename column 수정전 필드명 to 수정 후 필드명

EX) alter table b_prtest rename column name to fname;

이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER
FNAME VARCHAR2(30)

2) 데이터가 있는 경우 -> 11g 사용시 변경 가능(버전마다 다를 수 있음)

alter table prtest rename column name to fname;

SQL> alter table b_prtest rename column name to fname;

테이블이 변경되었습니다.

SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER
FNAME VARCHAR2(30)

SQL> alter table prtest rename column name to fname;

테이블이 변경되었습니다.

SQL> desc prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER
FNAME VARCHAR2(10)

SQL> select * from prtest
2 ;

ID FNAME
---------- ----------
1 홍길동
2 갑순이
3 이고운
4 이름
5 홍원표

---

[ 제약조건 활성화(add) / 비활성화(drop) ]

Q14. 제약조건의 변경(10장 PPT 참고, 교재 6p, 9p)

형식)

* DB관리/ 제약 조건이 필요한 이유

1) 어떻게 해야 올바른 값을 저장하는가? -> 설계 목적(ddl)

ex) 이름에 'aaa'를 입력하면 데이터로 가치가 없으므로 제약을 주어야 함

2) 어떻게 해야 원하는 자료를 검색하고 통계처리하여 출력할 수 있는가? (select ~)

3) 자동으로 not null+unique index 키 설정(중복이 되면 안되는 필드에 부여)

    -> 인덱스키 -> 내부에서 먼저 검색 -> 검색 속도가 증가(책갈피)

1. PRIMARY KEY(기본키) : 테이블의 행을 구분하는 키워드로 사용

    -> 중복되면 안되는 필드에 반드시 부여(empno(사번), 계좌번호, 주민등록번호, 아이디 등)

형식) alter table 수정할 테이블명 add constraint 제약조건명 제약조건종류(적용필드명)

-> 데이터가 없는 상태에서 제약조건 추가

-> 제약조건이름 : 규칙을 정해서 생성, 테이블명_제약조건약어_적용필드명

                             ex) b_prtest_pk_id (b_ptest 테이블에 id 값에 primary 제약 조건이 부여됨)

-> 제약조건종류 : primary key~

EX) alter table b_prtest add constraint b_prtest_pk_id primary key(id);

이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
FNAME VARCHAR2(30)

-> 자동으로 not null+unique 키 설정

2. 중복 입력 불가 

SQL> insert into b_prtest values(1,'홍길동');

1 개의 행이 만들어졌습니다.

SQL> insert into b_prtest values(1,'홍길동');
insert into b_prtest values(1,'홍길동')
*
1행에 오류:
ORA-00001: 무결성 제약 조건(TEST01.B_PRTEST_PK_ID)에 위배됩니다

-> 올바른 값만 입력을 허용해줌

Q15. unique 제약조건 : 중복되면 안되는 필드에 부여(=pk와 기능이 동일)

* pk와 unique의 차이점

pk를 설정하면 자동으로 not null+unique key를 함께 부여

unique key를 부여할 경우는 unique key만 부여(not null 부여 안됨)

형식) alter table 수정할테이블명 add constraint 제약조건이름 제약조건종류(필드명)

EX) alter table b_prtest add constraint b_prtest_uk_id unique(id);

alter table b_prtest add constraint b_prtest_uk_id unique(id)
*
1행에 오류:
ORA-02261: 고유 키 또는 기본 키가 이미 존재하고 있습니다

-> PRIMARY KEY를 부여하면 자동으로 unique key가 설정이 되어, 중복으로 설정 할 수 없음

Q16. 제약조건 삭제 : drop constraint

형식) alter table 수정할테이블명 drop constraint 삭제시킬제약조건이름;

EX) alter table b_prtest drop constraint b_prtest_pk_id;

테이블이 변경되었습니다.

SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER
FNAME VARCHAR2(30)

Q17. CHECK 제약 조건 : 데이터를 저장시킬 때, 올바른 값이 입력되었는지 확인

* 올바른 값 지정하는 법

1) 숫자의 경우 : 범위 지정

2) 문자의 경우 : 특정한 값을 등록

형식) alter table 수정할테이블명 add constraint 제약조건이름 check(필드명 제약조건규칙(조건식));

EX) 숫자의 경우

alter table b_prtest add constraint b_prtest_ck_age check(age >=20 and age<=65);

SQL> insert into b_prtest values(4,'김갑순',123,'서울시 강남구');
insert into b_prtest values(4,'김갑순',123,'서울시 강남구')
*
1행에 오류:
ORA-02290: 체크 제약조건(TEST01.B_PRTEST_CK_AGE)이 위배되었습니다

-> age가 조건 범위에 벗어남

EX2) 문자의 경우

~ check in('사원', '대리', '계장', '과장', '부장')

Q18. DEFAULT 제약 조건 : 값을 저장시킬 때, 저장할 값('홍길동', 23)을 언급하지 않아도 자동으로 값이 들어가는 경우

ex) not null -> 지정하지 않으면 기본으로 null값이 저장 됨

형식) ~ (자료형 default 적용시킬 값)

EX) 입사일, 회원등록 날짜

alter table b_prtest add (hiredate date default sysdate);

* 데이터 삽입 시 주의 사항

1) 순서 준수가 필요한 데이터별 입력 : 반드시 필드의 수를 맞춰야 한다.

insert into 테이블명 values(필드명,'저장할값','2017-05-01',,,) 

EX1) insert into b_prtest values(4,'김길수',20,'서울시 도봉구');

*
1행에 오류:
ORA-00947: 값의 수가 충분하지 않습니다 -> 필드의 개수와 입력된 값이 다름

EX2) 직접 입력

insert into b_prtest values(4,'김길수',20,'서울시 도봉구','2017-05-01');

EX3) 키워드 사용

insert into b_prtest values(4,'김길수',20,'서울시 도봉구',sysdate);

EX4) default 사용

insert into b_prtest values(4,'김길수',20,'서울시 도봉구',default);

-> default 조건을 건 sysdate가 적용됨

Ex5) null 사용

insert into b_prtest values(7,'홍갑돌',30,'서울시 도봉구',null);

2) 순서와 무관한 데이터명

형식) insert into 테이블명(필드명1, 필드명2,,,) values(값1, 값2,,,);

-> null을 허용해주는 필드 또는 default가 적용된 필드는 필드명 생략 가능, not null인 필드만 부여

EX) insert into b_prtest(id, fname,addr) values(8,'홍호호','경기 안산시');

* 제약조건 구문

1) primary key, unique, check, not null : 하나의 테이블에 설정이 가능

2) foreign key : 즉 한 개 이상의 테이블에 설정하는 법

3) default(번외)

FOREIGN KEY

- 관계형 데이터베이스의 테이블간 정의를 위해, 즉 한 개 이상의 테이블에 설정하는 법

- 서로 다른 한 개 이상의 테이블 사이의 관계(한개의 테이블을 두개의 테이블로 나누어서 관리)

- 모든 데이터를 한 테이블에 저장이 가능(필드 수가 많으면 저장하기 힘들고 검색 속도도 느리다)

- 때문에 많은 양의 데이터를 두개의 테이블로 나누어서 관리

* 테이블 생성 방법

- 테이블 지정방식으로 생성

- 테이블을 만들고 나서 나중에 제약조건을 따로 생성(alter table~)

1) 테이블 지정방식으로 테이블 만들기(sawon, dept) : 테이블 생성시, 제약조건을 한꺼번에 부여하는 것

- create table dept

   (deptno number primary key,

    dname varchar2(20) not null, 

     lac varchar2(15) not null);

- insert into dept values(10,'영업부','서울');

insert into dept values(20,'개발부','대전');

insert into dept values(30,'총무부','마산');

- create table sawon

(id number primary key,

name varchar2(20) not null,

deptno number);

- insert into sawon values(1,'홍길동',10);

  insert into sawon values(2,'홍길수',40); 

-> 기존의 테이블에 값을 저장하려고 할 때, 특정 필드와 연결이 되어 있는 다른 테이블의 필드값을 검사 후 

    올바른 값을 저장, 존재하지 않는 값일 경우 저장이 안됨. (참조 무결성 강와 규칙)

Q20. 두개의 테이블을 연결하기

형식) alter table 수정할테이블명 add constraint 관계설정할이름 foreign key(자기테이블필드명)

          references 연결시킬 상대방의 테이블명(연결시키고자하는 필드명);

EX) alter table sawon add constraint sawon_fk_deptno  foreign key(deptno)

       references dept(deptno);

SQL> insert into sawon values(3,'김갑수',20);

1 개의 행이 만들어졌습니다.

SQL> select * from sawon;

ID NAME DEPTNO
---------- -------------------- ----------
1 홍길동 10
2 홍길수 30
3 김갑수 20

SQL> insert into sawon values(4,'김ㅋㅋ',50);
insert into sawon values(4,'김ㅋㅋ',50)
*
1행에 오류:
ORA-02291: 무결성 제약조건(TEST01.SAWON_FK_DEPTNO)이 위배되었습니다- 부모 키가
없습니다