initial support for optionally enforcing foreign-key constraints (fixes feature request #22)

git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@655 f203690c-595d-4dc9-a70b-905162fa7fd2

7 files changed, 708 insertions, 75 deletions

diff --git a/src/changes/changes.xml b/src/changes/changes.xml
index 6e241d2..addd5d1 100644
--- a/src/changes/changes.xml
+++ b/src/changes/changes.xml
@@ -8,6 +8,12 @@
       <action dev="jahlborn" type="update">
         Add info to the Column to support MEMO columns which are HYPERLINKS.
       </action>
+      <action dev="jahlborn" type="fix" system="SourceForge2" issue="22">
+        Add optional support for enforcing foreign-key constraints/cascading.
+        This is disabled by default (for backwards compatibility), but can be
+        controlled globally via a system property and/or on a per-Database
+        basis using setEnforceForeignKeys() method.
+      </action>
     </release>
     <release version="1.2.9" date="2012-10-15">
       <action dev="jahlborn" type="update">
diff --git a/src/java/com/healthmarketscience/jackcess/Database.java b/src/java/com/healthmarketscience/jackcess/Database.java
index 5f3505a..d9fe435 100644
--- a/src/java/com/healthmarketscience/jackcess/Database.java
+++ b/src/java/com/healthmarketscience/jackcess/Database.java
@@ -164,6 +164,13 @@ public class Database
   public static final String COLUMN_ORDER_PROPERTY = 
     "com.healthmarketscience.jackcess.columnOrder";
 
+  /** system property which can be used to set the default enforcement of
+   * foreign-key relationships.  Defaults to {@code false}.
+   * @usage _general_field_
+   */
+  public static final String FK_ENFORCE_PROPERTY = 
+    "com.healthmarketscience.jackcess.enforceForeignKeys";
+
   /**
    * default error handler used if none provided (just rethrows exception)
    * @usage _general_field_
@@ -462,6 +469,8 @@ public class Database
   private Short _defaultCodePage;
   /** the ordering used for table columns */
   private Table.ColumnOrder _columnOrder;
+  /** whether or not enforcement of foreign-keys is enabled */
+  private boolean _enforceForeignKeys;
   /** cache of in-use tables */
   private final TableCache _tableCache = new TableCache();
   /** handler for reading/writing properteies */
@@ -478,7 +487,9 @@ public class Database
   private LinkResolver _linkResolver;
   /** any linked databases which have been opened */
   private Map<String,Database> _linkedDbs;
-
+  /** shared state used when enforcing foreign keys */
+  private final FKEnforcer.SharedState _fkEnforcerSharedState =
+    FKEnforcer.initSharedState();
 
   /**
    * Open an existing Database.  If the existing file is not writeable, the
@@ -894,6 +905,7 @@ public class Database
     _format = JetFormat.getFormat(channel);
     _charset = ((charset == null) ? getDefaultCharset(_format) : charset);
     _columnOrder = getDefaultColumnOrder();
+    _enforceForeignKeys = getDefaultEnforceForeignKeys();
     _fileFormat = fileFormat;
     _pageChannel = new PageChannel(channel, closeChannel, _format, autoSync);
     _timeZone = ((timeZone == null) ? getDefaultTimeZone() : timeZone);
@@ -1096,6 +1108,33 @@ public class Database
   }
 
   /**
+   * Gets currently foreign-key enforcement policy.
+   * @usage _intermediate_method_
+   */
+  public boolean isEnforceForeignKeys() {
+    return _enforceForeignKeys;
+  }
+
+  /**
+   * Sets a new foreign-key enforcement policy.  If {@code null}, resets to
+   * the value returned by {@link #isEnforceForeignKeys}.
+   * @usage _intermediate_method_
+   */
+  public void setEnforceForeignKeys(Boolean newEnforceForeignKeys) {
+    if(newEnforceForeignKeys == null) {
+      newEnforceForeignKeys = getDefaultEnforceForeignKeys();
+    }
+    _enforceForeignKeys = newEnforceForeignKeys;
+  }
+
+  /**
+   * @usage _advanced_method_
+   */
+  FKEnforcer.SharedState getFKEnforcerSharedState() {
+    return _fkEnforcerSharedState;
+  }
+
+  /**
    * @returns the current handler for reading/writing properties, creating if
    * necessary
    */
@@ -2221,6 +2260,21 @@ public class Database
   }
   
   /**
+   * Returns the default enforce foreign-keys policy.  This defaults to
+   * {@code false}, but can be overridden using the system
+   * property {@value #FK_ENFORCE_PROPERTY}.
+   * @usage _advanced_method_
+   */
+  public static boolean getDefaultEnforceForeignKeys()
+  {
+    String prop = System.getProperty(FK_ENFORCE_PROPERTY);
+    if(prop != null) {
+      return Boolean.TRUE.toString().equalsIgnoreCase(prop);
+    }
+    return false;
+  }
+  
+  /**
    * Copies the given InputStream to the given channel using the most
    * efficient means possible.
    */
