02. Constraint과 Relation

1. CONSTRAINT (제약 조건)

 

 

 

-- 1) PRIMARY KEY (기본 키 제약조건)
-- 중복과 NULL 을 허용하지 않는다.
-- 테이블 종속적이다. (하나의 테이블에 하나의 PK)
-- 하나의 컬럼으로 하나의 PK 를 만들 수 있지만, 여러개의 컬럼을 합쳐서 만들 수도 있다. (최대 16개 까지 조합 가능)

-- 테이블 생성 시 함께 생성 (가장 많이 활용)
create table pk_test(
	first_col int(3) primary key,
	second_col varchar(4)
);
desc pk_test;

-- 테이블 생성 후 추가
desc employees;
select * from employees;
-- ALTER TABLE [테이블 명] ADD CONSTRAINT [제약조건 타입] (적용 필드);
alter table employees add constraint primary key (emp_no);
alter table employees add primary key (emp_no);

-- 일부러 중복 데이터 넣어보기
insert into employees (emp_no, first_name, family_name, email, mobile, salary)
	values(119, '항오', '조', 'search@daum.net', '01012341234', 4500000);

-- UPSERT - 중복되는 키 값이 있으면 수정, 없으면 삽입
insert into employees (emp_no, first_name, family_name, email, mobile, salary)
	values(112, '태근', '김', 'search@daum.net', '01012341234', 4500000)
on duplicate key update email = 'email@naver.com', salary = 99999999;

-- 복합키 (combination key)
-- 하나의 컬럼으로 하나의 기본키를 구성하는 것이 가장 좋으나, 어쩔 수 없는 경우 가수의 컬럼을 조합해서 기본키를 생성
-- 최대 16개 까지
-- 외래키를 이용해 복합키를 만드는 경우가 있는데, 이 경우 UPDATE 나 INSERT 에 제한을 주므로 사용하지 않는 것이 좋다.
create table pk_tow_test(
	first_col int(5),
	second_col varchar(10),
	third_col date,
	primary key (first_col, second_col)
);
desc pk_tow_test;

-- 2) NOT NULL (NULL 을 허용하지 않음)
-- NOT NULL 은 제약 조건이지만 속성으로 취급
-- 테이블 생성 후 NOT NULL 넣기
-- ALTER TABLE [테이블 명] MODIFY [컬럼 명] (사이즈);
alter table pk_test modify second_col varchar(4) not null;
desc pk_test;

-- 제약조건 확인
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'pk_tow_test';
-- 제약조건 삭제
-- ALTER TABLE [테이블 명] DROP [제약조건 종류];
alter table pk_tow_test drop primary key;

 

 

 

 

 

 

2. RDBMS (Relation Data Base Management System)

 

 

 

테이블 간에 '부모-자식' 관계 설정을 할 수 있다.

 

 

-- RDBMS (Relation Data Base Management System)
-- 관계형 데이터 베이스

-- 1. 참조 제약 조건 (외래키 제약 조건)
-- 1) 테이블 생성 시 추가
create table parant_table(
	user_id varchar(30) primary key,
	user_name varchar(20),
	user_phone varchar(20),
	user_addr varchar(100)
);

-- 부모의 기본 키를 외부에서 참조해 온다.
create table child_table(
	order_id int(10)
	,user_id varchar(30)
	,product_name varchar(30)
	,price int(10)
	,qty int(5)
	-- ,foreign key(user_id) references parant_table(user_id)
);

-- 2) 테이블 생성 후 추가
-- ALTER TABLE [테이블 명] ADD CONSTRAINT [제약 조건 종류](적용 필드) REFERENCES [가져올 테이블](가져올 컬럼);
alter table child_table add constraint foreign key(user_id) references parant_table(user_id);

select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'child_table';

/* 부모 자식 관계란 부모의 기본 키를 자식이 가져다 쓰는 관계이다.
 * 이때 부모의 기본 키를 나의 기본 키로 사용하는 경우를 '식별 관계' 라고 한다.
 * 그렇지 않을 경우는 '비식별 관계' 라고 한다.
 * */

