오라클 데이터 무결성(Data Integrity) 제약조건(Constraints), primarykey, foreignkey, not null, unique, check, 외래키, 주키, 참조무결성

오라클 데이터 무결성(Data Integrity) 제약조건(Constraints), primarykey, foreignkey, not null, unique, check, 외래키, 주키, 참조무결성

​

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=633

 

오라클 데이터 무결성(Data Integrity) 제약조건(Constraints), primarykey, foreignkey, not null, unique, check, 외래키, 주키, 참조무결성

오라클 데이터 무결성(Data Integrity) 제약조건(Constraints), primarykey, foreignkey, not null, unique, check, 외래키, 주키, 참조무결성8.1.6 데이터 무결성(Data Integrity) 제약조건(Constraints)무결성 제약조건이란?n IC(Integrity Constraints)는 하나의…

ojc.asia

8.1.6 데이터 무결성(Data Integrity) 제약조건(Constraints)

무결성 제약조건이란?

n IC(Integrity Constraints)는 하나의 테이블내의 칼럼 값에 대해 제약을 가하는 조건으로 데이터값의 무결함, 정확성, 일관성을 보장하기 위한 방법이다.

n 무결성 제약조건에 맞지 않는 데이터가 입력되면 DML(INSERT, UPDATE, DELETE, MERGE)은 롤백된다.

n 테이블 생성시 또는 생성 후 무결성 제약조건 추가 가능 하다.

n 테이블의 모든 무결성 제약조건은 데이터 딕셔너리에 저장된다.

n 제약조건을 지정할 때 이름을 부여하지 않으면 오라클 내부적으로 SYS_Cn 형태로 이름이 부여된다

무결성 제약조건(IC)	Description
NOT NULL	칼럼값은 NULL을 가질 수 없다.
PRIMARY KEY(기본키)	테이블의 행을 유일하게 구별하기 위한 키 Unique Constrinats, NOT NULL Constraints를 결합한 형태
FOREIGN KEY(외래키)	같거나/다른 테이블의 PK/UK값을 참조하기 위한 제약조건 (EMP 테이블에서 DEPTNO 칼럼은 값이 입력된다면 반드시 DEPT테이블의 DEPTNO 칼럼 값이 입력되어야 한다.)
UNIQUE	칼럼 값은 유일해야 한다.
CHECK	칼럼에 입력되는 값을 지정하거나 그 범위를 지정

무결성 제약조건 정의

컬럼 레벨(Column Constraints Level) : 단일 칼럼에 정의하는것으로 칼럼의 정의가 끝나기 전에 제약조건을 정의하며 모든 무결성 제약조건을 지정 가능하다.

테이블 레벨(Table Constraints Level) : 칼럼에 대한 정의가 모든 끝난 후 콤마(,)을 찍고 그 다음에 제약조건을 기술하는 것으로 한 개 또는 여러 칼럼에 대해 정의가 가능하며, NOT NULL 제약조건은 칼럼레벵에서만 정의가 가능하다.

[기본 형식]

칼럼 레벨 :column_name [CONSTRAINT constraint_name] constraint_type

테이블 레벨 : column,,, [CONSTRAINT constraint_name]constraint_type(column_name,,,)

SQL> drop table t1; 테이블이 삭제되었습니다. --칼럼레벨로 PK 제약조건 정의, constraint 키워드를 사용한다면 반드시 다음에 이름을 기술해야 한다. 기술하지 않으면 SYS_CnXXX 형태로 오라클이 임의로 부여한다. SQL> create table t1 ( id number constraint pk_t1 primary key, name varchar2(10)); 테이블이 생성되었습니다. -- user_constraints 딕셔너리뷰를 통해 만든 Primary Key 제약조건을 확인하자. SQL> desc user_constraints; 이름 널? 유형 ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) …… -- Primary Key인 경우 CONSTRAINT_TYPE이 ‘P’ SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from user_constraints where TABLE_NAME = 'T1'; CONSTRAINT_NAME C TABLE_NAME ------------------------------ - ------------------------------ PK_T1 P T1 --테이블 레벨로 제약조건을 정의해 보자. PK인 경우 자동으로 NOT NULL, Unique KEY 제약조건이 붙으므로 칼럼에 별도로 기술 안해도 된다. SQL> drop table t1; 테이블이 삭제되었습니다. SQL> create table t1 ( id number , name varchar2(20), constraint pk_t1 primary key (id)); 테이블이 생성되었습니다.

