--- a/flys-backend/doc/schema/oracle.sql	Mon Apr 22 12:11:55 2013 +0200
+++ b/flys-backend/doc/schema/oracle.sql	Mon Apr 22 13:00:13 2013 +0200
@@ -3,7 +3,7 @@
 
 CREATE TABLE annotation_types (
     id              NUMBER(38,0) NOT NULL, 
-    name            VARCHAR2(255),
+    name            VARCHAR2(255) NOT NULL UNIQUE,
     PRIMARY KEY     (id)
 );
 
@@ -12,8 +12,8 @@
 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
 
 CREATE TABLE annotations (
-    id              NUMBER(38,0) NOT NULL, 
-    attribute_id    NUMBER(38,0),
+    id              NUMBER(38,0) NOT NULL,
+    attribute_id    NUMBER(38,0) NOT NULL,
     edge_id         NUMBER(38,0),
     position_id     NUMBER(38,0),
     range_id        NUMBER(38,0),
@@ -27,7 +27,7 @@
 
 CREATE TABLE attributes (
     id              NUMBER(38,0) NOT NULL, 
-    value           VARCHAR2(255), 
+    value           VARCHAR2(255) NOT NULL UNIQUE, 
     primary key     (id)
 );
 
@@ -78,9 +78,10 @@
 
 CREATE TABLE discharge_table_values (
     id                  NUMBER(38,0) NOT NULL,
-    q                   NUMBER(38,2),
-    w                   NUMBER(38,2),
-    table_id            NUMBER(38,0),
+    q                   NUMBER(38,2) NOT NULL,
+    w                   NUMBER(38,2) NOT NULL,
+    table_id            NUMBER(38,0) NOT NULL,
+    UNIQUE (table_id, q, w),
     PRIMARY KEY         (id)
 );
 
@@ -90,10 +91,10 @@
 
 CREATE TABLE discharge_tables (
     id                  NUMBER(38,0) NOT NULL,
-    description         VARCHAR2(255),
+    description         VARCHAR2(255) NOT NULL,
     bfg_id              VARCHAR2(50),
-    kind                NUMBER(38,0),
-    gauge_id            NUMBER(38,0),
+    kind                NUMBER(38,0) NOT NULL DEFAULT 0,
+    gauge_id            NUMBER(38,0) NOT NULL,
     time_interval_id    NUMBER(38,0),
     PRIMARY KEY         (id)
 );
@@ -122,7 +123,7 @@
     official_number     NUMBER(38,0) UNIQUE,
     range_id            NUMBER(38,0) NOT NULL,
     -- remove river id here because range_id references river already
-    river_id            NUMBER(38,0),
+    river_id            NUMBER(38,0) NOT NULL,
     PRIMARY KEY         (id),
     UNIQUE (name, river_id),
     UNIQUE (river_id, station)
@@ -197,7 +198,7 @@
 
 CREATE TABLE main_value_types (
     id                  NUMBER(38,0) NOT NULL,
-    name                VARCHAR2(255),
+    name                VARCHAR2(255) NOT NULL UNIQUE,
     PRIMARY KEY         (id)
 );
 
@@ -207,10 +208,13 @@
 
 CREATE TABLE main_values (
     id                  NUMBER(38,0) NOT NULL,
-    value               NUMBER(38,2),
-    gauge_id            NUMBER(38,0),
-    named_value_id      NUMBER(38,0),
+    value               NUMBER(38,2) NOT NULL,
+    gauge_id            NUMBER(38,0) NOT NULL,
+    named_value_id      NUMBER(38,0) NOT NULL,
     time_interval_id    NUMBER(38,0),
+
+    -- TODO: better checks
+    UNIQUE (gauge_id, named_value_id, time_interval_id),
     PRIMARY KEY         (id)
 );
 
@@ -221,7 +225,7 @@
 CREATE TABLE named_main_values (
     id                  NUMBER(38,0) NOT NULL,
     name                VARCHAR2(256) NOT NULL UNIQUE,
-    type_id             NUMBER(38,0),
+    type_id             NUMBER(38,0) NOT NULL,
     PRIMARY KEY (id)
 );
 
@@ -231,7 +235,7 @@
 
 CREATE TABLE positions (
     id                  NUMBER(38,0) NOT NULL,
-    value               VARCHAR2(255 char),
+    value               VARCHAR2(255 char) NOT NULL UNIQUE,
     PRIMARY KEY         (id)
 );
 
@@ -244,7 +248,8 @@
     a                   NUMBER(38,10) NOT NULL,
     b                   NUMBER(38,10),
     river_id            NUMBER(38,0),
-    PRIMARY KEY         (id)
+    UNIQUE (river_id, a, b),
+    PRIMARY KEY (id)
 );
 
 