diff --git a/src/java/com/healthmarketscience/jackcess/FKEnforcer.java b/src/java/com/healthmarketscience/jackcess/FKEnforcer.java
new file mode 100644
index 0000000..b5ce3ec
--- /dev/null
+++ b/src/java/com/healthmarketscience/jackcess/FKEnforcer.java
@@ -0,0 +1,314 @@
+/*
+Copyright (c) 2012 James Ahlborn
+
+This library is free software; you can redistribute it and/or
+modify it under the terms of the GNU Lesser General Public
+License as published by the Free Software Foundation; either
+version 2.1 of the License, or (at your option) any later version.
+
+This library is distributed in the hope that it will be useful,
+but WITHOUT ANY WARRANTY; without even the implied warranty of
+MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+Lesser General Public License for more details.
+
+You should have received a copy of the GNU Lesser General Public
+License along with this library; if not, write to the Free Software
+Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307
+USA
+*/
+
+package com.healthmarketscience.jackcess;
+
+import java.io.IOException;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.TreeSet;
+
+
+/**
+ * Utility class used by Table to enforce foreign-key relationships (if
+ * enabled).
+ *
+ * @author James Ahlborn
+ * @usage _advanced_class_
+ */
+final class FKEnforcer 
+{
+  // fk constraints always work with indexes, which are always
+  // case-insensitive
+  private static final ColumnMatcher MATCHER =
+    CaseInsensitiveColumnMatcher.INSTANCE;
+
+  private final Table _table;
+  private final List<Column> _cols;
+  private List<Joiner> _primaryJoinersChkUp;
+  private List<Joiner> _primaryJoinersChkDel;
+  private List<Joiner> _primaryJoinersDoUp;
+  private List<Joiner> _primaryJoinersDoDel;
+  private List<Joiner> _secondaryJoiners;
+
+  FKEnforcer(Table table) {
+    _table = table;
+
+    // at this point, only init the index columns
+    Set<Column> cols = new TreeSet<Column>();
+    for(Index idx : _table.getIndexes()) {
+      Index.ForeignKeyReference ref = idx.getReference();
+      if(ref != null) {
+        // compile an ordered list of all columns in this table which are
+        // involved in foreign key relationships with other tables
+        for(IndexData.ColumnDescriptor iCol : idx.getColumns()) {
+          cols.add(iCol.getColumn());
+        }
+      }
+    }
+    _cols = !cols.isEmpty() ?
+      Collections.unmodifiableList(new ArrayList<Column>(cols)) :
+      Collections.<Column>emptyList();
+  }
+
+  /**
+   * Does secondary initialization, if necessary.
+   */
+  private void initialize() throws IOException {
+    if(_secondaryJoiners != null) {
+      // already initialized
+      return;
+    }
+
+    // initialize all the joiners
+    _primaryJoinersChkUp = new ArrayList<Joiner>(1);
+    _primaryJoinersChkDel = new ArrayList<Joiner>(1);
+    _primaryJoinersDoUp = new ArrayList<Joiner>(1);
+    _primaryJoinersDoDel = new ArrayList<Joiner>(1);
+    _secondaryJoiners = new ArrayList<Joiner>(1);
+
+    for(Index idx : _table.getIndexes()) {
+      Index.ForeignKeyReference ref = idx.getReference();
+      if(ref != null) {
+
+        Joiner joiner = Joiner.create(idx);
+        if(ref.isPrimaryTable()) {
+          if(ref.isCascadeUpdates()) {
+            _primaryJoinersDoUp.add(joiner);
+          } else {
+            _primaryJoinersChkUp.add(joiner);
+          }
+          if(ref.isCascadeDeletes()) {
+            _primaryJoinersDoDel.add(joiner);
+          } else {
+            _primaryJoinersChkDel.add(joiner);
+          }
+        } else {
+          _secondaryJoiners.add(joiner);
+        }
+      }
+    }
+  }
+
+  /**
+   * Handles foregn-key constraints when adding a row.
+   *
+   * @param row new row in the Table's row format, including all values used
+   *            in any foreign-key relationships
+   */
+  public void addRow(Object[] row) throws IOException {
+    if(!enforcing()) {
+      return;
+    }
+    initialize();
+
+    for(Joiner joiner : _secondaryJoiners) {
+      requirePrimaryValues(joiner, row);
+    }
+  }
+
+  /**
+   * Handles foregn-key constraints when updating a row.
+   *
+   * @param oldRow old row in the Table's row format, including all values
+   *               used in any foreign-key relationships
+   * @param newRow new row in the Table's row format, including all values
+   *               used in any foreign-key relationships
+   */
+  public void updateRow(Object[] oldRow, Object[] newRow) throws IOException {
+    if(!enforcing()) {
+      return;
+    }
+
+    if(!anyUpdates(oldRow, newRow)) {
+      // no changes were made to any relevant columns
+      return;
+    }
+
+    initialize();
+
+    SharedState ss = _table.getDatabase().getFKEnforcerSharedState();
+    
+    if(ss.isUpdating()) {
+      // we only check the primary relationships for the "top-level" of an
+      // update operation.  in nested levels we are only ever changing the fk
+      // values themselves, so we always know the new values are valid.
+      for(Joiner joiner : _secondaryJoiners) {
+        if(anyUpdates(joiner, oldRow, newRow)) {
+          requirePrimaryValues(joiner, newRow);
+        }
+      }
+    }
+
+    ss.pushUpdate();
+    try {
+
+      // now, check the tables for which we are the primary table in the
+      // relationship (but not cascading)
+      for(Joiner joiner : _primaryJoinersChkUp) {
+        if(anyUpdates(joiner, oldRow, newRow)) {
+          requireNoSecondaryValues(joiner, oldRow);
+        }
+      }
+
+      // lastly, update the tables for which we are the primary table in the
+      // relationship
+      for(Joiner joiner : _primaryJoinersDoUp) {
+        if(anyUpdates(joiner, oldRow, newRow)) {
+          updateSecondaryValues(joiner, oldRow, newRow);
+        }
+      }
+
+    } finally {
+      ss.popUpdate();
+    }
+  }
+
+  /**
+   * Handles foregn-key constraints when deleting a row.
+   *
+   * @param row old row in the Table's row format, including all values used
+   *            in any foreign-key relationships
+   */
+  public void deleteRow(Object[] row) throws IOException {
+    if(!enforcing()) {
+      return;
+    }
+    initialize();
+
+    // first, check the tables for which we are the primary table in the
+    // relationship (but not cascading)
+    for(Joiner joiner : _primaryJoinersChkDel) {
+      requireNoSecondaryValues(joiner, row);
+    }
+
+    // lastly, delete from the tables for which we are the primary table in
+    // the relationship
+    for(Joiner joiner : _primaryJoinersDoDel) {
+      joiner.deleteRows(row);
+    }
+  }
+
+  private static void requirePrimaryValues(Joiner joiner, Object[] row) 
+    throws IOException 
+  {
+    // ensure that the relevant rows exist in the primary tables for which
+    // this table is a secondary table.
+    if(!joiner.hasRows(row)) {
+      throw new IOException("Adding new row " + Arrays.asList(row) + 
+                            " violates constraint " + joiner.toFKString());
+    }
+  }
+
+  private static void requireNoSecondaryValues(Joiner joiner, Object[] row) 
+    throws IOException 
+  {
+    // ensure that no rows exist in the secondary table for which this table is
+    // the primary table.
+    if(joiner.hasRows(row)) {
+      throw new IOException("Removing old row " + Arrays.asList(row) + 
+                            " violates constraint " + joiner.toFKString());
+    }
+  }
+
+  private static void updateSecondaryValues(Joiner joiner, Object[] oldFromRow,
+                                            Object[] newFromRow)
+    throws IOException
+  {
+    IndexCursor toCursor = joiner.getToCursor();
+    List<IndexData.ColumnDescriptor> fromCols = joiner.getColumns();
+    List<IndexData.ColumnDescriptor> toCols = joiner.getToIndex().getColumns();
+    Object[] toRow = new Object[joiner.getToTable().getColumnCount()];
+
+    for(Iterator<Map<String,Object>> iter = joiner.findRows(
+            oldFromRow, Collections.<String>emptySet()); iter.hasNext(); ) {
+      iter.next();
+
+      // create update row for "to" table
+      Arrays.fill(toRow, Column.KEEP_VALUE);
+      for(int i = 0; i < fromCols.size(); ++i) {
+        Object val = fromCols.get(i).getColumn().getRowValue(newFromRow);
+        toCols.get(i).getColumn().setRowValue(toRow, val);
+      }
+
+      toCursor.updateCurrentRow(toRow);
+    }
+  }
+
+  private boolean anyUpdates(Object[] oldRow, Object[] newRow) {
+    for(Column col : _cols) {
+      if(!MATCHER.matches(_table, col.getName(),
+                          col.getRowValue(oldRow), col.getRowValue(newRow))) {
+        return true;
+      }
+    }
+    return false;
+  }
+
+  private static boolean anyUpdates(Joiner joiner,Object[] oldRow, 
+                                    Object[] newRow)
+  {
+    Table fromTable = joiner.getFromTable();
+    for(IndexData.ColumnDescriptor iCol : joiner.getColumns()) {
+      Column col = iCol.getColumn();
+      if(!MATCHER.matches(fromTable, col.getName(),
+                          col.getRowValue(oldRow), col.getRowValue(newRow))) {
+        return true;
+      }
+    }
+    return false;
+  }
+
+  private boolean enforcing() {
+    return _table.getDatabase().isEnforceForeignKeys();
+  }
+
+  static SharedState initSharedState() {
+    return new SharedState();
+  }
+
+  /**
+   * Shared state used by all FKEnforcers for a given Database.
+   */
+  static final class SharedState 
+  {
+    /** current depth of cascading update calls across one or more tables */
+    private int _updateDepth;
+
+    private SharedState() {
+    }
+
+    public boolean isUpdating() {
+      return (_updateDepth == 0);
+    }
+    
+    public void pushUpdate() {
+      ++_updateDepth;
+    }
+
+    public void popUpdate() {
+      --_updateDepth;
+    }
+  }
+}
diff --git a/src/java/com/healthmarketscience/jackcess/IndexData.java b/src/java/com/healthmarketscience/jackcess/IndexData.java
index c51a016..2c24a2d 100644
--- a/src/java/com/healthmarketscience/jackcess/IndexData.java
+++ b/src/java/com/healthmarketscience/jackcess/IndexData.java
@@ -566,8 +566,7 @@ public abstract class IndexData {
           newEntry.equalsEntryBytes(nextPos.getEntry())) ||
           ((prevPos != null) &&
            newEntry.equalsEntryBytes(prevPos.getEntry())));
