CODEWITHSUNDEEP
mysqldatabase-designforeign-keysdatabase-schema
teeparty
teeparty

Reputation: 296

Allow a foreign key insertion only if another foreign key is matched

I have these 3 tables:

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

I would like to enforce this: a location_id for an asset is acceptable only if asset.company_id = location.company_id

currently I'm enforcing this through the application, I was wondering if it's possible to do this using only MySQL.

Upvotes: 0

Views: 79

Answers (1)

Drew
Drew

Reputation: 24959

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');

remember that your FK must be back to a single parent table with a key in the same composite order (company,location) in this example

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 ...

Upvotes: 1

Related Questions