제약 조건 (CONSTRAINTS)

제약조건 (CONSTRAINTS)

 

테이블에 추가, 변경, 삭제되는 데이터를 제한하기 위해 사용.

항목을 생성할 때, 'ADD constraint 제약조건_명(해당_컬럼_명) REFERENCE 타겟_테이블_명(타겟_컬럼_명)' 으로 입력한다.

Oracle에서 이런 제약조건을 KEY를 통해 수행하며, KEY의 요건을 만족하는 데이터만 Column에 저장하고, 그 이외에는 받아들이지 않는다.

제약조건은 데이터의 결함을 사전에 방지하고자 할 때, 사용된다.

 

제약조건 종류

USER_CONSTRAINTS   // 사용자가 설정 한, 제약조건

ALL_CONSTRAINTS

DBA_CONSTRAINTS

USER_CONS_COLUMNS
ALL_CONS_COLUMNS
DBA_CONS_COLUMNS

관리자가 통제할 수 없는 Oracle이 알아서 통제하는 Data Dictionary에 저장.

제약조건을 주면서 동시에 테이블 생성가능.

 

P(primary key : 테이블에서 유일하고, not null 조건을 동시에 충족하는 항목. 주로 하나.)

 // NOT NULL, UNIQUE 조건을 모두 만족해야하는 제약.

 // PK는 employee_id, department_id 와 같이 유일하게 테이블을 식별하게 해주는 컬럼_명으로 지정한다.

R(reference key : 다른 테이블과 관계를 가지고 있는 항목. 여러개 있을 수 있음. - foreign key)

 // 신입사원이 있을 때, 현존하지 않는 부서에 배치되면, 데이터에 결함이 있게 되므로

// employees 테이블의 department_id에 foreign key를 설정하여

 // 사원 정보 입력/변경 시, departments 테이블의 primary key인 department_id를 참조하여 거기에 존재하는 값일 때만 입력/변경을 허용

C(check : 데이터 입력 시, 반드시 지켜져야 하는 조건. email- ought to have @, salary > 0, ...)

U(unique : 데이터 내에서 유일해야 하는 조건. email should be unique, ID number, not cloned)

 

 

제약조건 생성

create table supplier (
supplier_id number(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(25),
constraint supplier_pk PRIMARY KEY(supplier_id)
);
// table 생성과 동시에 제약조건.

create table products (
product_id number(10) not null primary key,
supplier_id number(10) not null,
constraint fk_supplier FOREIGN KEY(supplier_id)
REFERENCES supplier(supplier_id)
on delete cascade);
// table 생성과 동시에 제약조건2.
/*
supplier table 의 supplier_id를 참조(references) - REFERENCES supplier(supplier_id)
JOIN을 하기 위해 FOREIGN KEY(supplier_id)

foreign_key_name 은 한 user 안에 중복이 안된다
foreign key 는 보통 join을 위해 중복 데이터(supplier_id)를 만듦

on delete cascade 참조되는 부모(supplier)값이 삭제되면 연쇄적으로 자식 테이블의 값 삭제.
on delete set null 참조되는 부모(supplier)값이 삭제되면 참조하는 자식 테이블의 값 NULL로 설정
보통 cascade
*/

create table empp ( emp_id number (6) primary key,// constraint  
frist_name varchar2(20),
last_name varchar2(20) constraint new_empp_lname_nn NOT NULL,// not null constraint 부여하며 이름 지정
email varchar2(25) constraint new_empp_email_u UNIQUE, // 이름 지정할 때, constraint 작성 후 이름.
phone varchar2(20),
hired DATE DEFAULT SYSDATE constraint new_empp_hiewd_nn NOT NULL,
// 설정 시, DEFAULT(기본값)과 SYSDATE(작성당일)를 주지 않으면 입력 한 날짜로 된다.
job_id varchar2(10) constraint new_empp_jobid_nn NOT NULL,
salary number (8, 2) constraint new_empp_sal_min CHECK(salary > 0),
manager_id number(6),
depart_id number(4));
);

제약조건 추가 및 삭제

 

alter table emp1
modify email unique;

alter table customers
add constraint cust_email_addr
check (REGEXP_LIKE(cust_email,'@')) NOVALIDATE;
// 정규식 표현을 이용한 제약조건
// cust_email_addr 에 @가 있어야 한다는 제약조건
// NOVALIDATE : 이메일을 작성하게 된다면, check 해라

alter table employees
modify salary NOT NULL (OR salary >= 0);
// salary 값이 없으면 안된다. 그리고 양수여야 한다.

alter table customers
drop constraint CUST_EMAIL_CH;
// 제약조건 풀기. CUST_EMAIL_CH는 제약조건 걸 때, 작성한 명칭

select * from all_constraints
where table_name = 'CUSTOMERS'
// CUSTOMERS table의 모든 제약조건 확인. table_name 은 무조건 대문자로 해야한다.

alter table empp
ADD constraint new_empp_empid_pk primary key (emp_id)  <= primary key

alter table empp
ADD constraint new_empp_manid_nn NOT NULL(manager_id) <= NN constraints

alter table empp
ADD constraint new_empp_manid_fk  <= FK constraints
FOREIGN KEY(manager_id)
REFERENCES depts(emp_id);     
// constraint, foreign key, reference 각각 작성 시, 중간에 ','  쉼표를 찍지 않아도 된다.

select * from tabs; // 테이블 데이터 확인

desc department; // 구조 확인

select * from all_constraints
where table_name = 'EMPP'; // 테이블의 모든 제약 조건 확인

 

desc USER_CONSTRAINTS   // 사용자가 아닌, 오라클이 알아서 만든 제약

desc USER_CONS_COLUMNS // column 에  설정된 제약조건 / 사용자가 설정한.

desc ALL_CONSTRAINTS   // user 가 설정한 모든 제약조건이 나옴.

 

 

JOIN

/*
desc USER_CONSTRAIN
desc USER_CONS_COLUMNS
desc ALL_CONSTRAINTS
*/

select column_name, uc.constraint_name, constraint_type, search_condition
from USER_CONSTRAINTS uc JOIN USER_CONS_COLUMNS ucc
ON uc.table_name = ucc.table_name
where uc.table_name = 'EMPLOYEES';
// uc와 ucc는 USER_CONSTRAINTS uc JOIN USER_CONS_COLUMNS ucc 이렇게 작성함으로써,
// USER_CONSTRAINTS, JOIN USER_CONS_COLUMNS의 줄임말로 만들어버림.
// join으로 USER_CONSTRAINTS, JOIN USER_CONS_COLUMNS를 같이 나오게 해주고,
// 기준을 uc.table_name = ucc.table_name 이것으로 둠.
// uc.table_name = 'EMPLOYEES'; 에서 가져옴

select * from all_constraints
where table_name = 'EMPLOYEES'
// employees table 의 제약조건을 확인한다.

 

INDEX 

 검색 속도 향상을 위해 사용하며, primary key 와 unique key 는 index자동 생성.

 일정 수준 이상의 크기가 아닌 BD라면, INDEX 사용이 속도 향상에 도움되지 않을 수 있다.