제약조건(constraint) 생성, 수정, 삭제 실습 (cascade)

--제약조건 생성

create table employees2(

    employee_id number(6),

    last_name varchar2(25) not null,

    salary number(8,2),

    commission_pct number(2,2),

    hire_date date constraint emp_hire_date_nn not null

    );

 

------------------------------------------------------

--제약조건 확인(테이블 & 칼럼)

select *

from user_constraints c1, user_cons_columns c2

where c1.table_name = c2.table_name

and c1.table_name = upper('departments2');

 

------------------------------------------------------

 

create table departments3 (

    department_id number(4) unique,

    department_name varchar2(30),

    manager_id number(6),

    location_id number(4),

    constraint department3_department_name_uk unique(department_name)

    );

 

 

create table departments4 (

-- 열단위 제약 (이름 없음)

    department_id number(4) primary key,

    department_name varchar2(30),

    manager_id number(6),

    location_id number(4),

--테이블 단위 제약(이름 있음)

    constraint department4_department_name_pk primary key(department_name)

    )

 

 

 

---------------------------------

create table employees2 (

    employee_id number(6),

    department_id number(4) constraint employees2_department_id_fk

    references departments(department_id)

    );

-- forein key는 primary key를 참조 생성 : primary key에 존재하는 값만 넣을 수 있음

 

---------------------------------

--제약조건 확인

select table_name ,constraint_name, constraint_type, status

from user_constraints

where lower(table_name) in('employees2', 'departments2')

order by table_name;

 

-- departments2  : department_id에 primary key(식별자) 생성

alter table departments2

add constraint departments2_department_id_pk primary key(department_id);

 

--제약조건은 "수정"이 불가능 : 삭제(drop)하고 새로 생성해야

alter table employees2

drop constraint employees2_department_id_fk;함

 

-- employees2  : department_id 에 foreign key(외래키) 생성 - on delete cascade

alter table employees2

add constraint employees2_department_id_fk foreign key(department_id)

references departments2(department_id) on delete cascade;

 

--제약조건 삭제시 옵션주기

alter table departments2

drop constraint DEPARTMENTS2_DEPARTMENT_ID_PK cascade;

 

 

--제약조건은 수정할 수 없음!!!

--create 할 때 제약조건 생성 하거나

--create table 후에 alter table을 통해 추가 or 삭제!