4 ms·

Both tables have an ID primary key, one is also a FOREIGN KEY to the other.

fweimer 4y ago
Doesn't this require support for deferrable constraints? Even if they are supported on the transaction level, once you have these kinds of dependency loops, they tend to impact lots and lots of things—restoring from backups, application-level replication, and so on.
adamzochowski 4y ago
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; $$;
petepete 4y ago
That's a 1:N relationship. There's nothing to prevent you from adding more related records. 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.
- crgwbr 4y ago
  If the foreign key column is also that table’s primary key (or just has a unique constraint), then that’s not 1:N, it’s 1:1 or 1:0.
  - petepete 4y ago
    Yeah, I noted elsewhere among the replies it's possible to use constraints to prevent extra rows in the related table, but it's inelegant and I've seen plenty of cases where it's forgotten and ends up being buggy. I'd still advise people to follow the rules of normalisation unless there are very strong to deviate.

Doesn't this require support for deferrable constraints? Even if they are supported on the transaction level, once you have these kinds of dependency loops, they tend to impact lots and lots of things—restoring from backups, application-level replication, and so on.

adamzochowski 4y ago

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; $$;

petepete 4y ago

That's a 1:N relationship. There's nothing to prevent you from adding more related records. 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.

crgwbr 4y ago
If the foreign key column is also that table’s primary key (or just has a unique constraint), then that’s not 1:N, it’s 1:1 or 1:0.
- petepete 4y ago
  Yeah, I noted elsewhere among the replies it's possible to use constraints to prevent extra rows in the related table, but it's inelegant and I've seen plenty of cases where it's forgotten and ends up being buggy. I'd still advise people to follow the rules of normalisation unless there are very strong to deviate.