[Oracle] 2020.12.22. day_42 제약사항_foreign key(외래키, 참조키), unique

◎ 제약사항

	제약사항
	개발자(DBA : DataBase Administrator)가 원하는 값만 받기위해 테이블에 설정하는 것
종류	primary key, foreign key, unique, check, not null // default (제약사항은 아님)
	테이블에 설정된 제약사항은 user_constraints (DD) 테이블에서 확인할 수 있다 DD : DataBase Dictionary 의 줄임말, 정보저장용 테이블(DBMS에서 사용하는)
	테이블단위 제약사항과 컬럼단위 제약사항 alter문으로 설정할 수 있다
테이블단위 제약사항 :	테이블 생성시 컬럼의 정의가 종료되고 나서 정의하는 제약사항 (table level constraint)
컬럼단위 제약사항 :	테이블 생성시 컬럼의 정의 뒤에 붙여서 정의하는 제약사항 (column level constraint)
alter문 :	문법이따로 있는것이 아니라 기존에 있던 쿼리를 alter에 넣고 쓴다고 생각하면 된다
	제약사항은 alter문을 사용하여 활성화, 비활성화를 수행할 수 있다

---

2. foreign key( 외래키, 참조키 )

	foreign key (외래키, 참조키)
	다른 테이블 (부모테이블) 의 컬럼 값과 동일한 값으로 컬럼값이 저장되어야할 때
	다른 테이블에 일반컬럼은 foreign key로 참조할 수 없다
	null을 허용한다
	값이 입력될 때에는 부모테이블의 컬럼에 존재하는 값으로만 추가된다
	foreign key가 설정된 테이블을 자식 테이블이라고 한다
	하나의 테이블은 여러개의 foreign key를 가질 수 있다
	부모테이블의 primary key 컬럼만 참조가능
	부모테이블의 레코드를 참조하는 자식테이블이 존재한다면,  자식테이블의 레코드가 삭제된 후 무보테이블의 레코드가 삭제될 수 있다
	테이블단위 제약사항문법, 컬럼단위 제약사항문법 둘다 사용가능
문법 1. 테이블단위 제약사항 문법	create table 테이블명(   컬럼명 데이터형(크기),   ,,,,   constraint 제약사항명 foreign key(적용컬럼명)    references 부모테이블명(참조할 컬럼명) );
문법 2. 컬럼단위 제약사항문법	create table 테이블명(   컬럼명 데이터형(크기) constraint 제약사항명 references 부모테이블명(참조할 컬럼명),   ,,,, );
데이터(레코드) 삭제하기
1.  자식레코드 -> 부모레코드 순서로 삭제하기
2. on delete cascade
	부모 테이블의 레코드가 삭제되면 자식테이블 레코드를 자동으로 삭제한다
문법	create table 테이블명(   컬럼명 데이터형(크기)   constraint 제약사항명 foreign key (적용컬럼명)   references 부모테이블명(참조컬럼명) on delete cascade );

---

2. foreign key( 외래키, 참조키 ) 연습해보기

① 테이블 단위 제약사항

primary_table table

primary_table 테이블의 ssn컬럼을 참조키로 설정

1 2 3 4 5

create table foreign_table(     ssn char(14),     age number(3),     constraint fk_ssn foreign key(ssn) references primary_table(ssn) ); Colored by Color Scripter

cs

user_constraints table

 

② 컬럼 단위 제약사항

primary_column table

primary_column 테이블의 id컬럼을 참조하는 자식 테이블 생성  자식 테이블의 컬럼명은 부모테이블의 컬럼명과 같지 않아도 된다

1 2 3 4 5 6

create table foreign_column(     f_id varchar2(20) constraint fk_f_id references primary_column(id),     email varchar2(50),     age number(3),     input_date date ); Colored by Color Scripter

cs

user_constraints table

※ 값 추가 해보기 __성공

primary_column table

값 추가해보기 -추가 성공 1. 부모테이블에 존재하는 값으로 컬럼값을 추가할 때 2. 부모테이블에 존재하는 값이라면 몇개든 추가할 수 있다 3. null을 추가하는 경우

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

