---
# System prepended metadata

title: 参照整合性実験

---

# 参照整合性実験

# 通常 

## 事前準備

```sql
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 ("のび太");
```

```sql
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");
```

```sql
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)
);
```

## テスト

```sql
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

```


```sql
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`))
```

```sql
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`))
```

```sql
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`))
```