@@ -254,10 +259,10 @@
 
 CREATE TABLE rivers (
     id                  NUMBER(38,0) NOT NULL,
-    official_number     NUMBER(38,0),
-    km_up               NUMBER(38,0),
-    name                VARCHAR2(255),
-    wst_unit_id         NUMBER(38,0),
+    official_number     NUMBER(38,0) UNIQUE,
+    km_up               NUMBER(38,0) NOT NULL DEFAULT 0,
+    name                VARCHAR2(255) NOT NULL UNIQUE,
+    wst_unit_id         NUMBER(38,0) NOT NULL,
     PRIMARY KEY         (id)
 );
 
@@ -279,7 +284,7 @@
 
 CREATE TABLE units (
     id                  NUMBER(38,0) NOT NULL,
-    name                VARCHAR2(255),
+    name                VARCHAR2(255) NOT NULL UNIQUE,
     PRIMARY KEY         (id)
 );
 
@@ -289,8 +294,9 @@
 
 CREATE TABLE wst_column_q_ranges (
     id                  NUMBER(38,0) NOT NULL,
-    wst_column_id       NUMBER(38,0),
-    wst_q_range_id      NUMBER(38,0),
+    wst_column_id       NUMBER(38,0) NOT NULL,
+    wst_q_range_id      NUMBER(38,0) NOT NULL,
+    UNIQUE (wst_column_id, wst_q_range_id),
     PRIMARY KEY         (id)
 );
 
@@ -300,9 +306,11 @@
 
 CREATE TABLE wst_column_values (
     id                  NUMBER(38,0) NOT NULL,
-    position            NUMBER(38,5),
-    w                   NUMBER(38,5),
-    wst_column_id       NUMBER(38,0),
+    position            NUMBER(38,5) NOT NULL,
+    w                   NUMBER(38,5) NOT NULL,
+    wst_column_id       NUMBER(38,0) NOT NULL,
+    UNIQUE (position, wst_column_id),
+    UNIQUE (position, wst_column_id, w),
     PRIMARY KEY         (id)
 );
 
@@ -313,10 +321,12 @@
 CREATE TABLE wst_columns (
     id                  NUMBER(38,0) NOT NULL,
     description         VARCHAR2(255),
-    name                VARCHAR2(255),
-    position            NUMBER(38,0),
+    name                VARCHAR2(255) NOT NULL,
+    position            NUMBER(38,0) NOT NULL DEFAULT 0,
     time_interval_id    NUMBER(38,0),
-    wst_id              NUMBER(38,0),
+    wst_id              NUMBER(38,0) NOT NULL,
+    UNIQUE (wst_id, name),
+    UNIQUE (wst_id, position),
     PRIMARY KEY         (id)
 );
 
@@ -326,8 +336,8 @@
 
 CREATE TABLE wst_q_ranges (
     id                  NUMBER(38,0) NOT NULL,
-    q                   NUMBER(38,5),
-    range_id            NUMBER(38,0),
+    q                   NUMBER(38,5) NOT NULL,
+    range_id            NUMBER(38,0) NOT NULL,
     PRIMARY KEY         (id)
 );
 
@@ -352,9 +362,10 @@
 
 CREATE TABLE wsts (
     id                  NUMBER(38,0) NOT NULL,
-    description         VARCHAR2(255),
-    kind                NUMBER(38,0),
-    river_id            NUMBER(38,0),
+    description         VARCHAR2(255) NOT NULL,
+    kind                NUMBER(38,0) NOT NULL,
+    river_id            NUMBER(38,0) NOT NULL,
+    UNIQUE (river_id, description),
     PRIMARY KEY         (id)
 );