NOT NULL 제약조건

n 칼럼의 값이 NULL 일 수 없다는 제약조건 이다.(반드시 NULL이 아닌 값을 가져야 한다)

n 칼럼 레벨로만 정의가 가능하며 NOT NULL 같이 DEFAULT 값도 지정 가능하다.

n 테이블에 값을 INSERT할 경우 NOT NULL 칼럼은 반드시 기술해야 한다.(DEFAULT로 정의한 경우는 제외)

SQL> drop table t1; 테이블이 삭제되었습니다. --id칼럼은 Primary Key이므로 자동으로 NOT NULL, Unique Key 제약조건을 가진다. SQL> create table t1 ( id number , name varchar2(20) default '오라클자바커뮤니티' not null, constraint pk_t1 primary key (id)); 테이블이 생성되었습니다. SQL> insert into t1 (id) values (1); 1 개의 행이 만들어졌습니다. SQL> commit; 커밋이 완료되었습니다. SQL> select * from t1; ID NAME ---------- -------------------- 1 오라클자바커뮤니티

Unique Key 제약조건

n 칼럼의 값은 유일해야 한다는 제약조건으로 하나의 칼럼 또는 여러 칼럼(32개이내)으로 정의 가능하다.

n NULL값은 항상 유일하므로 입력이 가능 하다.

n 칼럼 레벨, 테이블 레벨(주로 복합칼럼으로 제약조건을 만드는 경우) 모두 정의 가능하다.

n Unique Key 제약조건이 지정되면 제약조건과 같은 이름의 인덱스가 자동으로 생성된다.

SQL> drop table t1; 테이블이 삭제되었습니다. SQL> create table t1 ( id number unique not null, name varchar2(20) ); 테이블이 생성되었습니다. SQL> insert into t1 values (1, '1길동'); 1 개의 행이 만들어졌습니다. SQL> insert into t1 values (2, '2길동'); 1 개의 행이 만들어졌습니다. SQL> insert into t1 values (1, '1길동'); insert into t1 values (1, '1길동')* 1행에 오류: ORA-00001: 무결성 제약 조건(SCOTT.SYS_C0011528)에 위배됩니다 SQL> commit; 커밋이 완료되었습니다. --사용자가 작성한 제약조건을 확인하는 딕셔너리뷰에서 확인, Unique Key 제약조건은 제약조건 타입이 ‘U’ 이다. SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from user_constraints 2 where TABLE_NAME = 'T1'; CONSTRAINT_NAME C TABLE_NAME ------------------------------ - ------------------------------ SYS_C0011527 C T1 SYS_C0011528 U T1 -- 사용자가 작성한 인덱스를 user_indexes 딕셔너리 뷰에서 확인하자. 제약조건의 이름으로 인덱스가 만들어져 있음을 알 수 있다. SQL> select index_name, table_name from user_indexes where table_name = 'T1'; INDEX_NAME TABLE_NAME ------------------------------ ------------------------------ SYS_C0011528 T1

Primary Key 제약조건

n 한 테이블에서 레코드를 유일하게 구별하기 위한 키로써 주키 또는 기본키라고 부른다.

n 한 테이블당 하나의 PK만 가질 수 있다.

n PK로 지정된 칼럼은 NULL값을 가질 수 없다.

n PK로 지정된 칼럼은 자동 NOT NULL이고 Unique Key 제약조건을 가지고 제약조건의 이름으로 Unique Key Index를 생성한다.

n 칼럼 레벨 또는 테이블 레벨로 정의 가능하며 PK칼럼이 여러 개인 경우 테이블 레벨로 정의해야 한다.

