Mercurial > dive4elements > river
view flys-backend/doc/schema/postgresql-minfo.sql @ 2835:8649e4ed01ec
Removed superflous imports.
flys-backend/trunk@4267 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Wed, 18 Apr 2012 12:29:05 +0000 |
parents | 13b3fcaa1b34 |
children | c3b2673eafbf |
line wrap: on
line source
BEGIN; CREATE SEQUENCE LOCATION_SYSTEM_SEQ; CREATE TABLE location_system ( id int NOT NULL, name VARCHAR(32) NOT NULL, description VARCHAR(255), PRIMARY KEY(id) ); CREATE SEQUENCE ELEVATION_MODEL_SEQ; CREATE TABLE elevation_model ( id int NOT NULL, name VARCHAR(32) NOT NULL, unit_id int NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) ); CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ; CREATE TABLE bed_height_type ( id int NOT NULL, name VARCHAR(16) NOT NULL, description VARCHAR(255), PRIMARY KEY(id) ); CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; CREATE TABLE bed_height_single ( id int NOT NULL, river_id int NOT NULL, year int NOT NULL, sounding_width int NOT NULL, type_id int NOT NULL, location_system_id int NOT NULL, cur_elevation_model_id int NOT NULL, old_elevation_model_id int, range_id int NOT NULL, evaluation_by VARCHAR(255), description VARCHAR(255), PRIMARY KEY(id), CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id), CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ); CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ; CREATE TABLE bed_height_epoch ( id int NOT NULL, river_id int NOT NULL, time_interval_id int NOT NULL, -- sounding_with int NOT NULL, -- type_id int NOT NULL, cur_elevation_model_id int NOT NULL, old_elevation_model_id int, range_id int NOT NULL, evaluation_by VARCHAR(255), description VARCHAR(255), PRIMARY KEY(id), CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id), CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) ); CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ; CREATE TABLE bed_height_single_values ( id int NOT NULL, bed_height_single_id int NOT NULL, station NUMERIC NOT NULL, height NUMERIC, uncertainty NUMERIC, data_gap NUMERIC NOT NULL, sounding_width NUMERIC NOT NULL, width NUMERIC NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ); CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ; CREATE TABLE bed_height_epoch_values ( id int NOT NULL, bed_height_epoch_id int NOT NULL, station NUMERIC NOT NULL, height NUMERIC, PRIMARY KEY(id), CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ); CREATE SEQUENCE DEPTHS_ID_SEQ; CREATE TABLE depths ( id int NOT NULL, lower NUMERIC NOT NULL, upper NUMERIC NOT NULL, unit_id int NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ); CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ; CREATE TABLE sediment_density ( id int NOT NULL, river_id int NOT NULL, depth_id int NOT NULL, unit_id int NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id), CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ); CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; CREATE TABLE sediment_density_values ( id int NOT NULL, sediment_density_id int NOT NULL, station NUMERIC NOT NULL, density NUMERIC NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ); CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; CREATE TABLE morphologic_width ( id int NOT NULL, river_id int NOT NULL, unit_id int NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id), CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) ); CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; CREATE TABLE morphologic_width_values ( id int NOT NULL, morphologic_width_id int NOT NULL, station NUMERIC NOT NULL, width NUMERIC NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ); COMMIT;