3 ms·

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?

dragonwriter 4y ago
> 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.
- qazxcvbnm 4y ago
  Something like this (adjust the primary key name convention and schema as needed): 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; $$;

dragonwriter 4y ago

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

qazxcvbnm 4y ago
Something like this (adjust the primary key name convention and schema as needed): 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; $$;