[DB_MySQL] 이것이 MySQL이다 ch08 테이블과 뷰 1-1(테이블 제약 조건 및 수정 방법 실습)

1. 테이블 생성 및 데이터 삽입

use tabledb;

drop table if exists buytbl;
drop table if exists usertbl;

-- 테이블 생성
-- 회원 테이블
create table usertbl(
	userID		char(8),	
    name		varchar(10),					
    birthYear	int,					
    addr		char(2),					
    mobile1		char(3),									
    mobile2		char(8),									
    height		smallint,			
    mDate		date				
 );
 
 -- 회원 구매 테이블 (buytbl)
 create table buytbl(
	num			int	auto_increment primary key,
	userID		char(8),
    prodName	char(6),
    groupName	char(4),
    price		int,
    amount		smallint
);

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', null, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1871, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');

INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화',  NULL, 30,	2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000,	1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200,	1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,	5);

desc usertbl;

 

2. 기본키 설정 및 외부키(외래키) 설정

-- userID를 기본키로 설정
alter table usertbl
	add constraint PK_usertbl_userID
    primary key(userID);

-- 외래키 설정 : error 발생 원인? : usertbl에 bbk에 대한 정보가 없어서 에러
alter table buytbl
	add constraint FK_usertbl_buytbl
    foreign key(userID)
    references usertbl(userID);
    
-- bbk 제거
set SQL_SAFE_UPDATES = 0;
delete from buytbl where userid = 'bbk';

insert into buytbl values (null, 'BBK', '모니터', '전자', 200, 5);

-- 제약 조건 임시 해제 : set foreign_key_checks = 0
-- 제약 조건 설정 : set foreign_key_checks = 1

set foreign_key_checks = 0;

외부키 설정시 usertbl에 bbk에 대한 정보가 없어서 에러가 발생합니다.

bbk를 삭제하고 외부키 제약 조건을 임시 해제합니다.

그 후 다시 실행되지 않았던 외부키를 다시 실행합니다.

 

3. 데이터 입력 및 CHECK 제약 조건 추가

INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,	5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50,	3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80,	10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책'   , '서적', 15,	5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'   , '서적', 15,	2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50,	1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화',  NULL, 30,	2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'   , '서적', 15,	1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화',  NULL, 30,	2);
set foreign_key_checks = 1;
-- 외부키 다시 적용

select * from usertbl;

-- CHECK 제약 조건 추가 : usertbl의 출생년도를 1900 ~ 2023까지만 입력 가능하게 설정(null 값 허용 안됨)
alter table usertbl
	add constraint CK_brithYear
    CHECK ((birthYear >= 1900 and birthYear <= 2023) and (birthYear is not null));
    
-- 김경호의 출생년도가 1871로 설정되어있고 김범수의 출생년도 null 제약 조건을 충족하지 못하여 에러 발생

 

4. 조건을 변경 및 데이터 추가 입력

-- 김경호의 출생년도를 1971, 김범수의 출생년도 1979 년으로 변경

update usertbl set birthYear = 1971 where userID = 'KKH';
update usertbl set birthYear = 1979 where userID = 'KBS';

select * from usertbl;

INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울',  null,  NULL    , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남',  NULL,  NULL    , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');

select * from usertbl order by userid, name;​

 

5. userID 변경

1. 바로 BBK -> VVK로 변경합니다.

-- 바비킴 사용자가 자신의 ID를 변경하고자 한다. BBK -> VVK
update usertbl set userID = 'VVK' where userID = 'BBK';

-- 외래키 제약 조건으로 userID가 변경되지 않는다.​

외래키 제약때문에 userID가 변경되지 않습니다.


2. 외래키 제약 조건 해제

-- 제약 조건을 일시적으로 중지
-- 외래키 제약 조건 중지
set foreign_key_checks = 0;
update usertbl set userID = 'VVK' where userID = 'BBK';
-- 외래키 제약 조건 시작
set foreign_key_checks = 1;

