일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
- 책 원씽
- ajax 비동기식
- TCP 채팅
- 자바스크립트 undefined
- xml 파싱 방법
- 빅데이터분석기사
- 노트패드++ 줄바꿈
- 빅데이터분석기사 필기
- 빅데이터분석기사 독학
- 원씽 책
- 원씽 내용
- 빅분기 독학
- 동기식 비동기식
- Notepad 줄바꿈
- dom sax 장단점
- 자바 채팅
- dom sax 차이점
- Notepad++ 줄바꿈
- 빅분기
- The OneThing
- 빅분기 필기
- TCP Socket
- 간단한 채팅 프로그램
- 원씽 독후감
- 원씽 후기
- async
- ajax 동기식
- 자바 채팅 프로그램
- 노트패드 줄바꿈
- async false
- Today
- Total
SooBlending
[Oracle] 제약조건 (Constraint) 본문
<무결성 제약조건> Data integrity constraint rule
- 테이블에 부적절한 자료가 입력(insert,update)되는 것을 방지하기 위해서
테이블을 생성할 때 각 컬럼에 대해서 정의 하는 여러가지 규칙.
1. NOT NULL
해당 컬럼값으로 NULL을 허용하지 않음
- 입력시 데이터를 무조건 받겠다!!
2. UNIQUE
테이블내에서 해당 컬럼값은 항상 유일무이한 값을 가질 것.
- 중복허용하지 않겠다!!
3. PRIMARY KEY(기본키)- (not null+unique 동시에 만족)
해당컬럼값은 반드시 존재해야 하고 유일해야 한다는 조건.
- 테이블내에서 서로 다른 행을 구분하기 위해서 사용!!
4. FOREIGN KEY (외래키,참조키)
해당컬럼의 값이 타컬럼의 값을 참조해야만 함
즉, 참조되는 컬럼에 없는 값은 입력 불가.
- 참조하고자 하는 테이블에서 설정(예: 사원테이블의 deptno에 참조키 설정)
- 참조되는 쪽(부서테이블)의 컬럼은 반드시 unique하거나 또는 primary키 여야함!!
5. CHECK
해당 컬럼에 저장 가능한 데이터 값의 범위나 사용자 조건을 지정.
age컬럼: (1~100)
gender컬럼: (남성,여성)
<컬럼레벨 정의 방법으로 제약조건 지정하기>
---> 컬럼레벨: 제약조건의 명시를 테이블 생성시 컬럼정의와 함께!!
형식)
create table 테이블명(
컬럼명 자료형 CONSTRAINT_TYPE
);
drop table dept2;
create table dept2(
deptno number(2),
dname varchar2(15),
loc varchar2(15)
);
insert into dept2 values (10, '영업부','서울');
insert into dept2 values (10, '자재부','서울');
insert into dept2 values (null,'총무부','서울');
insert into dept2 values (10, '영업부','서울');
SQL> select * from dept2;
DEPTNO DNAME LOC
---------- ------------------------------
10 영업부 서울
10 자재부 서울
20 총무부 서울
10 영업부 서울
---> 첫행의 부서명을 '영업부' ---> '개발부'로 변경 ???
drop table dept2;
create table dept2(
deptno number(2) primary key,
dname varchar2(15),
loc varchar2(15)
);
insert into dept2 values (10, '영업부','서울');
insert into dept2 values (10, '자재부','서울');
===> ORA-00001: unique constraint (SCOTT.SYS_C007002) violated
insert into dept2 values (20, '자재부','서울');
insert into dept2 values (null,'총무부','서울');
===> ORA-01400: cannot insert NULL into ("SCOTT"."DEPT2"."DEPTNO")
insert into dept2 values (30,'총무부','서울');
insert into dept2 values (40, '영업부','서울');
SQL> select * from dept2;
DEPTNO DNAME LOC
---------- ------------------------------
10 영업부 서울
20 자재부 서울
30 총무부 서울
40 영업부 서울
---> 첫행의 부서명을 '영업부' ---> '개발부'로 변경 ???
update dept2 set dname='개발부'
where deptno=10;
SQL> select * from dept2;
DEPTNO DNAME LOC
---------- ------------------------------
10 개발부 서울
20 자재부 서울
30 총무부 서울
40 영업부 서울
<컬럼레벨(컬럼선언시) 제약설정과 함께 이름지정!!>
형식)
create table 테이블명(
컬럼명 자료형 [CONSTRAINT 제약명] 제약타입
);
※참고: 제약명 ---> 테이블명_컬럼명_제약
drop table dept2;
create table dept2(
deptno number(2) CONSTRAINT dept2_deptno_pk primary key,
dname varchar2(15),
loc varchar2(15)
);
---> ORA-00001: unique constraint (SCOTT.DEPT2_DEPTNO_PK) violated
==============================================================
<제약종류(constraint_type)>
1. not null
- 데이터를 반드시 입력!!
- 수정시 null로 변환 불가
drop table emp2;
create table emp2
(
empno number(4),
ename varchar2(15),
sal number(7,2),
job varchar2(15)
);
insert into emp2 (empno, ename, sal, job)
values (7000, '홍길동', 2000, '사원');
insert into emp2 (ename, sal, job)
values ('길라임', 2000, '사원');
insert into emp2 (ename, sal, job)
values ('길라임', 2000, '사원');
SQL> select * from emp2;
EMPNO ENAME SAL JOB
---------- ------------------------------ ----------
7000 홍길동 2000 사원
7002 길라임 2000 사원
7004 길라임 2000 사원
drop table emp2;
create table emp2
(
empno number(4) constraint emp2_empno_nn not null,
ename varchar2(15),
sal number(7,2),
job varchar2(15)
);
insert into emp2 (empno, ename, sal, job)
values (7000, '홍길동', 2000, '사원');
insert into emp2 (ename, sal, job)
values ('길라임', 2000, '사원');
===> ORA-01400: cannot insert NULL into ("SCOTT"."EMP2"."EMPNO")
insert into emp2 (empno, ename, sal, job)
values (7002,'길라임', 2000, '사원');
insert into emp2 (empno, ename, sal, job)
values (7004,'길라임', 2000, '사원');
SQL> select * from emp2;
EMPNO ENAME SAL JOB
---------- ------------------------------ ----------
7000 홍길동 2000 사원
7002 길라임 2000 사원
7004 길라임 2000 사원
insert into emp2 (empno, ename, sal, job)
values (7006,'김주원', 2000, '사원');
insert into emp2 (empno, ename, sal, job)
values (7006,'김주원', 2000, '사원');
EMPNO ENAME SAL JOB
---------- ------------------------------ ----------
7000 홍길동 2000 사원
7002 길라임 2000 사원
7004 길라임 2000 사원
7006 김주원 2000 사원
7006 김주원 2000 사원
2. unique
- 중복된 데이터를 방지
drop table emp2;
create table emp2
(
empno number(4), -- CONSTRAINT emp2_empno_uk unique, --컬럼레벨
ename varchar2(15),
sal number(7,2),
job varchar2(15),
-- 테이블 레벨(컬럼선언이 끝나고 난 후)에 제약을 정의!!
CONSTRAINT emp2_empno_uk unique (empno)
);
EMPNO
insert into emp2 values (7000,'홍길동', 2000, '사원');
insert into emp2 values (7002,'길라임', 2000, '사원');
insert into emp2 values (7004,'길라임', 2000, '사원');
insert into emp2 values (7006,'김주원', 2000, '사원');
insert into emp2 values (7006,'김주원', 2000, '사원');
==> ORA-00001: unique constraint (SCOTT.EMP2_EMPNO_UK) violated
insert into emp2 values (NULL,'김유신', 2000, '사원');
==> 1 row created.
insert into emp2 values (NULL,'김유신', 2000, '사원');
==> 1 row created.
==> UNIQUE : 중복된 데이터를 방지하지만 NULL중복 체크 못함.
3. primary key (기본키, 주키)
- not null AND unique를 동시에 만족하는 데이터 입력
- 테이블내의 서로 다른 행을 구분하는 목적으로 사용.
- 테이블내에 단 한 개의 primary key만 존재!!
drop table emp2;
create table emp2
(
empno number(4) primary key,
ename varchar2(15) primary key,
sal number(7,2),
job varchar2(15)
);
==> ORA-02260: table can have only one primary key
drop table emp2;
create table emp2
(
empno number(4), --constraint emp2_empno_pk primary key,
ename varchar2(15),
sal number(7,2),
job varchar2(15) --,
-- constraint emp2_empno_pk primary key (empno)
);
<제약추가> : 이미 테이블 객체가 만들어진 상태에서 제약추가!!
alter table emp2
add constraint emp2_empno_pk primary key (empno);
insert into emp2 values (7000,'홍길동',3000,'사원');
==> 1 row created.
insert into emp2 values (7000,'홍길동',3000,'사원');
==> ORA-00001: unique constraint (SCOTT.EMP2_EMPNO_PK) violated
insert into emp2 values (null,'홍길동',3000,'사원');
==> ORA-01400: cannot insert NULL into ("SCOTT"."EMP2"."EMPNO")
drop table emp2;
create table emp2
(
empno number(4),
ename varchar2(15),
sal number(7,2),
job varchar2(15)
);
alter table emp2
add constraint emp2_pk primary key (empno, ename);
==> Table altered. (empno와 ename을 조합한 한 개의 primary key설정!!)
insert into emp2 values (8000,'홍길동',3000,'사원');
-->성공
insert into emp2 values (8000,'길라임',3000,'사원');
-->성공
insert into emp2 values (8002,'길라임',3000,'사원');
-->성공
insert into emp2 values (8002,'길라임',3000,'사원');
--> ORA-00001: unique constraint (SCOTT.EMP2_PK) violated
문제) emp2테이블의 primary key(emp2_pk)를 삭제하시오.
삭제1)
alter table emp2
drop constraint emp2_pk;
--> Table altered.(변경 성공!!)
alter table emp2
add constraint emp2_pk primary key (empno);
--> Table altered.(변경 성공!!)
시스템테이블 : user_XXs
----> user_constraints
select table_name, constraint_name, constraint_type
from user_constraints
where table_name='EMP2';
삭제2)
alter table emp2
drop primary key;
===> Table altered.
문제) emp2테이블을 새롭게 생성(empno, ename, sal, deptno컬럼)하고
테이블 생성 후 empno에 primary key 설정을 하고
ename, sal, deptno에 not null을 설정하시오.
drop table emp2;
create table emp2
(
empno number(4),
ename varchar2(15),
sal number(7,2) ,
deptno number(2)
);
alter table emp2
add constraint emp2_empno_pk primary key (empno);
==> 변경 성공!!
또는
alter table emp2
add primary key (empno);
alter table emp2
add constraint emp2_ename_nn not null (ename);
*
==> ORA-00904: : invalid identifier
==> add not null 안되는 이유?
테이블내의 모든 컬럼은 특별한 설정(not null)을 하지 않아도
null값이 기본으로 설정되어 있음!!
<제약수정>
ALTER TABLE 테이블명
MODIFY 컬럼명 CONSTRAINT 제약명 NOT NULL | NULL;
alter table emp2
modify ename not null;
SQL> desc emp2
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME NOT NULL VARCHAR2(15)
SAL NUMBER(7,2)
DEPTNO NUMBER(2)
alter table emp2 modify sal not null;
alter table emp2 modify deptno not null;
SQL> desc emp2
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME NOT NULL VARCHAR2(15)
SAL NOT NULL NUMBER(7,2)
DEPTNO NOT NULL NUMBER(2)
문제) emp2테이블의 sal컬럼의 not null 제약을 삭제하시오!!
alter table emp2
drop constraint emp2_sal_nn;
또는
alter table emp2
modify sal null;
not null ---> null
SQL> desc emp2
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME NOT NULL VARCHAR2(15)
SAL NUMBER(7,2)
DEPTNO NOT NULL NUMBER(2)
4. foreign key(외래키, 참조키)
- 참조하는 테이블의 컬럼데이터 범위내의 데이터만 입력.
- 예) 사원들은 반드시 존재하는 부서의 번호를 속성값으로 가져와야 함!!
===> 사원테이블(emp)에서 사원에 대한 정보를 입력, 수정시
부서테이블(dept)로 부터 존재하는 부서번호인지를 참조, 확인 해야함.
- 참조되는 컬럼은 unique 또는 primary key설정이 되어 있어야 함.
사원테이블(deptno 입력, 수정) -------> 부서테이블(deptno 참조)
10
20
30
40
--> 컬럼레벨
create table 테이블명emp
(
컬럼명 자료형 [constraint 제약명] REFERENCES 참조테이블명(참조컬럼명)
deptno number(2) REFERENCES dept(deptno)
);
--> 테이블레벨
create table 테이블명emp
(
컬럼명1 자료형,
컬럼명2 자료형,
컬럼명3 자료형,
[constraint 제약명] FOREIGN KEY (컬럼명2)
REFERENCES 참조테이블명(참조컬럼명)
----> foreign key (deptno) references dept(deptno)
);
--> 테이블 변경
alter table 테이블명
ADD [constraint 제약명] foreign key (deptno)
references dept(deptno);
---------------------------------------------------------
참조되는 테이블(부모테이블) dept2
drop table dept2;
create table dept2
as select * from dept;
----> 구조와 데이터를 복사 (제약은 제외!!)
---------------------------------------------------------
참조하는 테이블(자식테이블) emp2
drop table emp2;
create table emp2(
empno number(4),
ename varchar2(15),
sal number(7,2),
deptno number(2)
);
문제) emp2테이블내의 empno에 primary key설정,
deptno에 foreign key설정을 하시오.
alter table emp2
add constraint emp2_empno_pk primary key (empno);
---> Table altered.(변경성공!!)
alter table emp2
add constraint emp2_deptno_fk
foreign key (deptno) -- 현재테이블의 컬럼
references dept2(deptno); -- 참조테이블의 컬럼
----> ORA-02270: no matching unique or primary key for this column-list
참조되는 컬럼(dept2테이블--->deptno컬럼)은 unique 또는 primary key 설정
- dept2의 deptno: unique설정
alter table dept2
add constraint dept2_uk unique( deptno);
또는
alter table dept2
add constraint dept2_pk primary key( deptno);
==> 변경성공!!
alter table emp2
add constraint emp2_deptno_fk
foreign key (deptno) -- 현재테이블의 컬럼
references dept2(deptno); -- 참조테이블의 컬럼
==> 변경성공!!
'Programming > DataBase' 카테고리의 다른 글
[Oracle] PL/SQL (0) | 2017.11.09 |
---|---|
[Oracle] 시퀀스(Sequence) (0) | 2017.11.09 |
[Oracle] 뷰 (View) 단일뷰/복합뷰 (0) | 2017.11.08 |
[Oracle] 트랜잭션(Transaction) COMMIT/ROLLBACK (2) | 2017.11.07 |
[Oracle] Merge 테이블 합치기 (2) | 2017.11.07 |