title: Class Table Inheritance (CTI) and Insert Select statements
body: <p>I am working to build an inheritance database model within MySQL, such that all tables inherit from one base type (object), represented by the <code>object</code> table.  This allows for Notes to be linked to any object from any table within the database while retaining referential integrity.  The design looks something like this (there are a lot more child tables with similar structures):</p>
  
  <pre><code>CREATE TABLE object
  (
  object_id INT(10) AUTO_INCREMENT,
  object_type VARCHAR(80),
  PRIMARY KEY (object_id)
  );
  
  CREATE TABLE person
  (
  person_id INT(10),
  name_first VARCHAR(80),
  name_last VARCHAR(80),
  email_address VARCHAR(80),
  PRIMARY KEY (person_id),
  CONSTRAINT fk_person FOREIGN KEY (person_id)
  REFERENCES object (object_id)
  );
  
  CREATE TABLE note
  (
  note_id INT(10),
  not_text TEXT,
  note_subject_id INT(10),
  PRIMARY KEY (note_id),
  CONSTRAINT fk_note FOREIGN KEY (note_id)
  REFERENCES object (object_id),
  CONSTRAINT fk_note_subject FOREIGN KEY (note_subject_id)
  REFERENCES object (object_id)
  );
  </code></pre>
  
  <p>With this design, I am able to make a note with a person as the subject, a note with another note as the subject, or a note with one of the many other tables inheriting from object as a subject (these tables are not listed for brevity). Although it cannot be enforced through referential integrity, a presupposition of this design is that each object_id is used in only one row of one child table, so that there are no notes where the note_id is also a person_id.</p>
  
  <p><strong>The problem occurs when I want to perform <code>INSERT... SELECT</code> statement on person or note</strong>.  Let's say that I have a <code>user</code> table and I would like to insert all users in to <code>person</code>.  First I have to insert the number of new <code>person</code> rows I am creating into <code>object</code>, then I need to insert the new rows into <code>person</code>, but I have no way of matching each <code>user</code> row to an <code>object</code> row in order to populate the person_id column.</p>
  
  <p>My first thought was to create a <code>BEFORE INSERT TRIGGER</code> on <code>person</code> that would create the new <code>object</code> record and update the <code>NEW.person_id</code> value accordingly.  Unfortunately, the foreign key constraint is being evaluated before the trigger is allowed to fire, catching my orphaned row before I can correct it.  </p>
  
  <p>What I am looking for is either a way to change the order of constraint execution so that BEFORE INSERT triggers precede constraints, or for a more elegant way to achieve an Class Table Inheritance database structure within MySQL.</p>