[ORACLE]제약 조건

이번엔 제약조건에 대해서 알아볼 것 이다.

---

제약조건(Constraints) 
  

• 제약조건은 테이블에 추가, 변경, 삭제되는 데이터를 제한하기 위해서 사용된다. 
    제약조건 항목을 생성할 때에는 'ADD constraint 제약조건_명(해당_컬럼_명) REFERENCE 타겟_테이블_명(타겟_컬럼_명)'식으로 해준다. 
    예를 들어 사원번호라면 반드시 값이 할당되어져야 한다거나, 유일해야 한다거나, 
            이메일이라면 반드시 @가 들어 있어야 한다든가 식이다. 
    이는 데이터의 무결성을 위해서 반드시 필요하다. 
  Oracle에서는 이런 제약조건을 KEY를 통해서 수행하는데 KEY가 가지는 요건을 만족하는 데이터만 컬럼에 저장시키고 그 외에는 받아들이지 않게 된다. 제약조건은 데이터의 결함을 사전에 방지하고자 할 때 사용된다.
   
•  제약조건은 USER_CONSTRAINTS, ALL_CONSTRAINTS, DBA_CONSTRAINTS, 

USER_CONS_COLUMNS, ALL_CONS_COLUMNS, 그리고 DBA_CONS_COLUMNS가 있는데 이는 관리자가 통제할 수 없는 Oracle이 알아서 통제하는 Data Dictionary에 저장되어져 있다. 제약조건을 주면서 새로운 테이블을 생성할 수도 있다. 
=>제약조건은 P(primary key: 현재 테이블에서 유일하고 not null 조건을 동시에 충족하는 항목, 주로 하나임), 
R(reference key: 현재 테이블에서 다른 테이블과 관계를 가지고 있는 항목, 여러개 있을 수 있음 <-foreign key), C(check: 데이터 입력 시 반드시 지켜져야 하는 조건, email ought to have @, salary>0, ....), U(unique: 데이터 내에서 유일해야 하는 조건, email should be unique, not cloned)

  

• PRIMARY KEY와 FOREIGN KEY
    NOT NULL과 UNIQUE 조건을 모두 만족하는 제약이 Primary Key이다. 이 PK는 employees 테이블의 employee_id나 departments 테이블의 department_id와 같이 유일하게 테이블을 식별하게 해주는 컬럼_명으로 지정한다.
    하나의 테이블에는 보통 하나의 PK가 있고 여러 개의 FK가 있을 수 있다.
    employees 테이블에 저장된 컬럼 중에서 해당 사원의 부서번호를 저장하는 department_ id 컬럼이 있는데 이 컬럼에 입력되는 값은 현존 부서의 번호여야 한다. 신입사원이 있을 때 현존하지 않는 부서에 배치되면 데이터에 결함이 있게 되므로 employees 테이블의 department_id에 Foreign Key를 설정해서 employees 테이블에 사원 정보 입력/변경 시 departments 테이블의 Primary Key인 department_id를 참조해서 거기에 존재하는 값일 때만 입력/변경을 허용하게 된다. A table의 F.K는 B table의 P.K가 된다. <- 관계형 데이터베이스가 되고, 여러 테이블들을 조인 할 수 있는 근거가 된다.

 

 

메인 테이블에는 하나에 프라이머리 키와 여러개의 포링키를 가진다.
메인테이블과 다른 테이블과의 관계를 갖는다.
포린 키는 다른 테이블과 관계를 맺어야 해서 뒤에 쓸수 없지만 프라이 머리 키는 유일하니까 쓸수있다.

 

---

●예제

 

●USER_CONSTRAINTS
desc USER_CONSTRAINTS;

 

desc USER_CONS_COLUMNS;//사용자가 컬럼에 설정한 제약조건

 

desc ALL_CONSTRAINTS;

desc USER_CONSTRAINTS;
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';
이것도 가능 
select * from all_constraints
where table_name = 'EMPLOYEES';

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';

select * from all_constraints
where table_name = 'EMPLOYEES';

●제약조건 없이 만들고 나중에 제약조건 주기
create table emp1 (emp_id number(6), f_name varchar2(10), l_name varchar2(10),
email varchar2(25), phone varchar2(20), hired date default sysdate, 
job_id varchar2(10), salary number(8,2), commission number(4,2), manager_id number(6),
depart_id number(4));

제약조건이 없다.
select * from all_constraints
where table_name = 'EMP1';

create table emp1 (emp_id number(6), f_name varchar2(10), l_name varchar2(10),
email varchar2(25), phone varchar2(20), hired date default sysdate, 
job_id varchar2(10), salary number(8,2), commission number(4,2), manager_id number(6),
depart_id number(4));

select * from all_constraints
where table_name = 'EMP1';

●alter table emp1
modify l_name varchar2(20) NOT NULL;

alter table emp1
modify l_name varchar2(20) NOT NULL;

●insert into emp1(emp_id, l_name, email, hired) values('1001', 'Lee', 'econet@gmail.com', '2019/02/02');

insert into emp1(emp_id, l_name, email, hired) values('1001', 'Lee', 'econet@gmail.com', '2019/02/02');

●insert into emp1(emp_id, email, hired) values('1002', 'eco@gmail.com', '2020/02/02');
=l_name 이 없으면 데이터를 넣을수 없는 제약 조건

insert into emp1(emp_id, email, hired) values('1002', 'eco@gmail.com', '2020/02/02');

●insert into emp1(emp_id, l_name, hired, job_id) values('1002', 'hong', '2020/02/02', 'IT_man');

insert into emp1(emp_id, l_name, hired, job_id) values('1002', 'hong', '2020/02/02', 'IT_man');

