-1

I have two tables (A and B), and a foreign key in B, referencing A. I'm trying to insert rows in B, but with the validation of just inserting the rows that have an existing key in A.

With this table structure, I want to achieve the following:

create table a (id int primary key);
create table b (a_id int not null);
alter table b add foreign key (a_id) references a(id);

insert into a values (1), (2), (3);
insert into b values (2), (3), (4); -- This should just insert 2, 3 and exclude 4, since it doesn't exist in table A.

select * from b; -- The query should return just the foreign key 2 and 3.

Something like that. Is there any way to achieve this in PostgreSQL? Thanks.

Angel D.
  • 162
  • 2
  • 11
  • The only problem I see is that you are trying to insert the values in one statement. So this will insert nothing. If you try to insert the values into `b` one by one, `2` and `3` will pass, `4` wil be rejected. – clamp Nov 13 '19 at 00:30
  • Possible duplicate of [Can INSERT \[...\] ON CONFLICT be used for foreign key violations?](https://stackoverflow.com/questions/36038751/can-insert-on-conflict-be-used-for-foreign-key-violations) or [Is it possible to catch a foreign key violation in postgres](https://stackoverflow.com/q/37767474/1048572) – Bergi Nov 13 '19 at 02:07

1 Answers1

-1

What I would do is select the ids from table "a" and insert them into the foreign key column for table "b".

INSERT INTO b(a_id) SELECT id FROM a;
PeonProgrammer
  • 1,445
  • 2
  • 15
  • 27