Oracle constraint(제약조건)

/*//// 생성한 salesman으로 접속하여 실행 ////*/
-- 테이블 생성하기 --

--- 1 : 다 관계의 테이블에서 먼저 1(부모) 테이블을 생성해야한다.
drop table buseo;

create table buseo
(buno number(3),
buname varchar2(20) constraint buseo_buname_nn not null
-- 컬럼레벨 constraint : 컬럼안에 주어지는 제약조건을 말한다.
-- 그냥 not null 이라해도 되지만 차후 유지 관리를 위해서 이름을 지어서 not null을 해준다.
,addr varchar2(100)
,constraint buseo_buno_pk primary key(buno)); -- constraint 제약 조건
-- Table level Constraint : 컬럼다음에 오는 제약조건을 말한다.

-- constraint(제약조건)의 종류 --
-- 1. primary key
-- 2. unique
-- 3. foreign key
-- 4. check
-- 5. not null

----- 제약조건을 조회한다. -----
select *
from user_constraints;

----- buseo 테이블에 데이터입력 -----
insert into buseo values(101,'총무부','서울');
insert into buseo values(102,'연구부','서울');
insert into buseo values(103,'영업부','수원');
insert into buseo values(104,'생산부','인천');

select *
from buseo;
commit;
insert into buseo values(104,'생산부','인천'); -- buno는 이미 pk로 지정했기때문에 같은 번호가 올 수 없다.
insert into buseo values(105,null,'인천'); -- buname은 not null로 지정했기 때문에 null값이 될수가 없다.
insert into buseo values(105,'인사부',null); -- addr는 null 값이 가능하다.
rollback;
insert into buseo values(105,'인사부'); -- 컬럼수가 맞지않아서 자료추가가 안된다.
insert into buseo(buno,buname) values(105,'인사부');
      -- 이와같이 두개의 컬럼만 지정하면 두개컬럼값만 지정할 수 있다.
rollback;

--- 사원 테이블을 생성하자 (자식테이블)
create table sawon
(
sano number(4) constraint sawon_sano_nn not null,
      -- pk로 지정하면 굳이 not null을 안해도 되지만 해주는것을 권장한다.
      -- 테이블을 복사하면 not null제약만을 제외하고
                -- 나머지 모든 제약조건과 default 값은 복사가 안 되어진다.
                -- 그래서 not null을 붙여주는것을 권장한다.
saname varchar2(10) constraint sawon_saname_nn not null,
jubun varchar2(13) constraint sawon_jubun_nn not null,
jik varchar2(10) default '사원', -- default 값을 넣어주면 값이 지정되지 않으면 해당 default값이 된다.
sal number(4),
comm number(4),
hiredate varchar2(10) default to_char(sysdate,'yyyy-mm-dd'),
buno number(3), -- 부모테이블에서의 fk로 작용하기 때문에 부모테이블의 pk와 동일(number(4))해야한다.
mgr number(4), -- sawon 테이블의 sano의 fk로 작용하기때문에 sano와 동일하게 해야한다.
constraint sawon_sano_pk primary key(sano),
constraint sawon_jubun_uk unique(jubun),
       -- unique key는 자동 not null이 되지 않기 때문에 컬럼생성시 not null 조건을 넣어야한다.
constraint sawon_jubun_ck check(to_number(jubun) > 0 and
                 substr(jubun,7,1) in ('1','2','3','4') and length(jubun)=13),
constraint sawon_jik_ck check(jik in ('사장','부장','과장','대리','사원')),
constraint sawon_sal_ck check(sal between 1000 and 5000 and sal > comm),
constraint sawon_comm_ck check(comm between 0 and 2000),
constraint sawon_hiredate_ck check(extract(year from to_date(hiredate,'yyyy-mm-dd')) >= 1980),
constraint sawon_buno_fk foreign key(buno) references buseo(buno),
       -- references에서 정해준 테이블의 컬럼값을 현재테이블 컬럼에 fk로 지정한다.
constraint sawon_mgr_fk foreign key(mgr) references sawon(sano)
);

