title: SQL table referencing multiple foreign keys, while defining order and guaranteeing referential integrity
body: <p>My customer has specified, that he needs different actions to be executed, when certain conditions in an internal logic are met. Each action type (so far <code>Command</code> and <code>WriteVariable</code>) has an individual set of specific information, and therefore needs to be stored in an individual table. The user must be able to define the order in which the actions are to be executed.</p>
  
  <p>I have the following setup for a Database:</p>
  
  <pre><code>LogicTable
  * OutputID
  * Description
  
  OutputTable &lt;== a pure relational table
  * OutputID
  * LogicID
  * ActionID &lt;== this references one of the action tables (Command/WriteVariable)
  * ActionTypeID
  * Sequence
  
  ActionTypeTable
  * ActionTypeID
  * Description
  
  CommandTable
  * CommandID  &lt;== corresponds to ActionID in OutputTable
  three columns with further command-specific information
  
  WriteVariableTable
  * WriteVariableID  &lt;== corresponds to ActionID in OutputTable
  four columns with further write-variable-specific information
  </code></pre>
  
  <p>My problem is that I cannot have multiple relational tables, because I cannot guarantee the sequence of the actions across multiple tables. I cannot have multiple columns with foreign-keys to each individual action in the output table (customer requirement). With the above setup I cannot have referential integrity, causing possible <code>ConfigurationException</code> in my application due to foreign-key entries w/out corresponding primary key entry.</p>
  
  <p>Is there a design that will enable referential integrity and manage to guarantee the order of referenced actions?</p>