Mercurial > lada > lada-server
comparison db_schema/lada_schema.sql @ 1126:70b51893f15b
Fix UNIQUE-constraint and respective validation.
Btw. improved readability of DEFAULT definition.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 31 Oct 2016 18:09:45 +0100 |
| parents | 1ef08710aaeb |
| children | 48c0132dbc85 |
comparison
equal
deleted
inserted
replaced
| 1125:f9328849a53c | 1126:70b51893f15b |
|---|---|
| 190 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace: | 190 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace: |
| 191 -- | 191 -- |
| 192 | 192 |
| 193 CREATE TABLE probe ( | 193 CREATE TABLE probe ( |
| 194 id serial PRIMARY KEY, | 194 id serial PRIMARY KEY, |
| 195 id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('land.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL, | 195 id_alt character varying(20) UNIQUE NOT NULL |
| 196 DEFAULT 'sss' | |
| 197 || lpad(nextval('land.probe_probe_id_seq')::varchar, 12, '0') | |
| 198 || 'Y', | |
| 196 test boolean DEFAULT false NOT NULL, | 199 test boolean DEFAULT false NOT NULL, |
| 197 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, | 200 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, |
| 198 labor_mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, | 201 labor_mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle, |
| 199 hauptproben_nr character varying(20), | 202 hauptproben_nr character varying(20), |
| 200 datenbasis_id smallint REFERENCES stammdaten.datenbasis, | 203 datenbasis_id smallint REFERENCES stammdaten.datenbasis, |
| 212 mpl_id integer REFERENCES stammdaten.messprogramm_kategorie, | 215 mpl_id integer REFERENCES stammdaten.messprogramm_kategorie, |
| 213 mpr_id integer REFERENCES messprogramm, | 216 mpr_id integer REFERENCES messprogramm, |
| 214 solldatum_beginn timestamp without time zone, | 217 solldatum_beginn timestamp without time zone, |
| 215 solldatum_ende timestamp without time zone, | 218 solldatum_ende timestamp without time zone, |
| 216 tree_modified timestamp without time zone DEFAULT now(), | 219 tree_modified timestamp without time zone DEFAULT now(), |
| 217 UNIQUE (mst_id, hauptproben_nr, id_alt), | 220 UNIQUE (mst_id, hauptproben_nr), |
| 218 CHECK(solldatum_beginn <= solldatum_ende) | 221 CHECK(solldatum_beginn <= solldatum_ende) |
| 219 ); | 222 ); |
| 220 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | 223 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); |
| 221 CREATE TRIGGER tree_modified_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_probe(); | 224 CREATE TRIGGER tree_modified_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_probe(); |
| 222 | 225 |