Databases
StackOverflow2013
Postsdbo
POSQL foreign keys with multiple tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL foreign keys with multiple tables
    primarykey
    data
    text
    <p>If I have three tables:</p> <pre><code>music_genres ----------- music_type_id genres [other unique fields] minerals -------- mineral_id mineral [other unique fields] verbs ----- verb_id verbs [other unique fields] </code></pre> <p>and these are populated with:</p> <pre><code>rock jazz funk punk rock boulder stone shale rock wobble shake vibrate </code></pre> <p>Now let's say I was displaying a web page for each of these items, each using a separate template, and stored this information in the following table:</p> <pre><code>pages ----- page_id page_url template_url foreign_key_id </code></pre> <p>with data such as:</p> <pre><code>/page/music-genres/rock/ /music-genres-template.html 1 /page/verbs/rock/ /verb-template.html 1 /page/minerals/rock/ /mineral-template.html 1 /page/minerals/rock/images/ /mineral-images-template.html 1 </code></pre> <p>The templates will know that the a foreign key relates to the specific related table, so the mineral template with know to query the minerals table for that table's other fields. However, I don't have any referential integrity. If rock is deleted from the minerals table, I can't use cascading deletes to remove rows from pages, as foreign keys aren't unique. I can think of numerous ways to get around this inelegantly:</p> <ul> <li>Emulating cascading deletes with triggers</li> <li>Storing the item type in each row of the music_types, minerals and verbs tables, and using this a an additional foreign key</li> <li>Storing the appropriate table name in the pages table</li> <li>Maintaining the integrity of the database with PHP etc.</li> </ul> <p>I'm thinking that there must be a better way of either storing my data or maintaining the integrity of the database. Any thoughts?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAPC
    1. USjetboy
    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
    1. VO
      singulars
      1. POSQL foreign keys with multiple tables
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSo you have some some that you don't show that generates the primary key that these foreign keys relate to? So the same key for 'rock' is used in all three tables?
      singulars
      1. POSQL foreign keys with multiple tables
      1. USJames Black
    2. COExactly. In this case a MySQL autoincrement column. The scenario is obviously hypothetical, but illustrates that I can't rely on another column to be unique across all three tables that can be used to make a compound key.
      singulars
      1. POSQL foreign keys with multiple tables
      1. USjetboy
 

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