[Oracle] constraint (제약조건)

▶ 데이터 무결성(Integrity) 

저장된 데이터에는 잘못된 데이터가 없어야 한다

ex) primary key로 설정된 field는 중복된 값이나 null이 있어서는 안된다

     성별 field에는 남, 여 이외의 다른 값이 들어올 수 없다

     주문수량 field는 반드시 1 이상의 값이 들어와야 한다

 

▷ 무결성 종류

무결성 이름	내용	관련 제약조건
널 무결성	릴레이션의 속성값이 NULL이 될 수 없다	NOT NULL
고유 무결성	특정 속성에 속한 값은 서로 달라야 한다	UNIQUE, NOT NULL, PRIMARY KEY
참조 무결성	외래키 값은 NULL이거나, 참조할 수 없는 외래키 값을 가질 수 없다	FORIGEN KEY
도메인 무결성	특정 속성 값은 그 속성이 정의된 domain에 속한 값이어야 한다	CHECK, DEFALUT, NOT NULL
관계 무결성	릴레이션	ON DELETE, ON UPDATE
개체 무결성		PRIMARY KEY

 

 

 

▶ 제약조건(constraint)

DBMS는 데이터의 무결성을 보장하기 위해 잘못된 데이터가 저장되는 것을 방지하기 위해 제약조건(constraint)를 사용한다

제약조건은 table의 column에 설정하여 사용한다

해당 column에 설정된 제약조건에 위배된 데이터가 들어오는 경우 error가 발생한다

 

▷ constrain 종류

제약조건 이름 (약자)	제약조건의 의미
NOT NULL (NN)	이 조건이 설정된 column에는 null 입력이 불가하다
UNIQUE (UK)	이 조건이 설정된 column에는 중복값 입력이 불가하다
PRIMARY KEY (PK)	table 당 '단 1개' 설정 가능하고, 데이터의 유일성을 보장한다 NOT NULL + UNIQUE 의 의미
FORIGEN KEY (FK)	다른 table의 column을 참조해서 무결성 검사를 한다 외래키 값은 NULL 혹은 참조할 수 있는 값이어야 한다
CHECK (CK)	설정된 조건에 맞는 값만 입력을 허용한다

 

▷ 제약조건 설정하는 방법

나중에는 제약조건을 활성화/비활성화 하는 등의 관리가 필요할 때가 생긴다

이렇게 제약조건을 관리하려면 제약조건에 '이름'을 붙여서 관리해야 한다

실무에서는 제약조건에 이름을 지정하는 것을 권장한다

이름 없이 제약 조건을 설정하면 orcale이 알아서 이름을 붙여주지만 읽기 힘들고 다루기 어렵다

 

1. table 생성 시 동시에 제약조건을 설정하는 방법

[실습] #9001

DROP TABLE t_emp4 CASCADE CONSTRAINT purge; 

CREATE TABLE t_emp4 (
	NO number(4) PRIMARY KEY 
	, name varchar2(10) NOT NULL
	, jumin varchar2(13) NOT NULL UNIQUE -- 제약조건 여러개 설정 가능하다
	, area number(1) CHECK(area < 5) -- area 조건
	, deptno varchar(6) REFERENCES t_dept2(dcode) -- d_dept2의 dcode를 참조 (외래키) dcode 값 이외의 값은 받을 수 없다
);

 

2. 테이블 생성 후 제약조건 추가하는 방법

* not null은 별도의 항목으로 만들지 못한다!!

foreign key 참조당할 column references 참조할 테이블(참조할 column)

DROP TABLE t_emp4 CASCADE CONSTRAINT purge; 

CREATE TABLE t_emp4 (
	NO number(4) 
	, name varchar2(10) NOT NULL -- NOT null은 별도의 항목으로 만들지 못한다
	, jumin varchar2(13) NOT NULL 
	, area number(1) 
	, deptno varchar(6) 
	, PRIMARY key(no) 
	, UNIQUE(jumin)
	, CHECK(area < 5)
	, FOREIGN KEY(deptno) REFERENCES t_dept2(dcode)
);

 

 

 

