SF.net SVN: ledger-smb: [2011] branches/1.2/sql/fixes/tax_pkey_duplicate. sql

[..hidden..]

Revision: 2011
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2011&view=rev
Author:   tetragon
Date:     2007-12-27 18:40:43 -0800 (Thu, 27 Dec 2007)

Log Message:
-----------
Adding a fix script for the primary key to the tax table

Added Paths:
-----------
    branches/1.2/sql/fixes/tax_pkey_duplicate.sql

Added: branches/1.2/sql/fixes/tax_pkey_duplicate.sql
===================================================================
--- branches/1.2/sql/fixes/tax_pkey_duplicate.sql	                        (rev 0)
+++ branches/1.2/sql/fixes/tax_pkey_duplicate.sql	2007-12-28 02:40:43 UTC (rev 2011)
@@ -0,0 +1,13 @@
+-- SC: Replaces the primary key of the tax table with a check for if the
+--     combination of chart_id and validto is unique.  An index is added to
+--     check for the case of multiple NULL validto values and fail if that would
+--     result.
+
+ALTER TABLE tax DROP CONSTRAINT tax_pkey;
+ALTER TABLE tax ADD CONSTRAINT tax_unique UNIQUE (chart_id, validto);
+COMMENT ON CONSTRAINT tax_unique ON tax IS
+$$Checks on the base uniqueness of the chart_id, validto combination$$;
+
+CREATE UNIQUE INDEX tax_null_validto_unique_idx ON tax(chart_id) WHERE validto IS NULL;
+COMMENT ON INDEX tax_null_validto_unique_idx ON tax IS
+$$Part of primary key emulation for the tax table, ensures at most one NULL validto for each chart_id$$;


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.