'How do I generate the lowest id that satisfies a multi-part unqiue key?

The following schema and INSERTs:

DROP TABLE IF EXISTS foo_bar;
DROP TABLE IF EXISTS foo;

CREATE TABLE foo (
    foo_id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    baz VARCHAR UNIQUE NOT NULL
);

CREATE TABLE foo_bar (
    foo_id INT NOT NULL,
    bar_id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    quux VARCHAR UNIQUE NOT NULL,
    FOREIGN KEY(foo_id) REFERENCES foo(foo_id),
    PRIMARY KEY(bar_id, foo_id)
);

INSERT INTO foo(baz) VALUES('baz1');
INSERT INTO foo(baz) VALUES('baz2');
INSERT INTO foo_bar(foo_id, quux) VALUES(1, 'quux 1/1');
INSERT INTO foo_bar(foo_id, quux) VALUES(1, 'quux 1/2');
INSERT INTO foo_bar(foo_id, quux) VALUES(2, 'quux 2/1');
INSERT INTO foo_bar(foo_id, quux) VALUES(2, 'quux 2/2');

give the results:

mud=> select * from foo;
 foo_id | baz  
--------+------
      1 | baz1
      2 | baz2
(2 rows)

mud=> select * from foo_bar;
 foo_id | bar_id |   quux   
--------+--------+----------
      1 |      1 | quux 1/1
      1 |      2 | quux 1/2
      2 |      3 | quux 2/1
      2 |      4 | quux 2/2
(4 rows)

I want the generated bar_ids to be the lowest values which satisfy the unique primary key constraint on (foo_id, bar_id).

i.e. I want the results to be:

mud=> select * from foo;
 foo_id | baz  
--------+------
      1 | baz1
      2 | baz2
(2 rows)

mud=> select * from foo_bar;
 foo_id | bar_id |   quux   
--------+--------+----------
      1 |      1 | quux 1/1
      1 |      2 | quux 1/2
      2 |     *1*| quux 2/1
      2 |     *2*| quux 2/2
(4 rows)

How can I alter my schema to achieve this?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source