▶ [번외]

Q. 테이블에 존재하는 column에 대해서는 DESC 명령어로 알 수 있는데 내가 만든 테이블의 전체 정보를 알 수 있는 방법이 있을까?

A. data dictionary (데이터 딕셔너리)

스키마, 사용자, 객체, 권한, 롤, 데이터베이스의 정보 등등 oracle database를 운영하는데 필요한 정보를 관리하는 별도의 객체들을 일컫는다

시스템 정보를 가지고 있기 때문에 (건드리면 위험하다) 보통 view를 통해서만 조회할 수 있다

 

dictionary에 사용하는 view들은 dictionary view(딕셔너리 뷰) 라고 하는데 이것은 system view에 속한다

이러한 data dictionary view는 database 생성 시에 oracle system에 의해서 자동으로 생성된다

v$, 이것을 dynamic performance view(동적 성능 뷰) 라고 하며 dictionary view와 마찬가지로 system vuew의 한 종류이다 메모리나 현재 세션에 관해서 정보를 알 수 있는 뷰이다

 

▷ dictionary view 종류

ALL : sql에 접속 중인 사용자에게 권한이 존재하는 모든 객체와 접근 간으한 객체에 정보를 조회할 수 있다

DBA : all과 달리 DB 관리자들만 접속이 가능하다. 모든 객체에 대해 접근이 가능하다

USER : sql에 접속 중인 사용자가 소유자인 객체의 정보를 조회할 수 있다

 

 

 

▶ 설정되어 있는 제약조건 조회

눈에 보이지 않지만 database system에 내부적으로 많은  database를 운영하고 있다 - dictionary

테이블에 제약조건을 설정하면 그 내용은 dictionary에 저장되어 있다

사용자 dictionary : USER_CONSTRAINTS 와 USER_CONS_COLUMNS 사용

DB 전체 dictionary : DBA_CONSTARINTS 와 DBA_CONSTRAINT_COLUMNS 사용

 

* 테이블명은 대문자로. 내부적으로 저장된 건 대문자로 표현된다

* 제약조건 이름은 제약조건을 작성할 때마다  oracle이 일련의 이름으로 만들어준다

 

▷ CONSTRAINT TYPE
'C' : NN, CK
'P' : PK
'U' : UK
'R' : FK

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS
FROM USER_CONSTRAINTS
WHERE table_name = 'T_EMP4'; -- 테이블명 대문자로. 내부적으로 저장된 건 대문자로 표현된다

 

▷ 제약조건명을 명시하여 정의하기

1. t_emp3 table을 삭제

cascade : 두 테이블을 연결해서 pk를 가지고 있는 쪽의 값을 삭제하면 fk로 연결된 값이 동시에 삭제되게 하는 옵션

purge :  테이블 완전 삭제하기 (휴지통에 저장되지 않음)

DROP TABLE t_emp3 CASCADE CONSTRAINT purge;

2.  제약조건명 명시

SELECT TABLE t_emp3 (
	no number(4),
    jumin VARCHAR2(10) CONSTRAINT emp3_name_nn NOT NULL, -- 잊지말자 NOT NULL은 따로 명시 안된다
    area NUMBER(1),
    deptno VARCHAR(6),
    CONSTRAINT emp3_no_pk PRIMARY KEY(no),
    CONSTRAINT emp3_no_pk UNIQUE(jumin),
    CONSTRAINT emp3_area_ck CHECK(area < 5),
    CONSTRAINT emp3_deptno_fk FORIEGN deptno REFERENCES t_dept2(dcode)
);

3. 제약조건 조회 - 내가 바꾼 제약조건명으로 나온다

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE ,STATUS
FROM USER_CONSTRAINTS 
WHERE table_name = 'T_EMP3';

 

 

 

