반응형
1. 제약조건이란 ?
테이블 열에 저장될 데이터의 특성, 조건을 지정하는 최소한의 장치
| 종류 | 설명 |
|---|---|
| NOT NULL | 지정한 열에 NULL을 허용하지 않는다. NULL을 제외한 데이터의 중복은 허용된다. |
| UNIQUE | 지정한 열이 유일한 값을 가져야 한다. 즉, 중복될 수 없다. 단, NULL은 값의 중복에서 제외된다. |
| PRIMARY KEY | 지정한 열이 유일한 값이면서 NULL을 허용하지 않는다. PRIMARY KEY는 테이블에 하나만 지정 가능하다. |
| FOREIGN KEY | 다른 테이블의 열을 참조하여 존재하는 값만 입력할 수 있다. |
| CHECK | 설정한 조건식을 만족하는 데이터만 입력 가능하다. |
1.1.
1.2. 제약 조건 지정 방법
- 테이블을 생성하며 제약 조건 지정하기
- 테이블을 생성하며 제약 조건 이름 직접 지정하기
1.3. 데이터 무결성이란?
데이터베이스에 저장되는 데이터의 정확성과 일관성을 보장한다
무결성 종류
| 종류 | 설명 |
|---|---|
| 영역 무결성(domain integrity) | 열에 저장되는 값의 적정 여부를 확인. 자료형, 적절한 형식의 데이터, NULL의 여부 같은 정해 놓은 범위를 만족하는 데이터임을 규정 |
| 개체 무결성(entity integrity) | 테이블 데이터를 유일하게 식별할 수 있는 기본키는 반드시 값을 가지고 있어야 하며 NULL이 될 수 없고 중복될 수도 없음을 규정 |
| 참조 무결성(referential integrity) | 참조 테이블의 외래키 값은 참조 테이블의 기본키로서 존재해야 하며 NULL이 가능 |
1.4. 제약 조건 확인하기
USER_CONSTRAINTS
| 열 이름 | 설명 |
|---|---|
| OWNER | 제약 조건 소유 계정 |
| CONSTRAINT_NAME | 제약 조건 이름(직접 지정하지 않을 경우 오라클이 자동으로 지정함 |
| CONSTRAINT_TYPE | 제약 조건 종류C: CHECK, NOT NULLU: UNIQUEP: PRIMARY KEYR: FOREIGN KEY |
| TABLE_NAME | 제약 조건을 지정한 테이블 이름 |
2. NOT NULL
데이터의 중복 여부와는 상관없이 NULL의 저장을 허용하지 않는 제약 조건
제약조건을 설정하였으나, 이름을 지정하지 않으면 오라클에서 자동으로 이름을 지정해준다.
CONSTRAINT 키워드를 사용해서 이름을 지정할 수 있다. 여기서는 테이블명_컬럼명_제약조건 순으로 이름을 정했다.
<sql />
SQL> alter table emp2
modify ename verchar2(10) not null;
SQL> desc emp2
SQL> select table_name, constraint_name, constraint_type from user_constraints
where table_name = 'EMP2';
---
SQL> alter table emp2
SQL> modify ename varchar2(10) constraint emp2_ename_nn not null;
SQL> set linesize 200
select table_name, constraint_name, constraint_type from user constraints
where table_name = 'EMP2';
update emp
set ename = null
where empno = 7788;
// ename column은 not null 제약 때문에 오류
3. UNIQUE
중복 허용 X
null은 데이터가 존재하지 않음을 의미하므로 중복 대상에서는 제외된다. → null은 여러개 저장 가능하다.
<code />
SQL> alter table dept2
add constraint dept_dname_uq unique (dname);
SQL> set linesize 200
select table_name, constraint_name, constraint_type
from user constraints
where table_name = 'DEPT2';
SQL> select * from dept2;
SQL> insert into dept2
values (50, 'SALES', 'DALLAS');
//중복 값이 들어가므로 오류
4. PRIMARY KEY
null 허용 X, 중복 허용 X
테이블에 하나만 지정할 수 있다.
PRIMARY KEY로 지정하면 자동으로 인덱스가 만들어 진다.
<code />
SQL> alter table emp2
add constraint emp2_empno_pk primary key (empno);
SQL> select table_name, constraint_name, constraint_type
from user_constraints
where table_name IN ('EMP2', 'DEPT2');
SQL> select empno, ename, job
from emp2
where job = 'ANALYST';
SQL> update emp2
set empno = 7788
where ename = 'FORD';
5. FOREIGN KEY
다른 테이블의 PRIMARY KEY를 참조
자식쪽에서 FOREINGN KEY 설정해준다.
참조하고 있는 키의 데이터와 NULL만 저장 가능하다.
FOREIGN KEY로 참조 행 데이터 삭제하기
ON DELETE CASCADEON DELETE SET NULL
<code />
SQL> alter table dept2
add constraint dept2_deptno_pk primary key (deptno);
SQL> alter table emp2
add constraint emp2_deptno_fk foreign key (deptno) references dept2 (deptno);
SQL> select table_name, constraint_name, constraint_type
from user_constraints
where table_name IN ('EMP2', 'DEPT2');
---
SQL> delete from dept2
where deptno = 20;
SQL> update dept2
set deptno = 25
where deptno = 20;
SQL> select enamen, deptno
from emp2
where ename = 'SMITH';
SQL> update emp2
set deptno = 80
where ename = 'SMITH';
SQL> update emp2
set deptno = NULL
where ename = 'SMITH';
<code />
//확인하기
SQL> delete from dept2
2 where deptno = 20;
delete from dept2
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.EMP2_DEPTNO_FK) violated - child record found
SQL> update dept2
2 set deptno = 25
3 where deptno = 20;
update dept2
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.EMP2_DEPTNO_FK) violated - child record found
SQL> update emp2
2 set deptno = 80
3 where ename = 'SMITH';
update emp2
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.EMP2_DEPTNO_FK) violated - parent key not found
SQL> update emp2
2 set deptno = NULL
3 where ename = 'SMITH';
1 row updated.
6. CHECK
열에 저장할 수 있는 값의 범위 또는 패턴을 정의할 때 사용
<code />
SQL> alter table emp2
add constraint emp2_job_ck check (job in ('ANALYST','CLERK', 'MANAGER', 'PRESIDENT', 'SALESMAN'));
select table_name, constraint_name, constraint_type //제약조건이 잘 적용되었는지 확인
from user_constraints
where table_name IN ('EMP2', 'DEPT2');
SQL> update emp2
set job = '개발'
where ename = 'SMITH'
SQL> update emp2
set job = 'clerk'
where ename = 'SMITH';
7. Default
열에 저장할 값이 지정되지 않았을 경우 기본값(default)을 지정
<code />
SQL> alter table emp2
modify hiredate date default sysdate;
SQL> insert into emp2 (empno, ename, job, sal, deptno)
values(9999, 'PEONY', 'CLERK', 777, 40);
select ename, job, to_char(hiredate, 'YYYY-MM-DD HH24:MI:SS') hiredate
2 from emp2
3 where ename = 'PEONY';
ENAME JOB HIREDATE
-------------------- ------------------ --------------------------------------
PEONY CLERK 2024-07-12 14:17:30
SQL> update emp2
2 set hiredate = 'default'
3 where ename = 'SMITH';
set hiredate = 'default'
*
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
SQL> select ename, job, to_char(hiredate, 'YYYY-MM-DD HH24:MI:SS') hiredate
2 from emp2
3 where ename = 'SMITH';
ENAME JOB HIREDATE
-------------------- ------------------ --------------------------------------
SMITH CLERK 1980-12-17 00:00:00
8. 테이블 생성할 때 제약 조건 넣기
<code />
SQL> drop table emp2;
SQL> drop table dept2;
SQL> create table dept2 (
deptno number(2) constraint dept2_deptno_pk primary key
, dname varchar2(14) constraint dept2_dname_uq unique
, loc varchar2(13) constraint dept2_loc_nn not null
);
다른 방식
<code />
create table dept2 (
deptno number(2)
, dname varchar2(14)
, loc varchar2(13)
, constraint dept2_deptno_pk primary key
, constraint dept2_dname_uq unique
, constraint dept2_loc_nn not null
);
create table emp2(
empno number(4) constraint emp2_empno_pk primary key
, ename varchar2(10) constraint emp2_ename_nn not null
, job varchar2(9) constraint emp2_job_ck check (job in ('ANALYST','CLERK', 'MANAGER', 'PRESIDENT', 'SALESMAN'))
, mgr number(4) constraint emp2_mgr_fk references emp2 (empno)
, hiredate date default sysdate
, sal number(7, 2) constraint emp2_sal_ck check (sal between 700 and 9999)
, comm number(7, 2)
, deptno number(2) constraint emp2_deptno_fk references dept2 (deptno)
);
<code />
SQL> set linesize 50
desc emp
Name Null? Type
----------------------- -------- ----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
// 테이블 정의서
EMPNO NUMBER(4) PK
ENAME VARCHAR2(10) NN
JOB VARCHAR2(9) CK (ANA.., CLE.., MAN.., PRE.., SAL..)
MGR NUMBER(4) FK (EMP2.EMPNO))
HIREDATE DATE DK (sysdate)
SAL NUMBER(7,2) CK (700~9999)
COMM NUMBER(7,2)
DEPTNO NUMBER(2) FK (DEPT2.DEPTNO)
8.1. 복합 column에 제약 조건을 설정하기
<code />
create table memder (
id varchar2(15) constraint member_id_pk primary key
, password varchar2(15) constraint member_password_nn not null
, name varchar2(15) constraint member_name_nn not null
, phone varchar2(11) constraint member_phone_nn not null
, jumin1 number(6) constraint member_jumin1_nn not null // 주민번호 앞자리
, jumin2 number(7) constraint member_jumin2_nn not null // 주민번호 뒷자리
, constraint member_phone_uq unique (phone)
, constraint member_jumin12_uq unique (jumin1, jumin2) // 주민번호 전체로 하나의 제약조건을 설정함
);
반응형