SQL> drop table t1; 테이블이 삭제되었습니다. SQL> create table t1 ( id number constraint pk_t1 primary key, name varchar2(20)); 테이블이 생성되었습니다. SQL> drop table t1; 테이블이 삭제되었습니다. SQL> create table t1 ( id number , name varchar2(20), constraint pk_t1 primary key (id) ); 테이블이 생성되었습니다.

외래키(Foreign Key) 제약 조건

n 외래키 제약조건은 한 테이블의 칼럼 값이 참조되는 테이블의 PK, UK 값만 오도록 할 때 지정하는 제약조건 이다. 즉 외래키로 지정할 때 참조되는 테이블의 칼럼은 Primary Key 또는 Unique Key 이어야 한다.

n 외래키를 포함한 테이블을 자식테이블(Detail/Child Table)이라 하며 참조되는 키(Referencial Key)를 가지는 테이블을 부모 테이블(Master/Parent Table) 이라고 한다.

n 부모 테이블의 PK, UK를 참조하는 자식테이블의 칼럼을 외래키 칼럼이라 하고 부모 테이블의 칼럼을 참조 키라고 한다.

n 외래키와 참조되는 키가 하나의 테이블에 존재할 수 있으며 이를 자기참조(Self Reference)라고 하고 이 경우 부모테이블과 자식테이블이 동일하다.(EMP 테이블에서 mgr 칼럼은 외래키 칼럼으로 empno 컬럼을 참조한다.)

--제약조건을 확인 할 수 있는 딕셔너리 뷰에서 EMP 테이블에 생성된 제약조건을 조회 SQL> select constraint_name, constraint_type from user_constraints where table_name = 'EMP'; CONSTRAINT_NAME C ------------------------------ - PK_EMP P FK_DEPTNO R ß 외래키를 의미한다. -- DEPT 테이블의 deptno 칼럼을 참조하는 EMP 테이블에 생성된 외래키를 삭제 SQL> alter table emp drop constraint fk_deptno; 테이블이 변경되었습니다. --다시 생성 SQL> alter table emp add constraint fk_deptno foreign key(deptno) references dept(deptno); 테이블이 변경되었습니다. SQL> drop table t1; 테이블이 삭제되었습니다. SQL> drop table t2; 테이블이 삭제되었습니다. --부모테이블을 먼저 생성, 외래키로 내려 줄 칼럼은 PK 또는 UK 칼럼만 가능 하다. SQL> create table t1 ( id number primary key, name varchar2(20)); 테이블이 생성되었습니다. --Number 칼럼은 NULL도 가능하지만 값이 들어올 때는 t1 테이블의 id 칼럼의 값이 와야 한다. SQL> create table t2 ( sale_date date, id number references t1(id), amount number ); 테이블이 생성되었습니다. SQL> insert into t1 values (1, '1길동'); 1 개의 행이 만들어졌습니다. SQL> insert into t2 values (sysdate, 1, 2000); 1 개의 행이 만들어졌습니다. SQL> insert into t2 values (sysdate, 2, 4000); insert into t2 values (sysdate, 2, 4000) * 1행에 오류: ORA-02291: 무결성 제약조건(SCOTT.SYS_C0011535)이 위배되었습니다- 부모 키가 없습니다 --자식테이블이 존재 하므로 부모테이블의 삭제를 막는다. SQL> drop table t1; drop table t1 * 1행에 오류: ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다 --테이블 삭제시 먼저 관련 참조무결성 제약조건을 삭제 후 테이블을 삭제한다. SQL> drop table t1 cascade constraint; 테이블이 삭제되었습니다.

아래 부모테이블과 자식테이블에서 가능한 DML을 표로 만들었다.

DELETE, UPDATE시 참조 무결성 규칙

규칙	설 명
Restrict	부모테이블의 참조되는 값(Referenced Value)에 대해 delete나 update를 허락하지 않는다
Set to Null	부모테이블의 참조되는 값(Referenced Value)에 delete나 update를 할 때 모든 자식테이블의 외래키값을 NULL로 만든다.
Set to Default	부모테이블의 참조되는 값(Referenced Value)에 delete나 update를 할 때 모든 자식테이블의 외래키값을 기본값으로 만든다.
Cascade	부모테이블의 참조되는 값(Referenced Value)에 delete나 update를 할 때 delete 될 때는 그 값을 가지는 자식레코드를 모두 삭제하고, update인 경우 해당 값으로 모두 변경 함
No Action	부모테이블의 참조되는 값(Referenced Value)에 대해 delete나 update를 허락하지 않음

