Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way to insert a group of items that are dependent but consistent between them with unique primary keys and foreign keys but that are colliding with items in database.

For example, given a table A:

id  primary key,

and a table B:

 id primary key,
 id_a'  -- foreign key on A

I want to insert:

INSERT INTO A(id, name) VALUES (1, "a");
INSERT INTO B(id, name, id_a) VALUES (1, "b", 1);

But the keys or could be taken already. Is there a way to insert my elements with auto setting key and foreign keys in a consistent way without colliding with existing elements?

share|improve this question
Does this answer your question?:… – ypercubeᵀᴹ Nov 4 '13 at 16:58
It won't help if there is several elements A and B. Edit : maybe with variables – hokkos Nov 4 '13 at 17:03
If you want to insert multiple rows in both tables, you can modify @Erwin's answer here: How do I insert a row which contains a foreign key? – ypercubeᵀᴹ Nov 4 '13 at 17:05
The paragraph titled INSERT new FK rows at the same time has a statement that uses table structure identical to yours. – ypercubeᵀᴹ Nov 4 '13 at 17:15
The problem is that I have tables C, D, E, ... with foreign keys between them, I am not sure writing functions for cascading creation is scalable with 40 tables. – hokkos Nov 4 '13 at 17:30

In reference to your comment, CTEs are scalable that way. You should do everything in a single transaction anyway. The related question @ypercube suggested should fit your case exactly:
How do I insert a row which contains a foreign key?

Your other option would be to employ lastval() and curval() to identify values most recently obtained from sequences. Not providing code, your question is too vague. These related questions on SO may be helpful:
Reference value of serial column in another column during same INSERT
PostgreSQL next value of the sequences?

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.