외래키 조건을 일시적으로 해제하면 값을 변경할 수 있습니다.
외래키를 해제하면 데이터 입력시 데이터의 무결성이 없어질 수 있습니다. (데이터가 회손될 수 있음)


3. 물품 배송을 위한 회원테이블과 구매테이블 조인

-- 물품 배송을 위해 회원 테이블과 구매 테이블을 조인
select B.userid, U.name, B.prodName, U.addr, concat(U.mobile1, U.mobile2) as '연락처'
	from buytbl B
		left outer join usertbl U
			on B.userid = U.userid
	order by B.userid;

12개의 행이 출력됩니다.
usertbl의 BBK는 모두 VVK로 변경되었지만 buytbl에서는 변경되지 않았습니다.
따라서 다시 BBK로 변경한 후 외래키 조건을 이용해 연동해보겠습니다.

4. BBK로 변경

-- usertbl의 VVK를 원래 BBK로 변경, buytbl의 BBK -> VVK로 변경
-- 외래키 제약 조건 중지
set foreign_key_checks = 0;
update usertbl set userID = 'BBK' where userID = 'VVK';
-- 외래키 제약 조건 시작
set foreign_key_checks = 1;

5. 기존 외래키 삭제 후 조건 추가 후 외래키 재생성

-- 외래키 제약 조건이 걸린 userID를 쉽게 변경 : 제약조건 해제 테이블에 userid를 변경 제약조건 시작
-- 외래키 on update, on delete를 이용해서 관계가 형성된 제약조건 부분을 동시에 변경 또는 삭제
-- 기존 외래키 삭제 후 기능 추가
alter table buytbl
	drop foreign key FK_usertbl_buytbl;

-- 외래키 추가 생성(on update cascade를 통해 기본키 변경시 같이 변경되도록 연동)
alter table buytbl
	add constraint FK_usertbl_buytbl
    foreign key (userID)
    references usertbl(userID)
    on update cascade;

6. BBK를 VVK로 변경 후 재실행

update usertbl set userID = 'VVK' where userID = 'BBK';

select B.userid, U.name, B.prodName, U.addr, concat(U.mobile1, U.mobile2) as '연락처'
	from buytbl B
		inner join usertbl U
			on B.userid = U.userid
	order by B.userid;​

따로 외래키 제약조건을 해지하지 않아도 되고 buytbl의 userid도 모두 연동이 되었습니다!

 

6. userID 삭제

1. VVK 삭제

delete from usertbl where userID = 'VVK';​

 

외래키 제약 조건 때문에 삭제 되지 않습니다.


2. 외래키 제약 조건 추가

alter table buytbl
	drop foreign key FK_usertbl_buytbl;

alter table buytbl
	add constraint FK_usertbl_buytbl
    foreign key (userID)
    references usertbl(userID)
    on update cascade   -- 관계 설정된 테이블에 데이터도 같이 수정
    on delete cascade;  -- 관계 설정된 테이블에 데이터도 같이 삭제​

on delete cascade를 추가하면 외래키가 적용되어도 데이터를 연동해서 삭제할 수 있습니다.

3. 다시 삭제

delete from usertbl where userID = 'VVK';​

총 12개 중 4개가 삭제되고 8개가 남습니다!

4. usertbl의 birthYear 컬럼 삭제

alter table usertbl drop column birthYear;​

select * from usertbl order by userid, name;

최종적으로 birthYear 컬럼이 삭제된 값이 출력됩니다.

다른 DBMS에서는 CHECK 제약 조건이 설정된 열은 삭제되지 않습니다.

 

실습을 통해서 기본키, 외부키(외래키) 연결 해제 및 데이터 입력을 해보았습니다!

 

좀 더 익숙해지도록 노력해야겠어요!!

 

많은 분들의 피드백은 언제나 환영합니다! 많은 댓글 부탁드려요~~