Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <p>Logically, this would be an identifying relationship, since a form field cannot exist without a form.</p> </blockquote> <p>No, identifying relationship is about identification, not existence.</p> <p>Any X:Y relationship where X >= 1 guarantees existence of the left side, whether identifying or not. In your case, a 1:N relationship guarantees existence of <code>form</code> for any given <code>form_field</code>. You could make it identifying or non-identifying and it would still guarantee the same.</p> <p>Remarks:</p> <ul> <li>You would model an identifying relationship by making <code>form_field.form_id</code> part of a key. For example <code>form_field</code> PK could look like: <code>{form_id, label}</code>, which BTW would be quite beneficial for proper <a href="http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index">clustering</a> of your data (InnoDB tables are <a href="http://www.ovaistariq.net/521/understanding-innodb-clustered-indexes/">always clustered</a>).</li> <li>Just making a PK: <code>{id, form_id}</code> would be incorrect, since this superkey is not a candidate key (i.e. it is not minimal - we could remove <code>form_id</code> from it and still retain the uniqueness).</li> <li>You would model a 0..1:N relationship by making the <code>form_field.form_id</code> NULL-able (but then you wouldn't be able to make it identifying as well - see below).</li> </ul> <p>There are two definitions of the "identifying relationship":</p> <ul> <li><strong>Strict definition</strong>: A relationship that migrates parent key into child primary key<sup>1</sup>.</li> <li><strong>Loose definition</strong>: A relationship that migrates parent key into child key.</li> </ul> <p>In other words, the loose definition allows migration into alternate key as well (and not just primary).</p> <p>Most tools<sup>2</sup> seem to use the strict definition though, so if you mark the relationship as identifying, that will automatically make the migrated attributes part of the child PK, and none of the PK attributes can be NULL.</p> <hr> <p><em><sup>1</sup> Which is then either completely comprised from migrated attributes, or is a combination of migrated attributes and some additional attributes.</em></p> <p><em><sup>2</sup> ERwin and Visio do. I haven't used MySQL Workbench for modeling yet, but your description seems to suggest it behaves the same.</em></p>
    singulars
    1. This table or related slice is empty.
    1. POWhy is the foreign key part of the primary key in an identifying relationship?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBranko Dimitrijevic
    1. USBranko Dimitrijevic
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhy is the foreign key part of the primary key in an identifying relationship?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSo, if I understand you correctly: - The idea is that the primary key consists of two or more columns. - One of them is the foreign key to the parent table - The other is a column that is unique within all records that share the foreign key to the parent table, but (possibly) not within all records of the table - All of this is simply an alternative to an auto-incremented ID column.
      singulars
      1. PO
      1. USNic Wortel
    2. CO@NicNLD I think you got it, except if natural key exists, it's not strictly an "alternative" to the surrogate key - **if** a label needs to be unique per form, then you'd have to make a key `{form_id, label}` **in any case**. The only question is whether you'll **also** make a surrogate key `id` (there are pros and cons for that, as you can imagine).
      singulars
      1. PO
      1. USBranko Dimitrijevic
    3. COOK, I think I'm beginning to understand this. I'd have to make the key `{form_id, label}` to ensure there are not two duplicate labels in one form, right? But what still confuses me is that [this answer](http://stackoverflow.com/a/762994/1001110) has 164 upvotes but is telling something completely different. (although it doesn't explain **why** the PK of the parent should be part of the child's PK)
      singulars
      1. PO
      1. USNic Wortel
 

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