-- 식별 관계
-- 식별 관계는 완벽한 1대1 관계에서 사용해야 한다.
create table iden_table (
	user_id varchar(30) primary key
	,foreign key(user_id) references parant_table(user_id)
);


-- 2. 연계 참조 무결성 제약 조건
-- 부모 자식 간에 논리적으로 앞 뒤 말이 맞아야 한다.

-- 모든 컬럼의 값을 넣을 경우 컬럼 명을 생략해도 된다.
insert into parant_table values('user01', '김철수', '01012341234', '서울 금천구');
insert into parant_table values('user02', '홍길동', '01012341234', '서울 금천구');
insert into parant_table values('user03', '박영수', '01012341234', '서울 금천구');

select * from parant_table;

insert into child_table values(1, 'user01', '의류', 6000, 1);
insert into child_table values(2, 'user02', '의류', 6000, 1);
insert into child_table values(3, 'user03', '의류', 6000, 1);

select * from child_table;

-- 부모한테 없는 값을 외래키에 넣으려고 하면 에러가 나타난다.
-- 연계참조 무결성이 깨졌다.
-- 부모테이블의 기본 키는 user04 가 없지만, 자식이 그것을 쓰려고 했다. -> 부모없는 자식을 만들려고 함
insert into child_table values(4, 'user04', '의류', 6000, 1);

-- 부모를 지워 보자
delete from parant_table where user_id = 'user01';

-- 연계 참조 무결성을 해치지 않고 삭제하려면 자식이 먼저 삭제되고, 이후 부모가 삭제되어야 한다.
-- 이 과정을 자동으로 수행해 주는 ON DELETE CASCADE 옵션이 있다.
create table supplier(
	sup_id int(10) primary key
	,sup_name varchar(50) not null
	,phone varchar(12)
);

create table products(
	pd_id int(10) primary key
	,sup_id int(10)
	,price int(10)
	,foreign key (sup_id) references supplier(sup_id) on delete cascade
);

insert into supplier values(1, '김철수', '01012341234');
insert into supplier values(2, '이철수', '01012341234');
insert into supplier values(3, '박철수', '01012341234');

insert into products values(1111, 1, 6000);
insert into products values(1112, 2, 6000);
insert into products values(1113, 3, 6000);

select * from supplier;
select * from products;

delete from supplier where sup_id = 1;


-- 2. unique 제약 조건
-- 중복을 허용하지 않는 제약 조건
-- 테이블에 다수 존재할 수 있다.
-- unique + not null = PK
-- 후보 키 - 기본 키가 될 수 있는 조건은 갖을 수 있으므로...
-- ALTER TABLE [테이블 명] ADD CONSTRAINT [제약 조건 종류](컬럼 명);
alter table supplier add constraint unique(sup_name);
desc supplier ;
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'supplier';

-- Duplicate entry '박철수' for key 'sup_name'
insert into supplier values(4, '박철수', '01012341234');


-- 3. check 제약 조건
-- 조건이 맞지 않으면 받지 않는 제약 조건
-- ALTER TABLE [테이블 명] ADD CONSTRAINT [제약 조건 종류](조건);
-- price 가 5000원 미만인 경우만 받겠다.
-- 주의사항 : 조건은 기존 데이터가 만족해야할 조건을 걸어야 한다.
alter table products add constraint check(price < 5000);

-- price 가 6000원 인 경우만 받겠다.
alter table products add constraint check(price <= 6000);
-- 6000원 보다 크면 안들어간다.
insert into products values(1114, 2, 6000);

-- 안 쓰는 이유
-- 조건의 유연성이 떨어진다.
-- 조건 변경 시 DDL 을 수정해야 한다.
-- 어떤 조건이 걸려있는지 파악하기가 쉽지 않다.
select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'products';