SQL> drop table t2; 테이블이 삭제되었습니다. SQL> drop table t1; 테이블이 삭제되었습니다. SQL> create table t1 ( id number primary key, name varchar2(20)); 테이블이 생성되었습니다. --id 컬럼에 외래키를 생성했으며 기본적으로 Restrict Mode이므로 자식데이터가 있는 레코드는 삭제 불가하다. SQL> create table t2 ( sale_date date, id number references t1(id), amount number ); 테이블이 생성되었습니다. SQL> insert into t1 values (1, '1길동'); 1 개의 행이 만들어졌습니다. SQL> insert into t1 values (2, '2길동'); 1 개의 행이 만들어졌습니다. SQL> insert into t2 values (sysdate, 1, 2000); 1 개의 행이 만들어졌습니다. SQL> insert into t2 values (sysdate, 2, 4000); 1 개의 행이 만들어졌습니다. SQL> commit; 커밋이 완료되었습니다. -- t1의 id 값을 부모로 하는 자식데이터 t1의 id가 있으므로 기본적으로 삭제 불가 SQL> delete from t1 where id=1; delete from t1 where id=1 * 1행에 오류: ORA-02292: 무결성 제약조건(SCOTT.SYS_C0011537)이 위배되었습니다- 자식 레코드가 발견되었습니다 SQL> drop table t2; 테이블이 삭제되었습니다. --부모테이블의 값이 삭제될 때 자식테이블의 해당 값을 NULL로 바꾸기 위해 on delete set null을 사용했다. SQL> create table t2 ( sale_date date, id number references t1(id) on delete set null, amount number ); 테이블이 생성되었습니다. SQL> insert into t2 values (sysdate, 1, 2000); SQL>insert into t2 values (sysdate, 2, 4000); SQL> commit; SQL> select * from t1; ID NAME ---------- -------------------- 1 1길동 2 2길동 SQL> select * from t2; SALE_DAT ID AMOUNT -------- ---------- ---------- 14/09/10 1 2000 14/09/10 2 4000 SQL> delete from t1 where id=1; 1 행이 삭제되었습니다. SQL> select * from t2; SALE_DAT ID AMOUNT -------- ---------- ---------- 14/09/10 2000 ß id값이 NULL로 되었다. 14/09/10 2 4000 SQL> drop table t2; 테이블이 삭제되었습니다. --set default는 아직 구현되어 있지 않다. SQL> create table t2 ( sale_date date, id number references t1(id) on delete set default, amount number ); id number references t1(id) on delete set default, * 3행에 오류: ORA-03001: 현재에는 구현되어 있지 않은 기능입니다 --부모테이블 레코드삭제시 자식 테이블의 데이터를 삭제하는 옵션 on delete cascade 이용 SQL> create table t2 ( sale_date date, id number references t1(id) on delete cascade, amount number ); 테이블이 생성되었습니다. SQL> insert into t2 values (sysdate, 2, 4000); 1 개의 행이 만들어졌습니다. SQL> commit; 커밋이 완료되었습니다. SQL> select * from t1; ID NAME ---------- -------------------- 2 2길동 SQL> select * from t2; SALE_DAT ID AMOUNT -------- ---------- ---------- 14/09/10 2 4000 SQL> delete from t1 ; 1 행이 삭제되었습니다. SQL> select * from t2; 선택된 레코드가 없습니다.

CHECK 제약조건

칼럼에 입력되는 값의 범위를 지정하거나 특정한값만 입력 받고자 할 때 사용하는 제약조건

( 예 : myemp1 테이블의 성별 칼럼에는 ‘M’ or ‘F’ 만 입력 가능하도록 설정하고자 할 때)

