[데이터 베이스]8.데이터 정의(DDL)

-- 1.create
create table team_tb (
	tno int primary key,
    tname varchar(10) unique,
    tyear int,
    tloc varchar(10)
) char set=utf8mb4;

create table play_tb (
	pno int primary key,
    pname varchar(20),
    pnumber int,
    prole varchar(10),
    tno int
) char set=utf8mb4;

select * from team_tb;
desc team_tb; 

select * from play_tb;

-- 2. alter table(수정)
alter table player_tb change column prole ptype varchar(20);

-- 3. drop table (삭제)
drop table player_tb;

-- 4. truncate (테이블비우기) 
truncate team_tb;

-- 5. 제약조건들!!

drop table player_tb;

create table player_tb (
	pno int primary key auto_increment,
    pname varchar(20) not null,
    pnumber int,
    prole varchar(10) default '타자',
    tno int
) char set=utf8mb4;

insert into player_tb(pname, pnumber, tno) values('이대호', 20, 3);
insert into player_tb(pname, pnumber, prole, tno) values('가득염', 10,'투수', 3);
insert into player_tb(pname, pnumber, prole, tno) values('임수혁', 5,'포수', 3);
insert into player_tb(pname, prole, tno) values('이승엽','1루수', 1);
insert into player_tb(pname, pnumber, prole, tno) values('박병호', 18,'1루수', 2);

1. FK제약조건

-- DDL (create, drop, alter)

-- 모든 제약조건 잠시 해제
drop table if exists team_tb;
drop table if exists player_tb;

-- 1. create table (fk)
create table team_tb (
	tno int primary key,
    tname varchar(10) unique,
    tyear int,
    tloc varchar(10)
) charset=utf8mb4;

create table player_tb (
	pno int primary key auto_increment,
    pname varchar(20) not null,
    pnumber int,
    prole varchar(10) default '타자',
    tno int,
    constraint fk_player_tb foreign key (tno) references team_tb (tno)
) charset=utf8mb4;

-- 2. 더미데이터 세팅
insert into team_tb(tno, tname, tyear, tloc) values(1, '삼성', 1982, '대구');
insert into team_tb(tno, tname, tyear, tloc) values(2, '넥센', 2000, '서울');
insert into team_tb(tno, tname, tyear, tloc) values(3, '롯데', 1990, '부산');

insert into player_tb(pno, pname, pnumber, prole, tno) values(1, '이대호', 20, '1루수', 3);
insert into player_tb(pno, pname, pnumber, prole, tno) values(2, '가득염', 10, '투수', 3);
insert into player_tb(pno, pname, pnumber, prole, tno) values(3, '임수혁', 5, '포수', 3);
insert into player_tb(pno, pname, pnumber, prole, tno) values(4, '이승엽', 3, '1루수', 1);
insert into player_tb(pno, pname, pnumber, prole, tno) values(5, '박병호', 19, '1루수', 2);

-- 3. fk 제약조건으로 인해 insert 불가능
insert into player_tb(pname, pnumber, prole, tno) values('홍길동', 19, '1루수', 4);

-- 4. 삭제
-- where절에 고유하게 식별하는 값인 pno로 삭제
delete from player_tb where pno = 5;

-- 5.삭제 (실패)
-- (1) 참조하고 있는 이승엽의 tno를 null로 업데이트 후 삭제하면 잘됨
delete from team_tb where tno = 1;

-- (2) cascade - on delete
drop table if exists team_tb;
drop table if exists player_tb;


create table team_tb (
	tno int primary key,
    tname varchar(10) unique,
    tyear int,
    tloc varchar(10)
) charset=utf8mb4;

create table player_tb (
	pno int primary key auto_increment,
    pname varchar(20) not null,
    pnumber int,
    prole varchar(10) default '타자',
    tno int,
    constraint fk_player_tb foreign key (tno) references team_tb (tno)
    on delete cascade -- update
) charset=utf8mb4;

select * from player_tb;

delete from team_tb where tno = 3;

-- (3) cascade - on delete set null
drop table if exists team_tb;
drop table if exists player_tb;


create table team_tb (
	tno int primary key,
    tname varchar(10) unique,
    tyear int,
    tloc varchar(10)
) charset=utf8mb4;

create table player_tb (
	pno int primary key auto_increment,
    pname varchar(20) not null,
    pnumber int,
    prole varchar(10) default '타자',
    tno int,
    constraint fk_player_tb foreign key (tno) references team_tb (tno)
    on delete set null
) charset=utf8mb4;

select * from player_tb;

delete from team_tb where tno = 3;