[SQL] 오라클 테이블 작성 고급

- 테이블 작성 고급
- 무결성
- 기본키 생성 예제
create table test1
(
id number(4) primary key not null
,name varchar2(100)
,price number(8)
)

desc user_constraints

select constraint_name, table_name, constraint_type
from user_constraints;

create table test2
(
id number(4)
constraint pk_test2_id primary key not null
,name varchar2(100)
,price number(8)
)

desc user_constraints

select constraint_name, table_name, constraint_type
from user_constraints;

- 두개의 필드를 한개의 기본키 작성
create table test3
(
gubun number(4) not null
,code char(4) not null
,name varchar2(100) not null
,constraint pk_test3_key primary key (gubun, code)
)

desc user_constraints

select constraint_name, table_name, constraint_type
from user_constraints;

-이미 작성된 테이블에 기본키 작성
create table test4
(
id number(4) not null
,name varchar2(100)
,price number(8)
);

select constraint_name, table_name, constraint_type
from user_constraints;

alter table test4
add constraint pk_test4_key primary key (id);

select constraint_name, table_name, constraint_type from user_constraints

- 기본키 등의 제약 조건 삭제
alter table test4
drop primary key;

select constraint_name, table_name, constraint_type
from user_constraints

- unique key
create table test5
(
id number(4) null constraint uk_test_id unique
,name varchar2(100)
,score number(8)
);

select constraint_name, table_name, constraint_type
from user_constraints

insert into test5(name, score) values ('java', 80)
insert into test5(name, score) values ('jsp', 80)
insert into test5(id, name, score) values (1,'ejb', 80)
insert into test5(id, name, score) values (1,'oracle', 80) -> 오류

- check 컬럼에서 허용가능한 데이터의 범위나 조건 지정
alter table test5
add
(constraint ck_teat5_score check(score between 0 and 100))

select constraint_name, table_name, constraint_type
from user_constraints

insert into test5(id, name, score) values (2,'oracle', 80)
insert into test5(id, name, score) values (3,'oracle', 180) -> 오류

- foreign key
1)부서테이블 작성
create table dept1
(
code char(5) not null
,name char(30) not null
,constraint pk_dept1_code primary key (code)
);

2)사원테이블 작성
create table emp1
(
id number(4) not null
constraint pk_emp1_id primary key
,name char(10) not null
,code char(5) not null
)

3)사원테이블 데이터 입력
insert into emp1 (id, name, code) values (1, '지순', '10001')

4)참조키 작성 foreign key
alter table emp1
add (constraint fk_dept1_code foreign key (code)
references dept1 (code))
-> 오류 : 자식테이블(emp1)이 먼저 작성되어 부모테이블(dept1)에 참조키가 없음

5)부서 코드에 자료 입력
insert into dept1 (code, name) values ('10001', '영업')

6)다시 참조키 작성
alter table emp1
add (constraint fk_dept1_code foreign key (code)
references dept1 (code))
-> 성공
select constraint_name, table_name, constraint_type
from user_constraints
-> 확인

7)참조키를 위반하는 값 입력
insert into emp1 (id, name, code) values (2, '규창', '10002')
-> 오류

8) 부서 테이블에 먼저 자료 입력후 입력
insert into dept1 (code, name) values ('10002', '기획')
insert into emp1 (id, name, code) values (2, '규창', '10002')

9)부서 테이블에 자료 입력
insert into dept1 (code, name) values ('10003', '우리')

10)참조키가 자식과 걸려 있을때 참조 당하는 컬럼은 nc속성을 가짐
update dept1 set code='20002' where code='10002' -> 오류

11)참조키가 걸려 있어도 창조된 테이블에 존재하지 않은 행은 수정 가능
update dept1 set code='20003' where code='10003'

12)참조당하는 테이블 삭제
drop table dept1 -> 오류

13)참조키 삭제 후 부서 테이블 삭제 가능
alter table emp1 drop constraint fk_dept1_code
drop table dept1

- on delete cascade 외래 키
1)테이블 작성 외래키 이용
create table testA
(
a_id number(4) constraint pk_testA_id primary key
,name varchar2(20)
);
create table testB
(
b_id number(4) constraint pk_testb_id primary key
,a_id number(4) constraint fk_testB_id
references testA (a_id)
on delete cascade
,score number(3)
)

2)자료입력
insert into testA (a_id, name) values(1, '홍길동');
insert into testA (a_id, name) values(2, '이순신');
insert into testA (a_id, name) values(3, '김말자');
insert into testA (a_id, name) values(4, '정정해');
ed aaa
@aaa

insert into testB (b_id, a_id, score) values(1, 1, 85);
insert into testB (b_id, a_id, score) values(2, 2, 90);
insert into testB (b_id, a_id, score) values(3, 1, 75);
insert into testB (b_id, a_id, score) values(4, 3, 80);
insert into testB (b_id, a_id, score) values(5, 4, 77);
ed aaa
@aaa

select * from testA
select * from testB

3) 자료 수정은 불가하느 자료는 삭제 가능 (단, 자식 테이블 자료도 삭제됨)
update testA set a_id=10 where a_id=1 -> 불가
delete testA where a_id=1 -> testB 테이블 자료도 삭제

select * from testA
select * from testB

4) 테이블과 그 테이블을 참조하는 foreign key의 제약조건도 동시에 삭제가능
drop table testA cascade constraints;
select * from testB
drop table testB

- not null 테이블에서 지정한 컬럼의 데이터가 null값을 갖지 못함

- constraint 의 비활성화
disalbe

- constraint 의 활성화
ensalbe

- default
create table test1
(
empno number(6) not null
,sal number(8,2)
,nowdate date default systimestamp
)
insert into test1 values(2222,40000,null)
insert into test1 values(2222,40000,'')
insert into test1 values(3333,40000,'07/04/04')

update test1 set
nowdate=default
where empno=1111

insert into test1 values(2222,40000,default)

delete from test1
where sal=40000 -> 행 지우기