* 에러메시지 보고 이게 자바에서 발생한건지 데이터베이스에서 발생한건지 알 수 있어야 한다 !!!!

Q. 제약조건에 위배하면 무슨 일이 일어날까?

PRIMARY KEY ERROR

A. 이 코드를 또 실행하면 primary key(no)에 동일한 값이 들어가기에 에러 발생 !

    primary key는 유일성을 띈다. null과 중복값이 있어서는 안된다

SQL Error [1] [23000]: ORA-00001: unique constraint (SCOTT726.EMP3_NO_PK) violated

INSERT INTO t_emp3 values(
	1, '오라클', '1234561234567', 4, 1000 
);

 

UNIQUE ERROR

A. jumin은 not null, unique의 제약조건을 가지고 있는데 이미 있는 값을 입력했기에 에러 발생 !

SQL Error [1] [23000]: ORA-00001: unique constraint (SCOTT726.EMP3_JUMIN_UK) violated

INSERT INTO t_emp3 values(
	2, '오라클', '1234561234567', 4, 1000 
);

 

VARCHAR2(13) 초과 오류

A. area의 글자 크기는 13까지인데 그 범위를 초과하였기에 에러 발생 !

SQL Error [12899] [72000]: ORA-12899: value too large for column "SCOTT726"."T_EMP3"."JUMIN" (actual: 19, maximum: 13)

INSERT INTO t_emp3 values(
	2, '오라클', '2222222222222222222', 4, 1000 
);

 

PARENT KEY NOT FOUND ERROR

A. 참조하고 있는 t_dept2의 dcode 테이블엔 2000이라는 숫자가 존재하지 않아 에러 발생 !

SQL Error [2291] [23000]: ORA-02291: integrity constraint (SCOTT726.EMP3_DEPTNO_FK) violated - parent key not found

INSERT INTO t_emp3 values(
	3, 'tigers', '33333333', 3, 2000 
);

 

name의 NOT NULL 위배 오류

A. name column이 NOT NULL 제약조건을 가지고 있는데 없기에 에러 발생 !

SQL Error [1400] [23000]: ORA-01400: cannot insert NULL into ("SCOTT726"."T_EMP3"."NAME")

INSERT INTO t_emp3(NO, jumin, area, deptno) values(
	3, '33333333', 4, 1001
);

 

area의 UNIQUE 위배 오류

A. area < 5 의 조건이므로 update 했을 때도 적용해야하기에 에러 발생 !

SQL Error [1438] [22003]: ORA-01438: value larger than specified precision allowed for this column

이 열에 허용된 지정된 정밀도보다 큰 값

UPDATE t_emp3 SET area = 10 WHERE NO = 1;

 

참조되고 있는 부모는 삭제 불가

A. 부모 : t_dept2 자식 : t_emp3

[on delete restrict] : (default) 참조되는 부모 쪽 삭제 허용 안함
[on delete cascade] : 참조되는 부모 쪽 삭제되면 참조하는 자식도 함께 삭제됨
* create table에 명시할 수 있다

SQL Error [2292] [23000]: ORA-02292: integrity constraint (SCOTT726.EMP3_DEPTNO_FK) violated - child record found

SQL 오류 [2292] [23000]: ORA-02292: 무결성 제약 조건(SCOT726).EMP3_DEPTNO_FK) 위반 - 하위 레코드 발견

DELETE FROM t_dept2 WHERE dcode = 1000;

 

 

▷ alter 명령으로 테이블에 제약조건 추가 가능

t_emp4.name column에 unique 제약조건 추가

ALTER TABLE t_emp4
ADD CONSTRAINT emp4_name_uk UNIQUE(name);

 

t_emp4.area column에 not null 제약조건 추가

ALTER TABLE emp_4
ADD CONSTRAINT emp4_area_nn NOT NULL(area);

 