-      if(isUnique() && !isNullEntry && isDupeEntry)
-      {
+      if(isUnique() && !isNullEntry && isDupeEntry) {
         throw new IOException(
             "New row " + Arrays.asList(row) +
             " violates uniqueness constraint for index " + this);
diff --git a/src/java/com/healthmarketscience/jackcess/Joiner.java b/src/java/com/healthmarketscience/jackcess/Joiner.java
index 8e5bf7c..dc3f4ba 100644
--- a/src/java/com/healthmarketscience/jackcess/Joiner.java
+++ b/src/java/com/healthmarketscience/jackcess/Joiner.java
@@ -90,31 +90,31 @@ public class Joiner
     return create(getToTable(), getFromTable());
   }
   
-  public Table getFromTable()
-  {
+  public Table getFromTable() {
     return getFromIndex().getTable();
   }
   
-  public Index getFromIndex()
-  {
+  public Index getFromIndex() {
     return _fromIndex;
   }
   
-  public Table getToTable()
-  {
+  public Table getToTable() {
     return getToCursor().getTable();
   }
   
-  public Index getToIndex()
-  {
+  public Index getToIndex() {
     return getToCursor().getIndex();
   }
   
-  public IndexCursor getToCursor()
-  {
+  public IndexCursor getToCursor() {
     return _toCursor;
   }
 
+  public List<IndexData.ColumnDescriptor> getColumns() {
+    // note, this list is already unmodifiable, no need to re-wrap
+    return _fromCols;
+  }
+
   /**
    * Returns {@code true} if the "to" table has any rows based on the given
    * columns in the "from" table, {@code false} otherwise.
@@ -125,6 +125,15 @@ public class Joiner
   }
 
   /**
+   * Returns {@code true} if the "to" table has any rows based on the given
+   * columns in the "from" table, {@code false} otherwise.
+   */
+  boolean hasRows(Object[] fromRow) throws IOException {
+    toEntryValues(fromRow);
+    return _toCursor.findFirstRowByEntry(_entryValues);
+  }
+
+  /**
    * Returns the first row in the "to" table based on the given columns in the
    * "from" table if any, {@code null} if there is no matching row.
    *
@@ -181,6 +190,21 @@ public class Joiner
   }
 
   /**
+   * Returns an Iterator with the selected columns over all the rows in the
+   * "to" table based on the given columns in the "from" table.
+   *
+   * @param fromRow row from the "from" table (which must include the relevant
+   *                columns for this join relationship)
+   * @param columnNames desired columns in the from table row
+   */
+  Iterator<Map<String,Object>> findRows(Object[] fromRow,
+                                        Collection<String> columnNames)
+  {
+    toEntryValues(fromRow);
+    return _toCursor.entryIterator(columnNames, _entryValues);
+  }
+
+  /**
    * Returns an Iterable whose iterator() method returns the result of a call
    * to {@link #findRows(Map)}
    * 
@@ -224,25 +248,89 @@ public class Joiner
    *         otherwise
    */
   public boolean deleteRows(Map<String,?> fromRow) throws IOException {
+    return deleteRowsImpl(findRows(fromRow, Collections.<String>emptySet()));
+  }
+
+  /**
+   * Deletes any rows in the "to" table based on the given columns in the
+   * "from" table.
+   * 
+   * @param fromRow row from the "from" table (which must include the relevant
+   *                columns for this join relationship)
+   * @return {@code true} if any "to" rows were deleted, {@code false}
+   *         otherwise
+   */
+  boolean deleteRows(Object[] fromRow) throws IOException {
+    return deleteRowsImpl(findRows(fromRow, Collections.<String>emptySet()));
+  }
+
+  /**
+   * Deletes all the rows and returns whether or not any "to"" rows were
+   * deleted.
+   */
+  private static boolean deleteRowsImpl(Iterator<Map<String,Object>> iter)
+    throws IOException 
+  {
     boolean removed = false;
-    for(Iterator<Map<String,Object>> iter = findRows(
-            fromRow, Collections.<String>emptySet()); iter.hasNext(); ) {
+    while(iter.hasNext()) {
       iter.next();
       iter.remove();
       removed = true;
     }
-    return removed;
+    return removed;    
   }
 
   /**
    * Fills in the _entryValues with the relevant info from the given "from"
    * table row.
    */
-  private void toEntryValues(Map<String,?> fromRow)
-  {
+  private void toEntryValues(Map<String,?> fromRow) {
     for(int i = 0; i < _entryValues.length; ++i) {
-      _entryValues[i] = fromRow.get(_fromCols.get(i).getName());
+      _entryValues[i] = _fromCols.get(i).getColumn().getRowValue(fromRow);
     }
   }
-  
+
+  /**
+   * Fills in the _entryValues with the relevant info from the given "from"
+   * table row.
+   */
+  private void toEntryValues(Object[] fromRow) {
+    for(int i = 0; i < _entryValues.length; ++i) {
+      _entryValues[i] = _fromCols.get(i).getColumn().getRowValue(fromRow);
+    }
+  }
+
+  /**
+   * Returns a pretty string describing the foreign key relationship backing
+   * this Joiner.
+   */
+  public String toFKString() {
+    StringBuilder sb = new StringBuilder();
+    sb.append("Foreign Key from ");
+
+    String fromType = "] (primary)";
+    String toType = "] (secondary)";
+    if(!_fromIndex.getReference().isPrimaryTable()) {
+      fromType = "] (secondary)";
+      toType = "] (primary)";
+    }
+
+    sb.append(getFromTable().getName()).append("[");
+
+    sb.append(_fromCols.get(0).getName());
+    for(int i = 1; i < _fromCols.size(); ++i) {
+      sb.append(",").append(_fromCols.get(i).getName());
+    }
+    sb.append(fromType);
+    
+    sb.append(" to ").append(getToTable().getName()).append("[");
+    List<IndexData.ColumnDescriptor> toCols = _toCursor.getIndex().getColumns();
+    sb.append(toCols.get(0).getName());
+    for(int i = 1; i < toCols.size(); ++i) {
+      sb.append(",").append(toCols.get(i).getName());
+    }
+    sb.append(toType);
+    
+    return sb.toString();
+  }
 }
diff --git a/src/java/com/healthmarketscience/jackcess/Table.java b/src/java/com/healthmarketscience/jackcess/Table.java
index 6a6b807..67b31f9 100644
--- a/src/java/com/healthmarketscience/jackcess/Table.java
+++ b/src/java/com/healthmarketscience/jackcess/Table.java
@@ -38,8 +38,10 @@ import java.util.Comparator;
 import java.util.HashMap;
 import java.util.Iterator;
 import java.util.LinkedHashMap;
+import java.util.LinkedHashSet;
 import java.util.List;
 import java.util.Map;
+import java.util.Set;
 
 import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
@@ -138,15 +140,17 @@ public class Table
   /** List of columns in this table, ordered by column number */
   private List<Column> _columns = new ArrayList<Column>();
   /** List of variable length columns in this table, ordered by offset */
-  private List<Column> _varColumns = new ArrayList<Column>();
+  private final List<Column> _varColumns = new ArrayList<Column>();
   /** List of autonumber columns in this table, ordered by column number */
   private List<Column> _autoNumColumns;
   /** List of indexes on this table (multiple logical indexes may be backed by
       the same index data) */
-  private List<Index> _indexes = new ArrayList<Index>();
+  private final List<Index> _indexes = new ArrayList<Index>();
   /** List of index datas on this table (the actual backing data for an
       index) */
-  private List<IndexData> _indexDatas = new ArrayList<IndexData>();
+  private final List<IndexData> _indexDatas = new ArrayList<IndexData>();
+  /** List of columns in this table which are in one or more indexes */
+  private final Set<Column> _indexColumns = new LinkedHashSet<Column>();
   /** Table name as stored in Database */
   private final String _name;
   /** Usage map of pages that this table owns */
@@ -179,6 +183,8 @@ public class Table
   private PropertyMap _props;
   /** properties group for this table (and columns) */
   private PropertyMaps _propertyMaps;
+  /** foreign-key enforcer for this table */
+  private final FKEnforcer _fkEnforcer;
 
   /** common cursor for iterating through the table, kept here for historic
       reasons */
@@ -197,6 +203,7 @@ public class Table
     _name = null;
     _useBigIndex = true;
     setColumns(columns);
+    _fkEnforcer = null;
   }
   
   /**
@@ -216,23 +223,8 @@ public class Table
     _name = name;
     _flags = flags;
     _useBigIndex = useBigIndex; 
-    int nextPage = tableBuffer.getInt(getFormat().OFFSET_NEXT_TABLE_DEF_PAGE);
-    ByteBuffer nextPageBuffer = null;
-    while (nextPage != 0) {
-      if (nextPageBuffer == null) {
-        nextPageBuffer = getPageChannel().createPageBuffer();
-      }
-      getPageChannel().readPage(nextPageBuffer, nextPage);
-      nextPage = nextPageBuffer.getInt(getFormat().OFFSET_NEXT_TABLE_DEF_PAGE);
-      ByteBuffer newBuffer = getPageChannel().createBuffer(
-          tableBuffer.capacity() + getFormat().PAGE_SIZE - 8);
-      newBuffer.put(tableBuffer);
-      newBuffer.put(nextPageBuffer.array(), 8, getFormat().PAGE_SIZE - 8);
-      tableBuffer = newBuffer;
-      tableBuffer.flip();
-    }
-    readTableDefinition(tableBuffer);
-    tableBuffer = null;
+    readTableDefinition(loadCompleteTableDefinitionBuffer(tableBuffer));
+    _fkEnforcer = new FKEnforcer(this);
   }
 
   /**
@@ -542,10 +534,28 @@ public class Table
     int pageNumber = rowState.getHeaderRowId().getPageNumber();
     int rowNumber = rowState.getHeaderRowId().getRowNumber();
 
-    // use any read rowValues to help update the indexes
-    Object[] rowValues = (!_indexDatas.isEmpty() ?
-                          rowState.getRowValues() : null);
-    
+    // attempt to fill in index column values
+    Object[] rowValues = null;
+    if(!_indexDatas.isEmpty()) {
+
+      // move to row data to get index values
+      rowBuffer = positionAtRowData(rowState, rowId);
+
+      for(Column idxCol : _indexColumns) {
+        getRowColumn(getFormat(), rowBuffer, idxCol, rowState, null);
+      }
+
+      // use any read rowValues to help update the indexes
+      rowValues = rowState.getRowValues();
+
+      // check foreign keys before proceeding w/ deletion
+      _fkEnforcer.deleteRow(rowValues);    
+
+      // move back to the header
+      rowBuffer = positionAtRowHeader(rowState, rowId);
+    }
+
+    // finally, pull the trigger
     int rowIndex = getRowStartOffset(rowNumber, getFormat());
     rowBuffer.putShort(rowIndex, (short)(rowBuffer.getShort(rowIndex)
                                       | DELETED_ROW_MASK | OVERFLOW_ROW_MASK));
@@ -1197,6 +1207,31 @@ public class Table
 
     pageChannel.writePage(rtn, umapPageNumber);
   }
+
+  /**
+   * Returns a single ByteBuffer which contains the entire table definition
+   * (which may span multiple database pages).
+   */
+  private ByteBuffer loadCompleteTableDefinitionBuffer(ByteBuffer tableBuffer)
+    throws IOException
+  {
+    int nextPage = tableBuffer.getInt(getFormat().OFFSET_NEXT_TABLE_DEF_PAGE);
+    ByteBuffer nextPageBuffer = null;
+    while (nextPage != 0) {
+      if (nextPageBuffer == null) {
+        nextPageBuffer = getPageChannel().createPageBuffer();
+      }
+      getPageChannel().readPage(nextPageBuffer, nextPage);
+      nextPage = nextPageBuffer.getInt(getFormat().OFFSET_NEXT_TABLE_DEF_PAGE);
+      ByteBuffer newBuffer = getPageChannel().createBuffer(
+          tableBuffer.capacity() + getFormat().PAGE_SIZE - 8);
+      newBuffer.put(tableBuffer);
+      newBuffer.put(nextPageBuffer.array(), 8, getFormat().PAGE_SIZE - 8);
+      tableBuffer = newBuffer;
+      tableBuffer.flip();
+    }
+    return tableBuffer;
+  }
     
   /**
    * Read the table definition
@@ -1268,7 +1303,12 @@ public class Table
 
     // read index column information
     for (int i = 0; i < _indexCount; i++) {
-      _indexDatas.get(i).read(tableBuffer, _columns);
+      IndexData idxData = _indexDatas.get(i);
+      idxData.read(tableBuffer, _columns);
+      // keep track of all columns involved in indexes
+      for(IndexData.ColumnDescriptor iCol : idxData.getColumns()) {
+        _indexColumns.add(iCol.getColumn());
+      }
     }
 
     // read logical index info (may be more logical indexes than index datas)
@@ -1462,6 +1502,10 @@ public class Table
     
     for (int i = 0; i < rowData.length; i++) {
       int rowSize = rowData[i].remaining();
+      Object[] row = rows.get(i);
+
+      // handle foreign keys before adding to table
+      _fkEnforcer.addRow(row);
 
       // get page with space
       dataPage = findFreeRowSpace(rowSize, dataPage, pageNumber);
@@ -1474,7 +1518,7 @@ public class Table
       // update the indexes
       RowId rowId = new RowId(pageNumber, rowNum);
       for(IndexData indexData : _indexDatas) {
-        indexData.addRow(rows.get(i), rowId);
+        indexData.addRow(row, rowId);
       }
     }
 
@@ -1523,29 +1567,32 @@ public class Table
       row = dupeRow(row, _columns.size());
     }
 
-    // fill in any auto-numbers (we don't allow autonumber values to be
-    // modified)
-    handleAutoNumbersForUpdate(row, rowBuffer, rowState);
-    
     // hang on to the raw values of var length columns we are "keeping".  this
     // will allow us to re-use pre-written var length data, which can save
     // space for things like long value columns.
-    Map<Column,byte[]> rawVarValues = 
+    Map<Column,byte[]> keepRawVarValues = 
       (!_varColumns.isEmpty() ? new HashMap<Column,byte[]>() : null);
 
-    // fill in any "keep value" fields
     for(Column column : _columns) {
-      if(column.getRowValue(row) == Column.KEEP_VALUE) {
-        column.setRowValue(
-            row, getRowColumn(getFormat(), rowBuffer, column, rowState,
-                              rawVarValues));
+      if(_autoNumColumns.contains(column)) {
+        // fill in any auto-numbers (we don't allow autonumber values to be
+        // modified)
+        column.setRowValue(row, getRowColumn(getFormat(), rowBuffer, column, 
+                                             rowState, null));
+      } else if(column.getRowValue(row) == Column.KEEP_VALUE) {
+        // fill in any "keep value" fields
+        column.setRowValue(row, getRowColumn(getFormat(), rowBuffer, column,
+                                             rowState, keepRawVarValues));
+      } else if(_indexColumns.contains(column)) {
+        // read row value to help update indexes
+        getRowColumn(getFormat(), rowBuffer, column, rowState, null);
       }
     }
 
     // generate new row bytes
     ByteBuffer newRowData = createRow(
         row, _singleRowBufferH.getPageBuffer(getPageChannel()), oldRowSize,
-        rawVarValues);
+        keepRawVarValues);
 
     if (newRowData.limit() > getFormat().MAX_ROW_SIZE) {
       throw new IOException("Row size " + newRowData.limit() + 
@@ -1553,8 +1600,12 @@ public class Table
     }
 
     if(!_indexDatas.isEmpty()) {
+
       Object[] oldRowValues = rowState.getRowValues();
 
+      // check foreign keys before actually updating
+      _fkEnforcer.updateRow(oldRowValues, row);
+
       // delete old values from indexes
       for(IndexData indexData : _indexDatas) {
         indexData.deleteRow(oldRowValues, rowId);
@@ -1889,23 +1940,6 @@ public class Table
   }
 
   /**
-   * Autonumber columns may not be modified on update.
-   */
-  private void handleAutoNumbersForUpdate(
-      Object[] row, ByteBuffer rowBuffer, RowState rowState)
-    throws IOException
-  {
-    if(_autoNumColumns.isEmpty()) {
-      return;
-    }
-
-    for(Column col : _autoNumColumns) {
-      col.setRowValue(row, getRowColumn(getFormat(), rowBuffer, col, rowState, 
-                                        null));
-    }
-  }
-
-  /**
    * Fill in all autonumber column values.
    */
   private void handleAutoNumbersForAdd(Object[] row)
@@ -2196,7 +2230,7 @@ public class Table
         autoCols.add(c);
       }
     }
-    return autoCols;
+    return (!autoCols.isEmpty() ? autoCols : Collections.<Column>emptyList());
   }
 
   /**
diff --git a/test/src/java/com/healthmarketscience/jackcess/FKEnforcerTest.java b/test/src/java/com/healthmarketscience/jackcess/FKEnforcerTest.java
new file mode 100644
index 0000000..9dd0c88
--- /dev/null
+++ b/test/src/java/com/healthmarketscience/jackcess/FKEnforcerTest.java
@@ -0,0 +1,138 @@
+/*
+Copyright (c) 2012 James Ahlborn
+
+This library is free software; you can redistribute it and/or
+modify it under the terms of the GNU Lesser General Public
+License as published by the Free Software Foundation; either
+version 2.1 of the License, or (at your option) any later version.
+
+This library is distributed in the hope that it will be useful,
+but WITHOUT ANY WARRANTY; without even the implied warranty of
+MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+Lesser General Public License for more details.
+
+You should have received a copy of the GNU Lesser General Public
+License along with this library; if not, write to the Free Software
+Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307
+USA
+*/
+
+package com.healthmarketscience.jackcess;
+
+import java.io.IOException;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+
+import static com.healthmarketscience.jackcess.DatabaseTest.*;
+import static com.healthmarketscience.jackcess.JetFormatTest.*;
+import junit.framework.TestCase;
+
+/**
+ *
+ * @author James Ahlborn
+ */
+public class FKEnforcerTest extends TestCase
+{
+
+  public FKEnforcerTest(String name) throws Exception {
+    super(name);
+  }
+
+  public void testNoEnforceForeignKeys() throws Exception {
+    for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.INDEX)) {
+
+      Database db = openCopy(testDB);
+      Table t1 = db.getTable("Table1");
+      Table t2 = db.getTable("Table2");
+      Table t3 = db.getTable("Table3");
+
+      t1.addRow(20, 0, 20, "some data", 20);
+
+      Cursor c = Cursor.createCursor(t2);
+      c.moveToNextRow();
+      c.updateCurrentRow(30, "foo30");
+
+      c = Cursor.createCursor(t3);
+      c.moveToNextRow();
+      c.deleteCurrentRow();
+
+      db.close();
+    }
+    
+  }
+
+  public void testEnforceForeignKeys() throws Exception {
+    for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.INDEX)) {
+
+      Database db = openCopy(testDB);
+      db.setEnforceForeignKeys(true);
+      Table t1 = db.getTable("Table1");
+      Table t2 = db.getTable("Table2");
+      Table t3 = db.getTable("Table3");
+
+      try {
+        t1.addRow(20, 0, 20, "some data", 20);
+        fail("IOException should have been thrown");
+      } catch(IOException ignored) {
+        // success
+        assertTrue(ignored.getMessage().contains("Table1[otherfk2]"));
+      }
+
+      try {
+        Cursor c = Cursor.createCursor(t2);
+        c.moveToNextRow();
+        c.updateCurrentRow(30, "foo30");
+        fail("IOException should have been thrown");
+      } catch(IOException ignored) {
+        // success
+        assertTrue(ignored.getMessage().contains("Table2[id]"));
+      }
+
+      try {
+        Cursor c = Cursor.createCursor(t3);
+        c.moveToNextRow();
+        c.deleteCurrentRow();
+        fail("IOException should have been thrown");
+      } catch(IOException ignored) {
+        // success
+        assertTrue(ignored.getMessage().contains("Table3[id]"));
+      }
+
+      Cursor c = Cursor.createCursor(t3);
+      Column col = t3.getColumn("id");
+      for(Map<String,Object> row : c) {
+        int id = (Integer)row.get("id");
+        id += 20;
+        c.setCurrentRowValue(col, id);
+      }
+
+      List<Map<String, Object>> expectedRows =
+        createExpectedTable(
+            createT1Row(0, 0, 30, "baz0", 0),
+            createT1Row(1, 1, 31, "baz11", 0),
+            createT1Row(2, 1, 31, "baz11-2", 0),
+            createT1Row(3, 2, 33, "baz13", 0));
+
+      assertTable(expectedRows, t1);
+
+      c = Cursor.createCursor(t2);
+      for(Iterator<?> iter = c.iterator(); iter.hasNext(); ) {
+        iter.next();
+        iter.remove();
+      }
+
+      assertEquals(0, t1.getRowCount());
+
+      db.close();
+    }
+    
+  }
+
+  private static Map<String,Object> createT1Row(
+      int id1, int fk1, int fk2, String data, int fk3)
+  {
+    return createExpectedRow("id", id1, "otherfk1", fk1, "otherfk2", fk2,
+                             "data", data, "otherfk3", fk3);
+  }
+}