How do you enforce the 1:1 relationship at the database level?
I don't think it's very elegant and people often forget the unique constraint.
One table is a much better solution.
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;
$$;At the database level there's only one kind of foreign key.
Yeah, you can try and prevent it from happening in code or throw some unique constraints at it, but it's unnecessary.
I'd still advise people to follow the rules of normalisation unless there are very strong to deviate.