4 ms·

Even with no ORM involved, 1-1 relations help keep things conceptually separate. This makes e.g spinning off parts of your application as microservices much eas

• petepete 4y ago
  How do you enforce the 1:1 relationship at the database level?
  • vasachi 4y ago
    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.
  • giovannibonetti 4y ago
    With an unique constraint/index
    • petepete 4y ago
      Yes, I mentioned the approach elsewhere. Now you have two database things (the FK and the constraint) describing a single relationship. I don't think it's very elegant and people often forget the unique constraint. One table is a much better solution.
  • contravariant 4y ago
    If you could it would become impossible to insert any data in them.