●alter table emp1 
modify email unique; =유니크 제약 조건 주기

alter table emp1 
modify email unique;

●select * from all_constraints
where table_name = 'EMP1'; = 제약조건 확인

select * from all_constraints
where table_name = 'EMP1';

●insert into emp1 (emp_id, l_name, email) values('1003', 'kang', 'econet@gmail.com');

insert into emp1 (emp_id, l_name, email) values('1003', 'kang', 'econet@gmail.com');

●update emp1
set email = 'ecconet@gamil.com'
where emp_id = '1001';

update emp1
set email = 'ecconet@gamil.com'
where emp_id = '1001';

●employees 테이블에서 SA_REP인 Kumar 사원을 SA_MAN으로 직급을 올리면서 현재 급료의 1.5배를 받도록 설정 하시오.
/* select first_name, salary, job_id, last_name from employees1
where last_name = 'Kumar'; */
update employees1
set job_id = 'SA_MAN', salary = salary*1.5
where last_name = 'Kumar';
select first_name, salary, job_id, last_name from employees1
where last_name = 'Kumar';

select first_name, salary, job_id, last_name from employees1
where last_name = 'Kumar'; */
update employees1
set job_id = 'SA_MAN', salary = salary*1.5
where last_name = 'Kumar';
select first_name, salary, job_id, last_name from employees1
where last_name = 'Kumar';

/* select * from all_constraints
where table_name = 'EMPLOYEES'; 
 select * from all_constraints
where table_name = 'JOBS'; 
 select * from all_constraints
where table_name = 'DEPARTMENT'; */

select * from all_constraints
where table_name = 'EMPLOYEES'; 
 select * from all_constraints
where table_name = 'JOBS'; 
 select * from all_constraints
where table_name = 'DEPARTMENT';

●alter table customers
add constraint cust_email_addr
check (REGEXP_LIKE(cust_email, '@')) NOVALIDATE; //추가 체크 조건, 데이터 입력시

alter table customers
add constraint cust_email_addr
check (REGEXP_LIKE(cust_email, '@')) NOVALIDATE;

●select * from  all_constraints 
where table_name = 'CUSTOMERS'; 제약조건 확인

select * from  all_constraints 
where table_name = 'CUSTOMERS';

●insert into customers (customer_id, cust_first_name, cust_last_name, cust_email)
values ('9999', 'Hong', 'Kildong', 'mail.naver.com');

insert into customers (customer_id, cust_first_name, cust_last_name, cust_email)
values ('9999', 'Hong', 'Kildong', 'mail.naver.com');

●select * from customers
where customer_id = '9999';

select * from customers
where customer_id = '9999';

●alter table employees
modify salary NOT NULL (OR salary >= 0);

alter table employees
modify salary NOT NULL (OR salary >= 0);

●alter table customers
drop constraint CUST_EMAIL_CH;// 제약 조건 삭제

alter table customers
drop constraint CUST_EMAIL_CH;

●테이블 생성하면서 제약 조건 걸기
create table supplier(supplier_id number(10) not null,
suppplier_name varchar2(50) not null,
contact_name varchar2(25),
constraint supplier_pk PRIMARY KEY(supplier_id));

create table supplier(supplier_id number(10) not null,
suppplier_name varchar2(50) not null,
contact_name varchar2(25),
constraint supplier_pk PRIMARY KEY(supplier_id));

 비교
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); fk키를 삭제하면 관련된걸 삭제한다. 
원본이 없어지면 포링키 삭제한다.

reate 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);

 

●만들때 constrints 걸기
create table empp ( emp_id number(6) primary key,
first_name varchar2(20), 
last_name varchar2(20) constraint new_empp_lname_nn NOT NULL,
email varchar2(25) constraint new_empp_email_u UNIQUE,
phone varchar2(20),
hired DATE DEFAULT SYSDATE constraint new_empp_hired_nn NOT NULL,
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));

create table empp ( emp_id number(6) primary key,
first_name varchar2(20), 
last_name varchar2(20) constraint new_empp_lname_nn NOT NULL,
email varchar2(25) constraint new_empp_email_u UNIQUE,
phone varchar2(20),
hired DATE DEFAULT SYSDATE constraint new_empp_hired_nn NOT NULL,
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));

 

●테이블을 만든후 constraints 걸기 
alter table empp
add constraint new_empp_empid_pk primary key(emp_id), <= primary key
alter table empp
add constriant new_empp_manid_nn NOT NULL(manager_id), <=NN comstraints
alter table empp
add constraint new_empp_manid_fk <= FK constraints
FOREIGN KEY(manager_id)// 매니저 아이디를 포링키로
REFERENCES departments(emp_id); // 테이블이 없어서 불가능 코드

alter table empp
add constraint new_empp_empid_pk primary key(emp_id), <= primary key
alter table empp
add constriant new_empp_manid_nn NOT NULL(manager_id), <=NN comstraints
alter table empp
add constraint new_empp_manid_fk <= FK constraints
FOREIGN KEY(manager_id)// 매니저 아이디를 포링키로
REFERENCES departments(emp_id); // 테이블이 없어서 불가능 코드

alter table empp
add constraint new_empp_deptno_fk //제약조건 이름
foreign key(manager_id)                // empp테이블의manager_id를 fk로 만듦
references dapartment(deptno);      //department 테이블의 deptno를 참조하게 함

lter table empp
add constraint new_empp_deptno_fk //제약조건 이름
foreign key(manager_id)                // empp테이블의manager_id를 fk로 만듦
references dapartment(deptno);      //department 테이블의 deptno를 참조하게 함