Project

General

Profile

« Previous | Next » 

Revision 582

Added by Aaron Marcuse-Kubitza about 13 years ago

vegbien.sql: Do location duplicate elimination independently on code or lat/long, allowing duplicate entries with NULLs to exist when a location is incompletely specified

View differences:

vegbien.for_ERD.my.sql
111 111 
CREATE TABLE location
112 112 
(
113 113 
  location_id int(11) NOT NULL AUTO_INCREMENT,
114 
  authorlocationcode character varying(30) NOT NULL DEFAULT '',
114 
  authorlocationcode character varying(30),
115 115 
  reference_id int(11),
116 116 
  parent_id int(11),
117 
  reallatitude double precision NOT NULL DEFAULT 'NaN',
118 
  reallongitude double precision NOT NULL DEFAULT 'NaN',
117 
  reallatitude double precision,
118 
  reallongitude double precision,
119 119 
  locationaccuracy double precision,
120 120 
  confidentialitystatus int(11) NOT NULL DEFAULT 0,
121 121 
  confidentialityreason character varying(200),
......
136 136 
  CONSTRAINT location_reference_id FOREIGN KEY (reference_id)
137 137 
      REFERENCES reference (reference_id) MATCH SIMPLE
138 138 
      ON UPDATE CASCADE ON DELETE CASCADE,
139 
  CONSTRAINT location_keys UNIQUE (reference_id , authorlocationcode , reallatitude , reallongitude ),
140 
  CONSTRAINT location_keys_subplot UNIQUE (parent_id , authorlocationcode , reallatitude , reallongitude )
139 
  CONSTRAINT location_keys_code UNIQUE (reference_id , authorlocationcode ),
140 
  CONSTRAINT location_keys_coords UNIQUE (reference_id , reallatitude , reallongitude ),
141 
  CONSTRAINT location_keys_subplot_code UNIQUE (parent_id , authorlocationcode ),
142 
  CONSTRAINT location_keys_subplot_coords UNIQUE (parent_id , sublocationxposition , sublocationyposition )
141 143 
);
142 144 

  		




  143
  145
  
    
CREATE TABLE locationevent -- VegBank's observation table.


  		












Also available in:  Unified diff