mysqlの外部キー制約について

Question

create table test_table_1(
	id int(11) not null,
	name varchar(30) not null,
	primary key (id)
)charset=utf8;


create table test_table_2(
	id int(11) not null,
	name  varchar(30) not null,
	age int(11) not null,
	persontext text not null,
)charset=utf8;

mysqlで上記のテーブルで、「test_table_1」を被参照元として「id」で外部キー制約をつけたのですが、
「test_table_2」で、idとnameの入力の関連性に制限を持たせる方法が知りたいです。

例えば「test_table_1」のデータを参照して、
「test_table_2」でid「5」には必ずname「武田」が入らないとエラーになる制約は可能ですか？

Answer 1

MySQLの場合、外部キー制約以上に複雑な制約をかけようとすればストアードプロシージャを使うぐらいしか手がありません。

というより、test_table_2でtest_table_1の関連付けしたデータを入れるぐらいなら、そのままtest_table_2には入れずに、必要になるたびにtest_table_1と連携してデータを取るという方が、正規化もされているのでおすすめです。

Comments

[makibi5577]

回答ありがとうございます。

Answer 2

たとえで、 test_table_2 テーブルに name 列は削って、名称は test_table_1.name を使うという鉄の掟にすれば済むことかと想像します。
狭い日本をとっても、現実では結婚した妹が再婚したり出来るので、現実にそぐわないモデルを云々してもどの程度有益かは疑問です。

Comments

[makibi5577]

回答ありがとうございます。

Answer 3

PKを変えても良いのであれば

CREATE TABLE `test_table_1` (
	`id` INT(11) NOT NULL,
	`name` VARCHAR(30) NOT NULL,
	PRIMARY KEY (`id`, `name`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `test_table_2` (
	`id` INT(11) NOT NULL,
	`name` VARCHAR(30) NOT NULL,
	`age` INT(11) NOT NULL,
	`persontext` TEXT NOT NULL,
	INDEX `FK_test_table_2_test_table_1` (`id`, `name`),
	CONSTRAINT `FK_test_table_2_test_table_1` FOREIGN KEY (`id`, `name`) REFERENCES `test_table_1` (`id`, `name`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

INSERT INTO `aaaaaa`.`test_table_2` (`id`, `name`, `age`, `persontext`) VALUES (5, '鈴木', 12, 'ああああ');
/* SQL エラー  (1452): Cannot add or update a child row: a foreign key constraint fails (`aaaaaa`.`test_table_2`, CONSTRAINT `FK_test_table_2_test_table_1` FOREIGN KEY (`id`, `name`) REFERENCES `test_table_1` (`id`, `name`)) */

となります。

Comments

[makibi5577]

回答ありがとうございます。

[makibi5577]

ミス投稿しました。