'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 |
|---|