select *
from sawon

insert into sawon values
(1001,'백승만','7910121234567','사장',5000,1000,'1980-01-05',101,null);

insert into sawon values
(1002,'윤봉길','8102201234567',default,3000,default,default,103,1001);

insert into sawon(sano,saname,jubun,sal,buno,mgr) values
(1003,'엄정화','7410102234567',2000,103,1001);
--/// 테이블네임다음에 컬럼명을 명시해 주지 않으면 모든 컬럼값을 넣어줘야 하는데
--    해당 컬럼값에 default를 명시해 주었으면 default값이 들어오고
--    모든 컬럼값을 넣기 힘들면 원하는 컬럼만 정해주고 넣어도 된다.

--|| default로 정의되어진 값을 변경하고자 한다.
select column_name, data_default
from user_tab_cols
where table_name='SAWON';  -- 사원테이블의 각 컬럼별 default값을 보여준다.
 
alter table sawon       -- alter table 테이블명 modify 컬럼명 default 원하는값;
modify comm default 0;  -- 위와같이 실행하면 해당컬럼의 default값이 변경된다.

---- 생성되어진 sawon테이블의 재약조건을 조회 ----
select *
from user_constraints
where table_name='SAWON'

select *
from user_cons_columns
where table_name='SAWON' -- 컬럼의 제약조건을 보여준다.

select B.constraint_name, A.column_name,B.constraint_type, B.search_condition, B.status
from user_cons_columns A join user_constraints B
on A.constraint_name = B.constraint_name
where A.table_name = 'SAWON';  -- 해당 테이블의 제약조건 리스트를 보여준다.

---- 제약조건을 제거하거나 수정하기 ----
--//. 제약조건을 수정하려면 먼저 해당 제약조건을 제거하고 새로이 추가해야한다.
alter table sawon
drop constraint sawon_jik_ck; -- 원하는 제약조건 삭제

alter table sawon
add constraint sawon_jik_ck check(jik in('사장','부장','과장','대리','주임','사원')); --제약조건추가

---- not null 제약 제거 및 추가하기 ----
alter table sawon
drop constraint sawon_saname_nn; -- not null 제약을 삭제함으로 제약조건 삭제
---- 또는
alter table sawon
modify saname null; -- null 값을 허용해서 not null 제약을 없앤다.
--- 다시 추가하기
alter table sawon
add constraint sawon_saname_nn saname not null;  -- 오류 문법이다.

alter table sawon
modify saname constraint sawon_saname_nn not null; -- not null 제약은 항시 이렇게 추가해야한다.

----- 제약조건의 활성화/비활성화 -----
--1. 비활성화
alter table sawon
disable constraint sawon_saname_nn; -- 해당 제약조건을 비활성화 한다. (존재는 하나 사용 안하게된다.)
--2. 활성화
alter table sawon
enable constraint sawon_saname_nn; -- 해당 제약조건을 다시 활성화 시킨다.

alter table buseo
disable constraint buseo_buno_pk; -- 해당 문법은 실행이 되지 않는다.

select B.constraint_name, A.column_name,B.constraint_type, B.search_condition, B.status
from user_cons_columns A join user_constraints B
on A.constraint_name = B.constraint_name
where A.table_name = 'BUSEO';

select *
from user_constraints
where table_name = 'SAWON' and constraint_type='R';

select *
from user_cons_columns
where constraint_name = 'BUSEO_BUNO_PK'
------ PK 제약조건은 FK와 참조되어지기 때문에 그냥은 지워지지 않는다.
alter table buseo
disable constraint buseo_buno_pk cascade; -- cascade를 해주면 PK,FK둘다 비활성화가 되어진다.

alter table buseo
enable constraint buseo_buno_pk; -- cascade를 넣지않고 enable 하면 다시 활성화 되어진다.
alter table sawon
enable constraint sawon_buno_fk; -- 부모테이블에 pk를 먼저 활성화 한뒤 자식테이블의 fk를 활성화 한다.