데이터베이스 (2025 11 17)

drop procedure if exists errorProc;
delimiter $$
create procedure errorProc()
begin
	declare continue handler for 1146 select '테이블이 없음' as '메시지';
    select * from noTable;
end $$
delimiter ;
call errorProc();

프로시저를 사용하여 error 핸들링도 가능하다

 

drop procedure if exists errorProc2;
delimiter $$
create procedure errorProc2()
begin
	declare continue handler for sqlexception
    begin
		show errors;
        select '오류 발생' as '메시지';
        rollback;
	end;
    insert into usertbl values('YJS', '윤정수', 1988, '서울', null, null, 170, current_date());
end $$
delimiter ;
call errorProc2();

 

오류발생시 rollback을 일으키는 프로시저이다

 

use cookdb;
prepare myQuery from 'select * from usertbl where userID = "NHS"';
execute myQuery;
deallocate prepare myQuery;

prepare를 사용해서 쿼리를 미리 대기시켜 줄 수 있다 = 즉시 실행하지 않음

 

 

 

use tabledb;
drop table if exists buyTBL, userTBL;
create table userTBL
(
	userID char(8) not null primary key,
    userName varchar(10) not null,
    birthYear int not null,
    addr char(3) null,
    mobile2 char(8) null,
    height smallint null,
    mDate date null
);
create table buyTBL
(
	num int auto_increment not null primary key,
    userID char(8) not null,
    prodName char(6) not null,
    groupName char(4) null,
    price int not null,
    amount smallint not null,
    foreign key(userID) references userTBL(userID)
);

확인용 테이블을 생성함

insert into userTBL values('YJS', '유재석', 1972, '서울', '010', '11111111', 178, '2008-8-8');
insert into userTBL values('KHD', '강호동', 1970, '경북', '010', '22222222', 182, '2007-7-7');
insert into userTBL values('KKJ', '김국진', 1965, '서울', '010', '33333333', 171, '2009-9-9');

insert into buyTBL values(null, 'KHD', '운동화', null, 30, 2);
insert into buyTBL values(null, 'KHD', '노트북', '전자', 1000, 1);
insert into buyTBL values(null, 'KYM', '모니터', '전자', 200, 1);

이렇게 넣으려 하면

16:52:05 insert into buyTBL values(null, 'KYM', '모니터', '전자', 200, 1) Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`tabledb`.`buytbl`, CONSTRAINT `buytbl_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `usertbl` (`userID`)) 0.000 sec

 

외래키 제약 조건이 막아버린다

이를 막을려면 참조받는 쪽에서 미리 모든 정보를 모두 넣어두어야 한다

 

create table userTBL
(
	userID char(8) not null,
    userName varchar(10) not null,
    birthYear int not null,
    addr char(2) null,
    mobile1 char(3) null,
    mobile2 char(8) null,
    height smallint null,
    mDate date null,
    constraint primary key PK_userTBL_userID (userID)
);

primary key를 세팅할때 위와 같이 constraint를 사용하면 primary key의 이름을 지정할 수 있다

 

create table userTBL
(
	userID char(8) not null,
    userName varchar(10) not null,
    birthYear int not null,
    addr char(2) null,
    mobile1 char(3) null,
    mobile2 char(8) null,
    height smallint null,
    mDate date null
);
alter table userTBL
	add constraint PK_userTBL_userID primary key (userID);

alter table에서 add constraint를 사용하여 수정을 할 수도 있다

 

drop table if exists prodTBL;
create table prodTBL
(
	prodCode char(3) not null,
    prodID char(4) not null,
    prodDate datetime not null,
    prodState char(10) null
);
alter table prodTBL
	add constraint PK_prodTBL_proCode_prodID
    primary key (prodCode, prodID);

이와 같이 primary key를 2개 이상 지정하는 것도 가능하다

drop table if exists prodTBL;
create table prodTBL
(
	prodCode char(3) not null,
    prodID char(4) not null,
    prodDate datetime not null,
    prodState char(10) null,
    constraint PK_prodTBL_proCode_prodID primary key (prodCode, prodID)
);

다른 방식으로 이렇게 만들 수도 있다

 

drop table if exists buyTBL, userTBL;
create table userTBL
(
	userID char(8) not null primary key,
    userName varchar(10) not null,
    birthYear int not null
);
create table buyTBL
(
	num int auto_increment not null primary key,
    userID char(8) not null,
    prodName char(8) not null,
    foreign key (userID) references userTBL (userID),
    constraint FK_userTBL_buyTBL foreign key (userID) references userTBL (userID)
);

외래키도 동일하게 적용 가능하다

 

drop table if exists userTBL;
create table userTBL
(
    userName varchar(10) not null primary key,
    birthYear int not null,
    addr char(2) null,
    mobile1 char(3) null,
    mobile2 char(8) null,
    height smallint null,
    mDate date null
);
alter table userTBL
	alter column birthYear set default -1;
alter table userTBL
	alter column addr set default '서울';
alter table userTBL
	alter column height set default 170;

특정 column의 기본 값을 수정하는 것도 가능하다

 

insert into userTBL values ('YBJ', '유병재', default, default, '010', '12345678', default, '2019.12.12');

이때에는 이렇게 데이터를 넣을 수 있다

 

drop table if exists userTBL;
create table userTBL
(
	userID int primary key,
    userName varchar(10),
    birthYear int check(birthYear >= 1900 and birthYear <= 2030),
    addr char(2) null,
    mobile1 char(3) null,
    mobile2 char(8) null,
    height smallint null,
    mDate date null
    constraint CK_userName check (userName is not null)
);

check 문으로 제약 조건을 넣는 것도 가능하다

 

use tabledb;
alter table userTBL
	add homepage varchar(30)
		default 'test'
        null;

열을 추가하고 싶다면 alter - add로 열을 추가할 수 있다

alter table userTBL
	drop column mobile1;

열 삭제도 가능하다

alter table userTBL
	change column userName uName varchar(20) null;

수정도 가능하다