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

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?

CodePudding user response：

Don't do it.

Primary keys only requirement is that their values need to be unique. They are internal identifiers that are not meant to be exposed to the users in the UI or through an external API, or other.

If you are asking this you probably want to expose them somewhere, and that's a big red flag in your design.

If you really need to show a customized ID, then you can create an extra generated column that can be formatted with prefixes, suffixes, dashes, and take any sequence or custom logic you want.