Databases
StackOverflow2013
Postsdbo
PODefine One to Many Relationships with SQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODefine One to Many Relationships with SQL
    primarykey
    data
    text
    <p>I'm looking for a way to set up a one to many relationship between 2 tables. The table structures is explained below but I've tried to leave everything off that has nothing to do with the problem.</p> <p>Table <code>objects</code> has 1 column called <code>uuid</code>.</p> <p>Table <code>contents</code> has 3 columns called <code>content</code>, <code>object_uuid</code> and <code>timestamp</code>.</p> <p>The basic idea is to insert a row into <code>objects</code> and get a new <code>uuid</code> from the database. This <code>uuid</code> is then used stored for every row in <code>contents</code> to associate contents with objects.</p> <p>Now I'm trying to use the database to enforce that:</p> <ul> <li>Each row in <code>contents</code> references a row in <code>objects</code> (a foreign key should do)</li> <li>No row in <code>objects</code> exists without at least a <code>row</code> in <code>contents</code></li> </ul> <p>These constraints should be enforced on commit of transactions.</p> <p>Ordinary triggers can't help probably because when a row in the <code>objects</code> table is written, there can't be a row in <code>contents</code> yet. Postgres does have so called <code>constraint triggers</code> that can be deferred until the end of the transaction. It would be possible to use those but they seem to be some sort of internal construct not intended for everyday use.</p> <p>Ideas or solutions should be standard SQL (preferred) or work with Postgres (version does not matter). Thanks for any input.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USAugustus Kling
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODefine One to Many Relationships with SQL
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODefine One to Many Relationships with SQL
      1. USBruno
      1. VTFavorite
    3. VO
      singulars
      1. PODefine One to Many Relationships with SQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe primary key for "contents" is {object_uuid,the_timestamp} ? BTW *why* do you want a separate "objects" table; IMHO it serves only as a domain for contents->object_uuid. BTW2: the constraint you want *can* be implemented with rules (is that allowed, since it is postgres-specific?), but there still is a problem with supplying a value for "content", on insertion of a fresh row. (I presume now() is good enough as an initializer for the_timestamp)
      singulars
      1. PODefine One to Many Relationships with SQL
      1. USwildplasser
    2. CO@wildplasser The reason for multiple tables is that there can be any number of contents and the actual tables contain more stuff than the example in this question. The solution outlined below solved the problem. Thanks for your time.
      singulars
      1. PODefine One to Many Relationships with SQL
      1. USAugustus Kling
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload