1:1 mapping means that if one table has a row, so does the other.
How does this guarantee that the ID is in both tables?
How does this guarantee that the ID is in both tables?
Create both tables, with the same PK the second with a deferrable, initially deferred foreign key to the first.
Alter the first to add a deferrable, initially deferred foreign key to the second. [Edited to correct swapped table refs in this ¶]
Mission accomplished.
create procedure mandatory_ (_table_name "text", _mandatory_table_name "text") language plpgsql as $$
declare
_trigger_name "text";
begin
_trigger_name = _table_name || '_mandatory_' || _mandatory_table_name || '_trigger';
execute
'create function "' || _trigger_name || '_" () returns trigger language plpgsql as $' || '$ '
'begin '
'if not exists '
'( select 1 '
'from public."' || _mandatory_table_name || '" _mandatory '
'where _mandatory ."' || _table_name || '_id" = new ."id" ) '
'then '
'raise '
'''non-existent relation between "%" and "%" violates mandatory participation constraint'' '
', ' || quote_literal (_table_name) ||
', ' || quote_literal (_mandatory_table_name) || '; '
'end if;'
'return new; '
'end; '
'$' || '$ ';
execute
'create constraint trigger "' || _trigger_name || '" '
'after insert or update on "' || _table_name || '" '
'deferrable initially deferred '
'for each row execute function "' || _table_name || '_mandatory_' || _mandatory_table_name || '_trigger_" () ';
end;
$$;