diff flys-backend/doc/schema/oracle.sql @ 2352:8126ccb58f28

Fixed errors. flys-backend/trunk@2932 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Bjoern Schilberg <bjoern@intevation.de>
date Tue, 11 Oct 2011 07:29:53 +0000
parents f993b735db71
children 67e0371f6f40
line wrap: on
line diff
--- a/flys-backend/doc/schema/oracle.sql	Mon Oct 10 16:20:17 2011 +0000
+++ b/flys-backend/doc/schema/oracle.sql	Tue Oct 11 07:29:53 2011 +0000
@@ -1,4 +1,3 @@
-BEGIN;
 -- ANNOTATION_TYPES
 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
 
@@ -33,11 +32,6 @@
     END;
 /
 
-ALTER TABLE annotations ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES ranges;
-ALTER TABLE annotations ADD CONSTRAINT constraint_edges FOREIGN KEY (edge_id) REFERENCES edges;
-ALTER TABLE annotations ADD CONSTRAINT constraint_positions FOREIGN KEY (position_id) REFERENCES positions;
-ALTER TABLE annotations ADD CONSTRAINT constraint_attributes FOREIGN KEY (attribute_id) REFERENCES attributes;
-ALTER TABLE annotations ADD CONSTRAINT constraint_types FOREIGN KEY (type_id) REFERENCES annotation_types;
 
 -- ATTRIBUTES 
 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
@@ -70,7 +64,6 @@
     END;
 /
 
-ALTER TABLE cross_section_lines ADD CONSTRAINT constraint_cross_sections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
 
 -- CROSS_SECTION_POINTS
 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
@@ -90,7 +83,6 @@
     END;
 /
 
-ALTER TABLE cross_section_points ADD CONSTRAINT constraint_cross_section_lines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
 
 -- CROSS_SECTIONS
 create sequence CROSS_SECTIONS_ID_SEQ;
@@ -109,8 +101,6 @@
     END;
 /
 
-ALTER TABLE cross_sections ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
-ALTER TABLE cross_sections ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
 
 -- DISCHARGE_TABLE_VALUES
 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
@@ -125,11 +115,10 @@
 
 CREATE OR REPLACE TRIGGER discharge_table_values_trigger BEFORE INSERT ON discharge_table_values FOR each ROW
     BEGIN
-        SELECT DISCHARGE_TABLES_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
+        SELECT DISCHARGE_TABLE_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
     END;
 /
 
-ALTER TABLE discharge_table_values ADD CONSTRAINT constraint_discharge_tables foreign key (table_id) REFERENCES discharge_tables;
 
 -- DISCHARGE_TABLES
 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
@@ -149,8 +138,6 @@
     END;
 /
 
-ALTER TABLE discharge_tables ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
-ALTER TABLE discharge_tables ADD CONSTRAINT constraint_gauges FOREIGN KEY (gauge_id) REFERENCES gauges;
 
 -- EDGES
 CREATE SEQUENCE EDGES_ID_SEQ;
@@ -188,8 +175,6 @@
     END;
 /
 
-ALTER TABLE gauges add CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
-ALTER TABLE gauges add CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES ranges;
 
 -- HYK_ENTRIES
 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
@@ -208,7 +193,6 @@
     END;
 /
 
-ALTER TABLE hyk_entries ADD CONSTRAINT constraint_hyks FOREIGN KEY (hyk_id) REFERENCES hyks;
 
 -- HYK_FLOW_ZONE_TYPES
 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
@@ -244,8 +228,6 @@
     END;
 /
 
-ALTER TABLE hyk_flow_zones ADD CONSTRAINT constraint_hyk_formations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
-ALTER TABLE hyk_flow_zones ADD CONSTRAINT constraint_hyk_flow_zone_types FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
 
 -- HYK_FORMATIONS
 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
@@ -268,7 +250,6 @@
     END;
 /
 
-ALTER TABLE hyk_formations ADD CONSTRAINT constraint_hyk_entries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
 
 -- HYKS
 CREATE SEQUENCE HYKS_ID_SEQ;
@@ -286,7 +267,6 @@
     END;
 /
 
-ALTER TABLE hyks ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
 
 -- MAIN_VALUE_TYPES
 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
@@ -321,9 +301,6 @@
     END;
 /
 
-ALTER TABLE main_values ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
-ALTER TABLE main_values ADD CONSTRAINT constraint_gauges FOREIGN KEY (gauge_id) REFERENCES gauges;
-ALTER TABLE main_values ADD CONSTRAINT constraint_named_main_values FOREIGN KEY (named_value_id) REFERENCES named_main_values;
 
 -- NAMED_MAIN_VALUES
 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
@@ -341,7 +318,6 @@
     END;
 /
 
-ALTER TABLE named_main_values ADD CONSTRAINT constraint_main_value_types FOREIGN KEY (type_id) REFERENCES main_value_types;
 
 -- POSITIONS
 CREATE SEQUENCE POSITIONS_ID_SEQ;
@@ -375,7 +351,6 @@
     END;
 /
 
-ALTER TABLE ranges ADD CONSTRAINT contraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
 
 -- RIVERS
 CREATE SEQUENCE RIVERS_ID_SEQ;
@@ -394,7 +369,6 @@
     END;
 /
 
-ALTER TABLE rivers ADD CONSTRAINT contraint_units FOREIGN KEY (wst_unit_id) REFERENCES units;
 
 -- TIME_INTERVALS
 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
@@ -443,8 +417,6 @@
     END;
 /
 
-ALTER TABLE wst_column_q_ranges ADD CONSTRAINT constraint_wst_colums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
-ALTER TABLE wst_column_q_ranges ADD CONSTRAINT constraint_wst_q_ranges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;
 
 -- WST_COLUMN_VALUES
 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
@@ -463,7 +435,6 @@
     END;
 /
 
-ALTER TABLE wst_column_values ADD CONSTRAINT constraint_wst_columns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
 
 -- WST_COLUMNS
 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
@@ -484,8 +455,6 @@
     END;
 /
 
-ALTER TABLE wst_columns ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
-ALTER TABLE wst_columns ADD CONSTRAINT constraint_wsts FOREIGN KEY (wst_id) REFERENCES wsts;
 
 -- WST_Q_RANGES
 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
@@ -503,7 +472,6 @@
     END;
 /
 
-ALTER TABLE wst_q_ranges ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES RANGES;
 
 -- WSTS
 CREATE SEQUENCE WSTS_ID_SEQ;
@@ -523,6 +491,37 @@
     END;
 /
 
-ALTER TABLE wsts ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
 
-COMMIT;
+-- ADD CONSTRAINTs
+ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges;
+ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
+ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
+ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
+ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types;
+ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
+ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
+ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers;
+ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
+ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables foreign key (table_id) REFERENCES discharge_tables;
+ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
+ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
+ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers;
+ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges;
+ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks;
+ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
+ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
+ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers;
+ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
+ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
+ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
+ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
+ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
+ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers;
+ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
+ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
+ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;
+ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
+ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
+ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts;
+ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES;
+ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers;

http://dive4elements.wald.intevation.org