# 参照整合性実験

# 通常

## 事前準備

create table reporters
(
	id int auto_increment,
	name varchar(255) not null,
	primary key (id)
);

insert into reporters (name) values ("太郎");
insert into reporters (name) values ("花子");
insert into reporters (name) values ("のび太");

create table bugstatus
(
	id int auto_increment,
	status varchar(255) unique not null,
	primary key (id)
);

insert into bugstatus (status) values ("NEW");
insert into bugstatus (status) values ("RESLOVE");
insert into bugstatus (status) values ("PROGRESS");

create table bugs 
 (
	id int auto_increment,
	reporter_id int not null,
	status varchar(255) not null,
	primary key (id),
	foreign key fk_reporters_id (reporter_id) references reporters (id),
	foreign key fk_bug_status (status) references bugstatus (status)
);

## テスト

insert into bugs (reporter_id, status) values (1, "NEW");

Query OK, 1 row affected (0.01 sec)

update bugs set status = "PROGRESS" where id = 1;

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

insert into bugs (reporter_id, status) values (4, "NEW");

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`experiment_foreign_key`.`bugs`, CONSTRAINT `bugs_ibfk_1` FOREIGN KEY (`reporter_id`) REFERENCES `reporters` (`id`))

insert into bugs (reporter_id, status) values (1, "HOGE");

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`experiment_foreign_key`.`bugs`, CONSTRAINT `bugs_ibfk_2` FOREIGN KEY (`status`) REFERENCES `bugstatus` (`status`))

update bugs set status = "HOGE" where id = 1;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`experiment_foreign_key`.`bugs`, CONSTRAINT `bugs_ibfk_2` FOREIGN KEY (`status`) REFERENCES `bugstatus` (`status`))