SQL> create table mystudent ( id number, grade number not null check (grade in (1,2,3,4))); 테이블이 생성되었습니다. SQL> insert into mystudent values (1,1); 1 개의 행이 만들어졌습니다. --7학년은 CHECK 제약조건으로 인해 입력되지 않는다. SQL> insert into mystudent values (1,7); insert into student values (1,7) * 1행에 오류: ORA-02290: 체크 제약조건(SCOTT.SYS_C0011542)이 위배되었습니다

무결성 제약 조건의 삭제

ALTER TABLE table_name DROP CONSTRAINT constraint_name [CASCADE]

만약 부모테이블의 PK를 삭제하는 경우 자식테이블에 외래키가 형성되어 있다면 오류가 발생한다. 이 경우 자식테이블의 외래키를 먼저 삭제하거나 제약조건 삭제시 CASCADE 옵션으로 삭제가 가능하다.

SQL> drop table t1; SQL> drop table t2; SQL> create table t1 ( id number primary key, name varchar2(20)); 테이블이 생성되었습니다. SQL> create table t2 ( sale_date date, id number references t1(id), amount number); 테이블이 생성되었습니다. SQL> alter table t1 drop primary key; alter table t1 drop primary key * 1행에 오류: ORA-02273: 고유/기본 키가 외부 키에 의해 참조되었습니다 --아래의 경우 t1의 PK뿐 아니라 t1 테이블에 생성된 외래키도 함께 삭제된다. SQL> alter table t1 drop primary key cascade; 테이블이 변경되었습니다. --이제 T11 T2 테이블에는 제약조건이 남아 있지 않다. SQL> select constraint_name, constraint_type from user_constraints where table_name = 'T2'; 선택된 레코드가 없습니다. SQL> select constraint_name, constraint_type from user_constraints where table_name = 'T1'; 선택된 레코드가 없습니다. SQL> create table t1 ( id number constraint pk_t1 primary key, name varchar2(20)); 테이블이 생성되었습니다. --drop constraint 구문으로 제약조건 삭제 SQL> alter table t1 drop constraints pk_t1; 테이블이 변경되었습니다.

무결성 제약조건의 활성화, 비활성화

가끔은 대량의 데이터를 입력하는 경우 제약조건 검사로 인해 시간이 오래 걸리거나 일부 데이터 오류로 인해 무결성 제약조건을 비활성화 해야 될 경우가 있다. 이러한 경우 제약조건을 비활성화 한 후 다시 활성화 하면 된다.

[기본 형식]

ALTER TABLE table_name ENABLE[NOVALIDATE] CONSTRAINT constraint_name

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name

SQL> drop table t2; SQL> drop table t1; SQL> create table t1 ( id number constraint pk_t1 primary key, name varchar2(20)); 테이블이 생성되었습니다. SQL> alter table t1 disable constraint pk_t1 ; 테이블이 변경되었습니다. SQL> alter table t1 enable constraint pk_t1 ; 테이블이 변경되었습니다.

무결성 제약조건 조회

USER_CONSTRAINTS 또는 USER_CONS_COLUMNS를 이용하여 테이블에 생성된 제약조건을 검사 또는 제약조건이 생성된 칼럼을 조회 할 수 있다.

--EMP테이블과 T1 테이블에 생성된 제약조건과 그 상태를 조회하시오. SQL> desc user_constraints; 이름 널? 유형 ----------------------------------------- -------- ----------------------- OWNER VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS from user_constraints where table_name in ('EMP','T1'); CONSTRAINT_NAME C TABLE_NAME STATUS ------------------------------ - ------------------------------ -------- PK_EMP P EMP ENABLED SYS_C0011531 R EMP ENABLED PK_T1 P T1 DISABLED --제약조건 PK_EMP이 적용된(EMP TABLE의 PK) 칼럼을 조회하시오. SQL> column constraint_name format a10 SQL> select CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME, POSITION from user_cons_columns where constraint_name = 'PK_EMP'; CONSTRAINT TABLE_NAME COLUMN_NAM POSITION ---------- ------------------------------ ---------- ---------- PK_EMP EMP EMPNO 1