From 914e5af35c01d96754b9aa267c1ed4ff4062d43c Mon Sep 17 00:00:00 2001 From: James Ahlborn Date: Mon, 21 Mar 2016 02:15:47 +0000 Subject: Allow null values in foreign key fields when enforcing referential integrity. Fixes issue #136 git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@980 f203690c-595d-4dc9-a70b-905162fa7fd2 --- src/changes/changes.xml | 4 ++++ .../com/healthmarketscience/jackcess/impl/FKEnforcer.java | 13 +++++++++++-- .../healthmarketscience/jackcess/impl/FKEnforcerTest.java | 9 ++++++--- 3 files changed, 21 insertions(+), 5 deletions(-) diff --git a/src/changes/changes.xml b/src/changes/changes.xml index c683ad9..a887ec3 100644 --- a/src/changes/changes.xml +++ b/src/changes/changes.xml @@ -13,6 +13,10 @@ Add Database.getTableMetaData method to enable getting basic info about a Table (by name) without actually loading it. + + Allow null values in foreign key fields when enforcing referential + integrity. + diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/FKEnforcer.java b/src/main/java/com/healthmarketscience/jackcess/impl/FKEnforcer.java index bf5138c..470035d 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/FKEnforcer.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/FKEnforcer.java @@ -219,8 +219,8 @@ final class FKEnforcer throws IOException { // ensure that the relevant rows exist in the primary tables for which - // this table is a secondary table. - if(!joiner.hasRows(row)) { + // this table is a secondary table. however, null values are allowed + if(!areNull(joiner, row) && !joiner.hasRows(row)) { throw new ConstraintViolationException( "Adding new row " + Arrays.asList(row) + " violates constraint " + joiner.toFKString()); @@ -288,6 +288,15 @@ final class FKEnforcer return false; } + private static boolean areNull(Joiner joiner, Object[] row) { + for(Index.Column col : joiner.getColumns()) { + if(col.getColumn().getRowValue(row) != null) { + return false; + } + } + return true; + } + private boolean enforcing() { return _table.getDatabase().isEnforceForeignKeys(); } diff --git a/src/test/java/com/healthmarketscience/jackcess/impl/FKEnforcerTest.java b/src/test/java/com/healthmarketscience/jackcess/impl/FKEnforcerTest.java index c3150ea..2567f12 100644 --- a/src/test/java/com/healthmarketscience/jackcess/impl/FKEnforcerTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/impl/FKEnforcerTest.java @@ -102,6 +102,8 @@ public class FKEnforcerTest extends TestCase assertTrue(ignored.getMessage().contains("Table3[id]")); } + t1.addRow(21, null, null, "null fks", null); + Cursor c = CursorBuilder.createCursor(t3); Column col = t3.getColumn("id"); for(Row row : c) { @@ -115,7 +117,8 @@ public class FKEnforcerTest extends TestCase 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)); + createT1Row(3, 2, 33, "baz13", 0), + createT1Row(21, null, null, "null fks", null)); assertTable(expectedRows, t1); @@ -125,7 +128,7 @@ public class FKEnforcerTest extends TestCase iter.remove(); } - assertEquals(0, t1.getRowCount()); + assertEquals(1, t1.getRowCount()); db.close(); } @@ -133,7 +136,7 @@ public class FKEnforcerTest extends TestCase } private static Row createT1Row( - int id1, int fk1, int fk2, String data, int fk3) + int id1, Integer fk1, Integer fk2, String data, Integer fk3) { return createExpectedRow("id", id1, "otherfk1", fk1, "otherfk2", fk2, "data", data, "otherfk3", fk3); -- cgit v1.2.3