Is there pattern to have union table for different items?

Question

I'd like to have column constraint based combination of 2 columns. I don't find the way to use foreign key here, because it should be conditional FK, then. Hope this basic SQL shows the problem:

CREATE TABLE performer_type (
  id serial primary key,
  type varchar
);

INSERT INTO performer_type ( id, type ) VALUES (1, 'singer'), ( 2, 'band');

CREATE TABLE singer (
  id serial primary key,
  name varchar
);

INSERT INTO singer ( id, name ) VALUES (1, 'Robert');

CREATE TABLE band (
  id serial primary key,
  name varchar
);

INSERT INTO band ( id, name ) VALUES (1, 'Animates'), ( 2, 'Zed Leppelin');

CREATE TABLE gig (
  id serial primary key,
  performer_type_id int default null, /* FK, no problem */
  performer_id int default null       /* want FK based on previous FK, no good solution so far */
);

INSERT INTO gig ( performer_type_id, performer_id ) VALUES ( 1,1 ), (2,1), (2,2), (1,2), (2,3);

Now, the last INSERT works, but for last 2 value pairs I'd like it fail, because there is no singer ID 2 nor band ID 3. How to set such constraint?

I already asked similar question in Mysql context and only solution was to use trigger. Problem with trigger was: you can't have dynamic list of types and table set. I'd like to add types (and related tables) on the fly.

I also found very promising pattern, but this is upside down for me, I did not figured out, how to turn it to work in my case.

What I am looking here seems to me so useful pattern, I think there must be some common way for it. Is it?

Edit.

Seems, I choose bad items in my examples, so I try make it clear: different performer tables (singer and band) have NO relation between them. gig-table just has to list tasks for different performers, without setting any relations between them.

Another example would items in stock: I may have item_type-table, which defines hundreds of item-types with related tables (for example, orange and house), and there should be table stock which enlists all appearances of items.

PostgreSQL I use is 9.6

Answer 1

Based on @Laurenz Albe answer I form a solution for example above. Main difference: there is parent table performer, which PK is FK/PK for specific performer-tables and is referenced also from gig table.

CREATE TABLE performer_type (
  id serial primary key,
  type varchar
);
INSERT INTO performer_type ( id, type ) VALUES (1, 'singer' ), ( 2, 'band' );

CREATE TABLE performer (
  id serial primary key,
  performer_type_id int REFERENCES performer_type(id)
);

CREATE TABLE singer (
  id int primary key REFERENCES performer(id),
  name varchar
);

INSERT INTO performer ( performer_type_id ) VALUES (1); -- get PK 1 for next statement
INSERT INTO singer ( id, name ) VALUES (1, 'Robert');

CREATE TABLE band (
  id int primary key REFERENCES performer(id),
  name varchar
);

INSERT INTO performer ( performer_type_id ) VALUES (2); -- get PK 2 for next statement
INSERT INTO singer ( id, name ) VALUES (2, 'Animates');
INSERT INTO performer ( performer_type_id ) VALUES (2); -- get PK 3 for next statement
INSERT INTO singer ( id, name ) VALUES (3, 'Zed Leppelin');

CREATE TABLE gig (
  id serial primary key,
  performer_id int REFERENCES performer(id)
);

INSERT INTO gig ( performer_id ) VALUES (1), (2), (3), (4);

And the last INSERT fails, as expected:

ERROR:  insert or update on table "gig" violates foreign key constraint "gig_performer_id_fkey"
DETAIL:  Key (performer_id)=(4) is not present in table "performer".

But

For me there is annoying problem: I have no good way to make distinction which ID is for singer and which for band etc. (in original example I had performer_type_id in gig-table for that), because any performer_id may belong any performer. So I'd like any performer type has it's own ID range, so I create dummy table for every sequence

CREATE TABLE band_id (
  id int primary key,
  dummy boolean default null
);
CREATE SEQUENCE band_id_seq START 1;
ALTER TABLE band_id ALTER COLUMN id SET DEFAULT nextval('band_id_seq');

CREATE TABLE singer_id (
  id int primary key,
  dummy boolean default null
);
CREATE SEQUENCE singer_id_seq START 2000000;
ALTER TABLE singer_id ALTER COLUMN id SET DEFAULT nextval('singer_id_seq');

Now, to insert new row into specific perfomer table I have to get next ID for it:

INSERT INTO band_id (dummy) VALUES (NULL);

Trying to figure out, is it possible to solve this process on DB level, or has something to done in App-level. It would be nice, if inserting into band table could:

• before trigger inserting into band_id to genereate specific ID
• before trigger inserting this new ID into performer-table
• include this new ID into INSERT into band

Frist 2 points are easy, but the last point is not clear for now.