Day - 34 [Maria DB] Reference (relation)

Reference 참조(relation)

• RDMS 의 R 은 Relation 의 약자 이다.
• 그래서 테이블 간에  "부모-자식" 관계 설정을 할 수 있다.
• o : 0개 , ㅣ : 1개 , ㅌ : 1개 이상
• 1:1 관계 / 1(없어도됨):1 관계 / n(없어도됨):1 관계 / n(있어야됨):1 관계 / n:n 관계

 

• 이때 연결되는 선은 실선과 점선에 따라 의미가 달라진다.
• 실선 : 부모의 primary key 를 자식이 primary key 로 사용 (O) => 식별 관계
• 점선 : 부모의 primary key 를 자식이 primary key 로 사용 (X) => 비식별 관계
• 아래 내용에서 학생 테이블은 과목과 동아리 테이블의 부모 테이블이 된다.

---

1) 참조 제약 조건 : FORIGN KEY(외래키)

다른(외부) 테이블의 PK를 가져와(참조) 사용한다.

 

1) 만들면서 설정

 

-- 부모

create table parent_table(
	user_id varchar(30) primary key
	,user_name varchar(20)
	,user_phone varchar(20)
	,user_addr varchar(20)
);
desc parent_table;

 

-- 자식

create table child_table(
	order_id int(10)
	,user_id varchar(30)
	,product_name varchar(20)
	,price int(10)
	,qty int(5)
	-- ,foreign key(user_id) references parent_table(user_id)
);
desc child_table;

 

2) 이미 만들어진 테이블에 추가

-- ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건 타입] (컬럼) references [참조테이블] (참조컬럼)

-- ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건 타입] (컬럼) references [참조테이블] (참조컬럼)

alter table child_table add constraint foreign key (user_id) references parent_table(user_id);

-- parent_table 의 PK 인 user_id 를 child_table 에서 가져다 쓰는 경우를 '비식별' 관계라 한다.
-- iden_table 은 parent_table 의 PK를 자신의 PK 로 사용할 것 이다. 이 경우 우리는 '식별' 관계라 한다.

create table iden_table(
	user_id varchar(30) primary key
	,user_number varchar(20)
	,foreign key (user_id) references parent_table(user_id)
);

 

2. 연계, 참조, 무결성 제약조건

부모 자식간(연계, 참조) 에 논리적으로 말이 되게 만들어주는(무결성) 제약 조건

부모가 없는데 자식이 있는건 말이 안된다. -> 부모가 없으면 자식도 없게 만든다.

 

-- 부모

create table supplier(
	supplier_id int(10) primary key
	,supplier_name varchar(50) not null
	,phone varchar(12)
);

 

-- 자식

-- on delete cascade : 부모가 지워지면 자식도 같이 지워진다. table 에 선언.

drop table products;

create table products(
	product_id int(10) primary key
	,supplier_id int(10)
	,product_price int(10)
	,foreign key(supplier_id) references supplier(supplier_id) on delete cascade
);

-- 부모데이터
insert into supplier (supplier_id,supplier_name,phone) values(1,'김철수','02-123-1234');
insert into supplier (supplier_id,supplier_name,phone) values(2,'홍길동','032-568-0078');
insert into supplier (supplier_id,supplier_name,phone) values(3,'박영수','042-323-3234');
select * from supplier;
-- 자식데이터
insert into products (product_id,supplier_id,product_price)values(1111,1,6000);
-- 컬럼의 순서와 동일하고 모든 컬럼에 값을 넣을 경우 컬럼명 생략 가능(비추) - 테스트 용도로만 사용 할 것
insert into products values(1112,2,7000);
insert into products values(1113,3,8000);
-- 4번 supplier_id 는 부모에 없는 값이다. 그러므로 추가 불가능
insert into products values(1114,4,9000);

-- 자식이 있는 부모를 지우자
-- Cannot delete or update a parent row
-- 자식있는 부모가 지워지면 부모없는 자식이 발생하므로 연계 참조 무결성이 위배된다.
-- 이걸 위배하지 않기 위해서는 부모를 참조한 자식들의 데이터를 하나하나 쫓아다니면서 지워야 한다.
-- 이건 너무 힘든 작업이기 때문에 연계참조 무결성 제약조건을 활용 한다.
delete from supplier where supplier_id = 1;
select * from supplier;
select * from products;

위에 cascade

 

3. Unique 제약 조건

• 중복을 허용하지 않는 제약 조건
• unique + not null = primary key
• PK 와의 차이점 1) null 을 허용한다. 2) 테이블에 여러개 둘 수 있다.
• -- ALTER TABLE [테이블 명] ADD CONSTRAINT [제약조건타입] (적용 컬럼)

-- ALTER TABLE [테이블 명] ADD CONSTRAINT [제약조건타입] (적용 컬럼)
alter table supplier add constraint unique(supplier_name);
-- Duplicate entry '박영수' for key 'supplier_name'
insert into supplier values(4,'박영수','02-123-1234');
desc supplier;

 

4. Check 제약 조건(조건에 맞지 않으면 데이터를 받지 않는다.)

• -- ALTER TABLE [테이블 명] ADD CONSTRAINT [제약조건타입] (적용 컬럼 조건)
• check 는 제약조건에 대한 표시가 없다.
• 조건이 바뀌었을 경우 제약조건을 추가해줘야 한다는 단점이 있어서 사용권장하지 않는다.

-- ALTER TABLE [테이블 명] ADD CONSTRAINT [제약조건타입] (적용 컬럼 조건)

alter table products add constraint check(product_price between 5000 and 10000);
desc products;
insert into products values(1114,2,9000);
-- 조건에 맞지 않으면 들어가지 않는다.
insert into products values(1115,2,11000);
-- 1. 제약조건에 대한 표시가 없다.(조건을 알아내기도 어렵다.)
select * from information_schema.table_constraints where TABLE_NAME = 'products';
-- 2. 조건이 바뀌었을 경우 제약조건을 추가해 줘야 한다.
-- 이럴땐 check 제약조건이 걸려있나 의심하라고 알려주는것임. 사용하라는것이 x