How can I insert a new row in a table with a foreign key reference only if the foreign key (in this case model) exists?
Currently I have the following statement:
INSERT INTO furniture (model, type) VALUES (modelA, chair)
1 Answer
Use a SELECT that returns nothing if the FK does not exist.
INSERT INTO furniture (model, type)
select 'modelA', 'chair'
where exists (select *
from model
where model.model = 'modelA');
You did not tell us what the referenced table is called. I assumed it's model - you need to adjust that to the real names.
1 Comment
ievgen.garkusha
you may also want to add locking to the exists clause to avoid race conditions see: dba.stackexchange.com/a/252925
... WHERE EXISTS (SELECT * FROM models WHERE model = modelA)