SF.net SVN: ledger-smb: [1391] trunk/sql/Pg-database.sql

[..hidden..]

Revision: 1391
          http://svn.sourceforge.net/ledger-smb/?rev=1391&view=rev
Author:   einhverfr
Date:     2007-07-12 13:56:23 -0700 (Thu, 12 Jul 2007)

Log Message:
-----------
Adding partial unique index on partnumber to prevent multiple active items sharing the same part number

Modified Paths:
--------------
    trunk/sql/Pg-database.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2007-07-12 20:48:40 UTC (rev 1390)
+++ trunk/sql/Pg-database.sql	2007-07-12 20:56:23 UTC (rev 1391)
@@ -531,6 +531,9 @@
   project_id int,
   avgcost NUMERIC
 );
+
+CREATE UNIQUE INDEX parts_partnumber_index_u ON parts (partnumber) 
+WHERE obsolete is false;
 --
 CREATE TABLE assembly (
   id int,
@@ -622,7 +625,7 @@
   chart_id int,
   taxcategory_id int,
   PRIMARY KEY (parts_id, chart_id),
-  FOREIGN KEY (parts_id) REFERENCES parts (id),
+  FOREIGN KEY (parts_id) REFERENCES parts (id) on delete cascade,
   FOREIGN KEY (chart_id) REFERENCES chart (id),
   FOREIGN KEY (taxcategory_id) REFERENCES taxcategory (taxcategory_id)
 );


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