Attachment 48156 Details for Bug 36546 – Integrity constraint violation

Integrity constraint violation

Integrity constraint violation.txt (text/plain), 2.92 KB, created by ribotb on 2011-06-19 02:39:34 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: ribotb

Created: 2011-06-19 02:39:34 UTC

Size: 2.92 KB

patch

obsolete

>-- DDL to create the tables
>
>create table d_study (study character varying(10) not null primary key);
>
>
>create table d_studyelement (study character varying (10) not null,
>  element character varying(10) not null,
>  constraint pk_element primary key (study, element),
>  constraint fk_elementstudy foreign key (study) references d_study (study)
>    on update cascade on delete cascade);
>
>
>create table l_lunit (length_unit character varying(10) not null primary key);
>
>
>create table d_sample (study character varying (10) not null,
>  sample character varying (10) not null,
>  element character varying (10) null,
>  surface_elevation double precision,
>  elevation_units character varying (10),
>  sample_depth double precision,
>  depth_units character varying (10),
>  constraint pk_sample primary key (study, sample),
>  constraint fk_samplestudy foreign key (study) references d_study (study)
>    on update cascade on delete cascade,
>  constraint fk_sampleelem foreign key (study, element) references d_studyelement
>    (study, element) on update cascade on delete cascade,
>  constraint fk_sampleelev foreign key (elevation_units) references l_lunit (length_unit)
>    on update cascade on delete cascade,
>  constraint fk_sampledepth foreign key (depth_units) references l_lunit (length_unit)
>    on update cascade on delete cascade);
>
>-- DML to initialize the tables
>
>insert into d_study (study) values ('StudyA');
>insert into d_study (study) values ('StudyB');
>
>
>insert into d_studyelement (study, element) values ('StudyA', 'Elem1');
>insert into d_studyelement (study, element) values ('StudyA', 'Elem2');
>
>
>insert into l_lunit (length_unit) values ('meters');
>insert into l_lunit (length_unit) values ('cm');
>insert into l_lunit (length_unit) values ('ft');
>
>
>insert into d_sample (study, sample, element, surface_elevation,
>  elevation_units, sample_depth, depth_units)
>  values ('StudyA', 'Sample1', 'Elem1', 1234, 'meters', 2, 'cm');
>insert into d_sample (study, sample, element, surface_elevation,
>  elevation_units, sample_depth, depth_units)
>  values ('StudyA', 'Sample2', 'Elem1', 1234, 'meters', 50, 'cm'); 
>insert into d_sample (study, sample, element, surface_elevation,
>  elevation_units, sample_depth, depth_units)
>  values ('StudyA', 'Sample4', 'Elem1', 1234, 'meters', 1.2, 'meters');
>insert into d_sample (study, sample, element, surface_elevation,
>  elevation_units, sample_depth, depth_units)
>  values ('StudyA', 'Sample5', 'Elem2', 602, 'ft', 2, 'meters');
>insert into d_sample (study, sample, element, surface_elevation,
>  elevation_units, sample_depth, depth_units)
>  values ('StudyA', 'Sample6', 'Elem2', 602, 'ft', 4, 'meters');
>insert into d_sample (study, sample, element, surface_elevation,
>  elevation_units, sample_depth, depth_units)
>  values ('StudyB', 'Sample1', null, 900, 'meters', 2, 'cm');
>
>-- Cascade an update through multiple paths
>
>update d_study set study='StudyZ' where study='StudyA';

Actions: View

Attachments on bug 36546: 48156 | 48157