Given:

1. Table A with multiple rows and attributes: (A_attr1 (key) , A_attr2).
2. Table B with attributes (B_attr1 (key) , A_attr1 (foreign key), B_attr2).

How do I insert some values in the table B only if the foreign key exists?

CodePudding user response：

First, we need to consider the fact that the condition (existence of foreign key in table A) is fundamental, in fact, if we try to add values in Table_B with an A_attr1 that it doesn't exist in Table_A we get an error of this type:

ERROR: the INSERT or the UPDATE on the TABLE table_B violates the foreign key constraint
"_A_attr1_"
DETAIL: the key (A_attr1)=(wrong_value) it's not present in the table "Table_A"

This is a possible solution:

INSERT INTO Table_B(B_attr1, A_attr1, B_attr2)
SELECT x.*
FROM (VALUES 

(something,something_else, something_else2),
(something_else3,something_else4, something_else5),
...
(something_else20, something_else21,something_else22)
) x(B_attr1, A_attr1, B_attr2)

WHERE EXISTS(
SELECT FROM TABLE_A y 
WHERE (y.A_attr1)=(x.A_attr1)
FOR SHARE);

The result is the addition in B of all the tuples that are acceptable (that is the one with the existing foreign keys).

This post is an extension of the following question: PostgreSQL insert if foreign key exists

The solution is based on the comments on this post:

https://dba.stackexchange.com/questions/252875/how-to-make-on-conflict-work-for-compound-foreign-key-columns/252925#252925

CodePudding user response：

In Postgres, we can use the Where Exists to implement your use case.

Here is an example of using it.

Insert Into Table_B Select 'Value 1', 'Foreign Value', 'Value 2' Where Exists 
(Select 1 From Table_A Where A_attr1 = 'Foreign Value');

This will insert only if the "Foreign Value" is present in Table_A.

Hope this helps