Given:
- Table A with multiple rows and attributes: (A_attr1 (key) , A_attr2).
- 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?
2 Answers
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
Comments
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:
4 Comments
Adrian Klaver
Sort of fundamental. You can enter a row in the child table(B) that has
NULL for the A_attr1 (foreign key) and it will succeed with the default action of MATCH SIMPLE per Create Table. So you can have child records that don't match on a parent record FK. Adding a NOT NULL to the child FK referencing column deals with that.
Bergi
Why don't you just post an answer to stackoverflow.com/questions/39224438/… instead of re-asking the question?
Scooterz Giovanni
Could have done that, i thought it was more clear this way.
Scooterz Giovanni
The problem was adding a tuple B with an A_attr1 not present in Table_A and therefore i didn't need it.
A_attr1must exist inTable Abefore being inserted ontoTable_B. This is well covered in the answers. The other condition is thatA_attr1is not provided in the insert toTable_b. This condition is covered by anot nullconstraint on Table_B.A_attr1.