[01.12] 내일배움캠프[Spring] TIL-52
1.SQL
제약 조건
create table member (
mem_id char(8) not null primary key,
mem_name varchar(10) not null,
height tinyint unsigned null
);
create table buy(
num int auto_increment not null primary key,
mem_id char(8) not null,
prod_name char(6) not null,
foreign key(mem_id) references member(mem_id)
);
insert into member values('BLK','블랙핑크',163);
insert into buy values(null,'BLK','지갑');
insert into buy values(null,'BLK','맥북');
select m.mem_id,m.mem_name,b.prod_name
from buy B join member m on b.mem_id = m.mem_id;
update member set mem_id = 'PINK' where mem_id = 'BLK';
create table buy (
num int auto_increment not null primary key,
mem_id char(8) not null,
prod_name char(6) not null,
foreign key(mem_id) references member(mem_id)
on update cascade
on delete cascade
);
alter table buy drop foreign key mem_id;
alter table buy add constraint foreign key
(mem_id) references member(mem_id) on delete cascade on update cascade;
update member set mem_id = 'PINK' where mem_id = 'BLK';
delete from member where mem_id = 'BLK';
2. 코딩 테스트