--1. 부모테이블에 존재하는 값으로 컬럼값을 추가할 때 insert into foreign_column(f_id, email, age, input_date) values('kim', 'kim@test.com', 20, sysdate);   --2. 부모테이블에 존재하는 값이라면 몇개든 추가할 수 있다 insert into foreign_column(f_id, email, age, input_date) values('kim', 'kim1111@test.com', 25, sysdate);   insert into foreign_column(f_id, email, age, input_date) values('lee', 'lee@test.com', 21, sysdate);   --3. null을 추가하는 경우 insert into foreign_column(email, age, input_date) values('null@test.com', 21, sysdate);   insert into foreign_column(email, age, input_date) values('null1@test.com', 21, sysdate);

cs

foreign_column table

 

※ 값 추가 해보기 __실패

 

※ 값 삭제하기

레코드 삭제 --foreign key 제약사항에 on delete cascade가 사용되지 않았다면 --자식테이블의 레코드를 삭제한 후 부모테이블의 레코드를 삭제할 수 있다

primary_column (부모)

foreign_column table (자식)

부모테이블에서 참조하고 있지 않은 레코드 삭제 ==> 바로 지워진다

delete from primary_column where id = 'test';

※ 주의해서 삭제할 것

부모테이블에서 참조하고 있는 자식레코드가 존재할 때 삭제

delete from primary_column where id = 'kim';

 

※ 참조를 하고 있는 레코드를 삭제하는 방법

1. 자식 -> 부모 레코드 순서로 삭제하기

--1. 자식레코드를 삭제
delete from foreign_column where f_id = 'lee';

자식테이블

--2. 부모레코드를 삭제
delete from primary_column where id = 'lee';

부모테이블(참조테이블)

2. 부모테이블에 레코드가 삭제되면 자동으로 자식 테이블의 모든 레코드를 동시에 삭제

on delete cascade 사용

on delete cascade가 설정되어 있어, 부모테이블의 레코드를 삭제하면 
참조하고 있는 모든 자식테이블의 레코드가 삭제된다

1. 부모테이블을 생성할 때, on delete cascade를 설정해준다

create table foreign_table(
    ssn char(14),
    age number(3),
--    constraint fk_ssn foreign key(ssn) references primary_table(ssn)
    constraint fk_ssn foreign key(ssn) references primary_table(ssn)
    on delete cascade
);

2. 값을 입력 (자식도 따로 해준다)

insert into foreign_table(ssn, age)
values('880101-1234567', 20);
 
insert into foreign_table(ssn, age)
values('880101-1234567', 21);
 
insert into foreign_table(ssn, age)
values('880101-1234568', 22);

3. 삭제

delete from primary_table where ssn = '880101-1234567';

 

 

---

 3. unique

	unique
	값이 없을 수도 있고, 있다면 유일해야할 때 == null을 허용하면서 값이 있다면 유일해야할 때
	테이블단위 제약사항, 컬럼단위 제약사항 모두 사용가능
	unique 제약은 하나의 테이블에 여러개를 설정할 수 있다
문법 1. 테이블단위	create table 테이블명(   컬럼명 데이터형(크기),   ,,,   ,,,, ,   constraint 제약사항명 unique(적용컬럼)  );
제약사항명 :	uk_컬럼명
문법  2. 컬럼단위	create table 테이블명(   컬럼명 데이터형(크기) unique ) //Oracle이 제약사항명을 자동으로 생성한다__권장하지 않는 문법 create table 테이블명(   컬럼명 데이터형(크기) constraint 제약사항명 unique )

 

 3. unique 연습해보기

① 테이블 단위 제약사항

create table unique_table(
    name varchar2(15),
    phone varchar2(13),
    constraint uk_phone unique(phone)
 
);

 

 

 

② 컬럼 단위 제약사항

테이블 생성

create table unique_column(
    name varchar2(30),
    age number(3),
    email varchar2(50) constraint uk_email unique,
    addr varchar2(100)
 
);

※ 값입력 성공하는 경우값ㅇ입력 성공하는 경우

1. 최초데이터 입력

insert into unique_column(name, age, email, addr)
values ('김ㅇ규', 25, 'kim@test.com', '서울시 마포구');

2. 이메일이 다른 경우

insert into unique_column(name, age, email, addr)
values ('김ㅇ규', 25, 'lee@test.com', '서울시 마포구');

3. null이 입력되는 경우 : null은 중복이 가능하다

insert into unique_column(name, age, email, addr)
values ('박권익', 25, '', '서울시 용산구');
 
insert into unique_column(name, age, email, addr)
values ('박권익', 25, '', '서울시 용산구');

실패하는 경우