Databases
StackOverflow2013
Postsdbo
POForeign key vs check constraint for integrity
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POForeign key vs check constraint for integrity
    primarykey
    data
    text
    <p>I am building a system that is a central repository for storing data from a number of other systems. A sync process is required to update the central repository when the other systems data is updated. There will be a sync_action table to identify which system the central repo needs to sync with and the type of sync required. There are set of defined actions that is very unlikely to change. A slimmed down system is below.</p> <p>As I see it I can approach this in two ways:</p> <p><strong>Option 1</strong>) Have an <code>Action</code> table that has the 3 actions available. Have a <code>sync_action</code> table which uses a foreign key to reference the actions required.</p> <p>Table: <strong>System</strong></p> <pre><code>ID Description 1 Slave System 1 2 Slave System 2 </code></pre> <p>Table: <strong>Action</strong></p> <pre><code>ID Description 1 Insert 2 Update 3 Delete </code></pre> <p>Table: <strong>Sync_action</strong></p> <pre><code>ID Action System 1 1 1 2 2 1 </code></pre> <p><strong>Option 2</strong>) Instead of a foreign key use a check constraint on the <code>sync_action.action</code> column so only the actions <code>Insert/Update/Delete</code> can be inserted.</p> <p>Table: <strong>Sync_action</strong></p> <pre><code>ID Action System 1 Insert 1 2 Update 1 </code></pre> <p>I would like to know what factors go into determining which is a better approach when deciding between integrity constraints, foreign key vs check constraint. There have been similar threads but I didn't find them definitive enough. This may be because its up to interpretation but any thoughts would be appreciated.</p> <p>Cheers</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USskaffman
    1. USJeremy Power
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POForeign key vs check constraint for integrity
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POForeign key vs check constraint for integrity
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POForeign key vs check constraint for integrity
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI generally prefer FKs and table-relationships because: they are "more visible" (e.g. show in the relationship diagrams) and they are "more extendible" (depend on data, not condition, and can have "extra" information attached) and they are often "more mappable" (to whatever ORM you choose). In cases like this I *also* consider using a non-surrogate PK (it really is a "static data" table), perhaps just a "single meaningful character" such as "I", "U", "D". If in another table then could also have "self documenting" label columns.
      singulars
      1. POForeign key vs check constraint for integrity
      1. This table or related slice is empty.
    2. COWelcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it!
      singulars
      1. POForeign key vs check constraint for integrity
      1. USmarc_s
    3. COI agree with `pst` - what if you need to add a fourth, fifth action?? If you have a separate `Action` table, it's as simple as adding a row. If you have check constraints, you need to go drop and recreate those - that's more work, and more hassle. I don't see any good arguments **against** having a separate `Action` table and enforcing referential integrity using a FK constraint - databases are good at that! (that's their core business!)
      singulars
      1. POForeign key vs check constraint for integrity
      1. USmarc_s
 

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