create table academy ( bid char(6) not null primary key, business_name varchar(100) not null, business_owner varchar(50) not null, business_number varchar(20) not null, business_date datetime not null, business_address varchar(200) not null, business_contact varchar(15) not null, uid varchar(70) not null ); create table payload ( pid char(22) not null primary key, title varchar(100) not null, subtitle varchar(100) null, body varchar(255) not null, alert_yn tinyint(1) default 1 not null, category char(4) not null, content text null ); create table push_cabinet ( uid varchar(70) not null, pid char(22) not null, send_date datetime not null, check_yn tinyint(1) default 0 not null, primary key (uid, pid), constraint fk_pushcabinet_pid_to_payload foreign key (pid) references payload (pid) on update cascade on delete cascade, constraint fk_pushcabinet_uid_to_user foreign key (uid) references user (uid) on update cascade on delete cascade ); ALTER TABLE payload ADD COLUMN bid CHAR(6) NOT NULL DEFAULT '000000' AFTER pid ; ALTER TABLE payload DROP PRIMARY KEY , ADD PRIMARY KEY (bid, pid) ; ALTER TABLE payload ADD CONSTRAINT fk_payload_bid_to_academy FOREIGN KEY (bid) REFERENCES academy(bid) ON UPDATE CASCADE ON DELETE CASCADE ; # //------// ALTER TABLE push_cabinet ADD COLUMN bid CHAR(6) NOT NULL DEFAULT 'AA0000' AFTER uid ; ALTER TABLE push_cabinet DROP PRIMARY KEY, ADD PRIMARY KEY (bid, uid, pid) ; ALTER TABLE push_cabinet DROP FOREIGN KEY fk_pushcabinet_pid_to_payload ; ALTER TABLE push_cabinet DROP FOREIGN KEY fk_pushcabinet_uid_to_user ; ALTER TABLE push_cabinet ADD CONSTRAINT fk_pushcabinet_payload FOREIGN KEY (bid, pid) REFERENCES payload(bid, pid) ON UPDATE CASCADE ON DELETE CASCADE ; ALTER TABLE push_cabinet ADD CONSTRAINT fk_pushcabinet_bid_to_academy FOREIGN KEY (bid) REFERENCES academy(bid) ON UPDATE CASCADE ON DELETE CASCADE ;