SQL; Unique constraint across tables

Recently I encountered several different data objects that all have a property akin to an 'id assigned by the company'. This field is a human readable string with a prescribed format, incremented by one shared sequence. Using a highly unique property like a UUID was sadly ruled out.

In the legacy data there seemed to be clear problems with keeping data consistent even within tables, so the database design is being implemented defensively.

It's possible to implement such a field as a One to One relationship from an 'id_from_company' table to the other tables. But this does not prevent the same id_from_company to be used twice, by using it in two different tables.

To uphold uniqueness across multiple tables, each table sadly needed to have a check constraint added, that checks for this uniqueness. I wouldn't soon recommend such a "unique over multiple columns" property, because of the overhead I imagine it creates. But it already existed, and is here to stay.

So here is an example to create a "unique across tables" constraint. It ain't pretty, but it works.

--Create a sequence to generate id's consistent across tables
create sequence one_for_all;

--Set the default value of the field to the next value of this sequence
create table quack(
     id serial primary key,
     id_from_company bigint default nextval('one_for_all'),
     a_field varchar
);

--Not recommendable, Not pretty, but it works
create function only_one_for_all(id_from_company bigint)
    RETURNS bool as $$
    begin
        return (select count(*) < 1
        from (
            select  quick.id_from_company
            from quick
            where quick.id_from_company = only_one_for_all.id_from_company
            union
            select quack.id_from_company
            from quack
            where quack.id_from_company = only_one_for_all.id_from_company
            union
            select quock.id_from_company
            from quock
            where quock.id_from_company = only_one_for_all.id_from_company
            ));
    end; $$
language plpgsql;

--use the boolean function as the input for a check constraint on the table
alter table quack
    add constraint just_one check (only_one_for_all(id_from_company));