t_emp4.area column에 not null 제약조건 수정

ALTER TABLE t_emp4
MODIFY (area CONSTRAINT emp4_area_nn NOT NULL); -- check 삭제

 

외래키 추가

[실습] #9007

t_emp4 테이블의 no 컬럼이 t_emp2 테이블의 empno 컬럼의 값을 참조하도록 참조키 제약조건을 설정하세요

그러나! 참조되는 부모테이블의 컬럼은 Primary Key 이거나 Unique 이어야 한다

SQL Error [2270] [42000]: ORA-02270: no matching unique or primary key for this column-list

참조되는 부모키는 UNIQUE이거나 PK이어야 한다. 그렇지 않으면 자식이 참조하지 못한다. 

ALTER TABLE t_emp4
ADD CONSTRAINT emp4_name_fk FOREIGN key(name) REFERENCES t_emp2(name);

 

** 부모 테이블의 name을 unique 제약조건을 부여하고 다시 위의 쿼리 수행하면 된다

ALTER TABLE t_emp2
ADD CONSTRAINT emp2_name_uk UNIQUE(name);

 

옵션

NO ACTION	참조 테이블(부모테이블)에 변화가 있어도 자식 테이블에 아무런 조치를 취하지 않는다
CASCADE	참조 테이블의 데이터가 삭제되면 자식 테이블의 관련 데이터도 모두 삭제, 참조 테이블의 데이터가 변경되면 자식 테이브르이 관련 데이터도 모두 변경된다
SET NULL	참조 테이블의 변화가 있으면 자식 테이블의 관련 데이터는 NULL로 변경된다
SET DEFAILT	참조 테이블의 변화가 있으면 자식 테이블의 관련데이터는 기본 데이터로 변경된다

 

 

 

ON DELETE CASCADE 

부모 테이블의 해당 데이터가 삭제되면 함께 삭제

CREATE TABLE t_emp3 (
	NO number(4)  CONSTRAINT emp3_no_pk PRIMARY KEY
	, name varchar2(10) CONSTRAINT emp3_name_nn NOT NULL -- NOT null은 별도의 항목으로 만들지 못한다
	, jumin varchar2(13) 
				CONSTRAINT emp3_jumin_nn NOT NULL
				CONSTRAINT emp3_jumin_uk UNIQUE 
	, area number(1) CONSTRAINT emp3_area_ck CHECK(area < 5)
	, deptno varchar(6) CONSTRAINT emp3_deptno_fk REFERENCES t_dept2(dcode)
			ON DELETE CASCADE -- 부모 테이블의 참조가 삭제되면 자식도 함께 삭제된다
);

 

기존 제약조건 삭제

ALTER TABLE t_emp3
DROP CONSTRAINT emp3_deptno_fk;

 

 

복합키 제약조건

지금까지는 하나의 PK만 지정했는데 여러 개의 COLUMN에 만들 수 있다

CREATE TABLE test_member (
    mb_uid NUMBER NOT NULL,
    mb_nick VARCHAR2(10) NOT NULL,
    mb_name VARCHAR2(10) NOT NULL,
    CONSTRAINT test_member_pk PRIMARY KEY(mb_uid, mb_nick)
    -- 앞으로 이 두개의 컬럼 조합이 중복되지 않으면 된다 
);

INSERT INTO test_member values(1, 'aaa', 'john');
INSERT INTO test_member values(1, 'bbb', 'john'); -- 가능
INSERT INTO test_member values(2, 'aaa', 'john'); -- 가능
INSERT INTO test_member values(1, 'aaa', 'john'); -- 불가능
-- 복합키는 조합이 같아야 중복인 것 고로 이렇게 aaa, bbb 만 달라도 가능하다
-- 특정 컬럼이 중복되는 게 아닌 조합!이 중복이 되야 중복인 것이다

SELECT * FROM test_member;