Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>From experience I have to tell you this design is horrible to work with. Consider changing the design to copy the data to an 'audit trail' table then physically remove it from the main table. </p> <p>If you won't consider this, at the very least bury this in a <code>VIEW</code> and do everything you can to avoid exposing this to anyone wanting to query the database, using <code>INSTEAD OF</code> triggers on the <code>VIEW</code> if necessary. Otherwise, expect applications to have frequent bugs because someone forgot to add the <code>AND isremoved = 0</code> predicate required by <em>every</em> query that uses this table.</p> <blockquote> <p>But this 'answer' doesn't address the real question.</p> </blockquote> <p>Yes. Sorry 'bout that. But sometimes you have to cure the disease rather than merely treat the symptoms. </p> <p>The design is compromised: a table <em>should</em> model a single entity type, whereas this is modelling two. How can I tell? Because the OP has stated that once 'removed' the entity has different data requirement, by saying "The problem is ... how can we check that all its child table not referring this record".</p> <p>So the 'real' answer is: move the entity to another distinct table.</p> <p>But if you are in the business of treating symptoms then here's an answer:</p> <ol> <li>Add the <code>IsRemoved</code> column to your so-called 'child' tables, with <code>DEFAULT false</code> and ensure it is <code>NOT NULL</code>.</li> <li>Add a <code>CHECK</code> constraint to each so-called 'child' table to test <code>isremoved = false</code> (or whatever 'boolean' means in your SQL product).</li> <li>Add a compound key (e.g. using <code>UNIQUE</code> or <code>PRIMARY KEY</code>) to your so-called 'master' table on <code>(IsRemoved, &lt;existing key columns here&gt;)</code>, or alter an existing key accordingly.</li> <li>Add <code>FOREIGN KEY</code> constraints to each so-called 'child' table to reference the compound key created above.</li> </ol>
    singulars
    1. This table or related slice is empty.
    1. POHow to check that a record of master table is being used/referenced by child table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USonedaywhen
    1. USonedaywhen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to check that a record of master table is being used/referenced by child table
      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. CO+1 for the view to alleviate select concerns.
      singulars
      1. PO
      1. UScethegeek
    2. COthis "answer" doesn't address the real question.
      singulars
      1. PO
      1. USthepaulpage
    3. COWe're trying to tell a user that the record that they are viewing is un-deletable. You know, put a visible flag somewhere that explains "you can't delete this, because you are using it" It would be nicer if we could tell them that it won't work, before we let them click a delete button. I dunno, maybe it's impossible, but it is what the question is asking.
      singulars
      1. PO
      1. USthepaulpage
 

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