diff flys-artifacts/doc/conf/datacage.sql @ 980:f9a6a9cd918e

Datacage: Added ON DELETE CASCADE constraints in schema. flys-artifacts/trunk@2407 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 27 Jul 2011 08:20:55 +0000
parents 2306340d7540
children 799c7108ea6d
line wrap: on
line diff
--- a/flys-artifacts/doc/conf/datacage.sql	Tue Jul 26 14:14:55 2011 +0000
+++ b/flys-artifacts/doc/conf/datacage.sql	Wed Jul 27 08:20:55 2011 +0000
@@ -8,7 +8,7 @@
 CREATE TABLE collections (
     id      IDENTITY PRIMARY KEY NOT NULL,
     gid     UUID                 NOT NULL UNIQUE,
-    user_id INT                  NOT NULL REFERENCES users(id),
+    user_id INT                  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
     name    VARCHAR(256)         NOT NULL
 );
 
@@ -20,13 +20,13 @@
 
 CREATE TABLE collection_items (
     id            IDENTITY PRIMARY KEY NOT NULL,
-    collection_id INT                  NOT NULL REFERENCES collections(id),
-    artifact_id   INT                  NOT NULL REFERENCES artifacts(id)
+    collection_id INT                  NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
+    artifact_id   INT                  NOT NULL REFERENCES artifacts(id)   ON DELETE CASCADE
 );
 
 CREATE TABLE artifact_data (
     id          IDENTITY PRIMARY KEY NOT NULL,
-    artifact_id INT                  NOT NULL REFERENCES artifacts(id),
+    artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
     k           VARCHAR(256)         NOT NULL,
     v           VARCHAR(256),         -- Maybe too short
     UNIQUE (artifact_id, k)
@@ -34,12 +34,20 @@
 
 CREATE TABLE facets (
     id          IDENTITY PRIMARY KEY NOT NULL,
-    artifact_id INT                  NOT NULL REFERENCES artifacts(id),
+    artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
     name        VARCHAR(256)         NOT NULL,
     num         INT                  NOT NULL,
+    state       VARCHAR(256)         NOT NULL,
     output      VARCHAR(256)         NOT NULL,
     description VARCHAR(256),
     UNIQUE (artifact_id, output, num, name)
 );
 
+-- DROP TABLE facets;
+-- DROP TABLE artifact_data;
+-- DROP TABLE collection_items;
+-- DROP TABLE collections;
+-- DROP TABLE artifacts;
+-- DROP TABLE users;
+
 COMMIT;

http://dive4elements.wald.intevation.org