220715 TIL

국비교육 33일차 Oracle : 제약조건 - 제약조건 이어서, 조회 방법, 추가/삭제/수정/변경

1. Constraint(제약조건)

1) Foreign Key(외래키) 제약 [R]

(1) on delete cascade

• 삭제하는 테이블의 대상이 부모레코드일 때 자식레코드부터 먼저 삭제한 다음에 부모의 레코드를 삭제하는 것이다.
• 테이블 생성 시
  constraint 제약명 foreign key(제약컬럼명) references 부모테이블명(식별자컬럼명) on delete cascade

(2) on delete set null

• on delete cascade를 통해서 쓰면 지우면 안되는 데이터까지 다 지우게 되기 때문에 데이터의 삭제가 아닌 null을 부여하는 방식이다.
• 테이블 생성 시
  constraint 제약명 foreign key(제약컬럼명) references 부모테이블명(식별자컬럼명) on delete set null

2) Check 제약 [C]

• 테이블 생성 시
  constraint 제약명 check(status in(0,1))

3) NOT NULL 제약

(1) not null 제약 제거

alter table 테이블명
modify 컬럼명 null;

(2) not null 제약 추가

alter table 테이블명
modify 컬럼명 not null;

4) 제약조건 조회하기

• 제약조건의 이름, 제약 타입, 테이블명 등

select *
from user_constraints
where table_name = '테이블명';

• 컬럼명과 제약조건의 이름 등

select *
from user_cons_columns
where table_name = '테이블명';

5) 테이블에 생성된 제약조건 조회하기

select A.constraint_name, A.constraint_type, A.search_condition, A.r_constraint_name, 
       A.status, A.index_name, B.column_name, B.position
from user_constraints A JOIN user_cons_columns B
on A.constraint_name = B.constraint_name
where A.table_name = '테이블명';

6) 테이블에 생성된 foreign key 제약조건 조회하기

select A.constraint_name, A.constraint_type, A.search_condition, A.r_constraint_name, 
       A.status, A.index_name, B.column_name, B.position
from user_constraints A JOIN user_cons_columns B
on A.constraint_name = B.constraint_name
where A.table_name = '테이블명' and constraint_type = 'R';
-- r_constraint_name 참조받는 부모테이블의 제약 조건
-- PK_TBL_NEW_MEMBER_MEMBER_ID 회원의 멤버아이디 테이블 참조

7) 제약조건 추가, 삭제, 수정

(1) 데이터 수정

-- 교체
alter table 테이블명
modify 컬럼명 null;

-- 삭제
delete from 테이블명
where 컬럼명= '값';

-- 업데이트
update 테이블명 set 컬럼명 = null
where 컬럼명 = '값';

• 테이블 삭제
  • 테이블을 drop 할 때도 자식테이블이 있는 경우 부모테이블을 drop 할 수가 없다. 그러므로 부모테이블과 자식테이블 모두 삭제할 경우에는 먼저 자식 테이블부터 drop을 하고 그 다음에 부모 테이블을 drop 해야한다.

8) 어떤 테이블에 제약조건을 추가하기

alter table 테이블명 add constraint 제약조건명 primary key(컬럼명);
alter table 테이블명 add constraint 제약조건명 unique(컬럼명);
alter table 테이블명 add constraint 제약조건명 check( ... );

alter table 테이블명 add constraint 제약조건명 foreign key(컬럼명) references 부모테이블명(식별자컬럼명);
alter table 테이블명 add constraint 제약조건명 foreign key(컬럼명) references 부모테이블명(식별자컬럼명) on delete casecade;
alter table 테이블명 add constraint 제약조건명 foreign key(컬럼명) references 부모테이블명(식별자컬럼명) on delete set null;

9) 제약조건명 변경하기

alter table 테이블명
rename constraint 현재사용중인제약조건명 to 새로운제약조건명;

10) 제약조건 삭제하기

alter table 테이블명 drop constraint 제약조건명;

-- NOT NULL 제약의 경우, 위의 방법과 아래 방법 모두 사용 가능
alter table 테이블명 modify 컬럼명 null;

-- 어떤 테이블에 primary key 제약조건을 삭제할 경우에는 위의 방법처럼 해도 되고, 또는 아래처럼 해도 된다.
alter table 테이블명 drop primary key;
-- primary key는 유일하기 때문이다. 

11) 제약조건의 내용 변경하기

• 제약조건의 내용 변경하는 명령어는 없다. 그러므로 해당 제약조건을 삭제하고 다시 새롭게 만들어야 한다.

alter table 테이블명 drop constraint 제약조건명;
-- 테이블명 이(가) 변경되었습니다.

alter table 테이블명 add constraint 제약조건명 check( 컬럼명 in (바꿀 내용) );
-- Table 테이블명 이(가) 변경되었습니다.

12) 어떤 테이블에 존재하는 제약조건을 비활성화 / 활성화 시키기

-- 비활성화
alter table 테이블명 disable constraint 제약조건명;

-- 활성화
alter table 테이블명 enable constraint 제약조건명;