mysql - 仅当另一个外键匹配时才允许外键插入

Question

我有这 3 个表:

--company--
company_id (primary key)
name

--location--
location_id (primary key)
company_id (foreign key referencing company.company_id)   
name

--asset--
asset_id (primary_key)
company_id (foreign key referencing company.company_id)  
location_id (foreign key referencing location.location_id)
name

我想强制执行这一点:仅当 asset.company_id = location.company_id 时， Assets 的 location_id 才是可接受的

目前我正在通过应用程序强制执行此操作，我想知道是否可以仅使用 MySQL 来执行此操作。

Answer 1

drop table company;
create table company
(   company_id int not null auto_increment,
    name varchar(100) not null,
primary key(company_id)
)ENGINE=InnoDB
;

insert into company(name) values ('acme widgets');
insert into company(name) values ('goober chocolates');
insert into company(name) values ('Fat R Us');


drop table location;
create table location
(   location_id int not null,
    company_id int not null,
    name varchar(100) not null,
primary key(company_id,location_id),
FOREIGN KEY (company_id ) REFERENCES company(company_id)
)ENGINE=InnoDB
;

insert into location(location_id,company_id,name) values (1,1,'Cambridge MA');
insert into location(location_id,company_id,name) values (1,2,'Boston MA');
insert into location(location_id,company_id,name) values (1,3,'Topeka KS');
insert into location(location_id,company_id,name) values (2,1,'Everywhere USA');
insert into location(location_id,company_id,name) values (2,666,'Fail Test this will fail');

create table asset
(   asset_id int not null auto_increment,
    company_id int not null,
    location_id int not null,
    name varchar(100) not null,
    primary key(asset_id),
    CONSTRAINT fk_asset_cl FOREIGN KEY (company_id,location_id)
                        REFERENCES location(company_id,location_id)
)ENGINE=InnoDB
;

insert into asset(company_id,location_id,name) values (1,1,'typewriter');
insert into asset(company_id,location_id,name) values (1,8,'typewriter fail');

请记住，在此示例中，您的 FK 必须返回到具有相同组合顺序(公司、位置)的键的单个父表

insert into asset(company_id,location_id,name) values (1,8,'typewriter fail');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ...