@@ -136,7 +136,7 @@ Constraints: This database makes use of five user relevant constraints.
...
@@ -136,7 +136,7 @@ Constraints: This database makes use of five user relevant constraints.
-**Check constraint**: Ensures the satisfaction of certain conditions based on logical queries or the existence of other column entries in this row.
-**Check constraint**: Ensures the satisfaction of certain conditions based on logical queries or the existence of other column entries in this row.
<details>
<details>
<summary>Example1</summary>
<summary>Example1</summary>
Check constraints for numeric values: Wherever possible numeric values are checked on insertion for their plausibility (e.g.areas, discharges, inhabitants) to be within a meaningful range, usually \\\> 0 and smaller a maximum value. The catchment area of a river must be larger than 0 km² and smaller than 7000000 km² (size of the Amazon river basin).
Check constraints for numeric values: Wherever possible numeric values are checked on insertion for their plausibility (e.g.areas, discharges, inhabitants) to be within a meaningful range, usually \\\> 0 and smaller a maximum value. The catchment area of a river must be larger than 0 km² and smaller than 7000000 km² (size of the Amazon river basin).
Values are checked based on values in other columns: If a measurement is marked as below an analytical limit, the value of the limit must be given in the corresponding column. If a measurement is marked as below the analytical limit of detection (LOD), the LOD must be given in the corresponding column.
Values are checked based on values in other columns: If a measurement is marked as below an analytical limit, the value of the limit must be given in the corresponding column. If a measurement is marked as below the analytical limit of detection (LOD), the LOD must be given in the corresponding column.
SQL_Umsetzung: CONSTRAINT check_lod_available CHECK ((lod \\\\\\\> 0 AND value_below_lod IS TRUE) OR (value_below_lod IS NOT TRUE))
SQL_Umsetzung: CONSTRAINT check_lod_available CHECK ((lod \\\\\\\> 0 AND value_below_lod IS TRUE) OR (value_below_lod IS NOT TRUE))
-**Unique key constraint**: Ensures that uniqueness of a column entry or a combination of column entries are satisfied. Some unique keys are supposed to store aggregated information about its row. If this is the case, it can be seen in the description of the column. In the \__column_definition\_ view those columns are marked in the \__col_constraints\_ column with (u).
-**Unique key constraint**: Ensures that uniqueness of a column entry or a combination of column entries are satisfied. Some unique keys are supposed to store aggregated information about its row. If this is the case, it can be seen in the description of the column. In the \__column_definition\_ view those columns are marked in the \__col_constraints\_ column with (u).
A unique key, in this case an identifier consits of several information of its row
A unique key, in this case an identifier consits of several information of its row
identifier_analytical_method: Identifier of an analytical laboratory method defined in the table determ.analytical_methods. A laboratory method in best case is defined by a Standard (ISO or other) and consists of sample preparation and sample analysis techniques with appropriate measures for quality assurance and a validation procedure.
identifier_analytical_method: Identifier of an analytical laboratory method defined in the table determ.analytical_methods. A laboratory method in best case is defined by a Standard (ISO or other) and consists of sample preparation and sample analysis techniques with appropriate measures for quality assurance and a validation procedure.
</details>
</details>
-**Foreign key constraint**: Ensures the case sensitive referencing of a column or a combination of column entries in the so called "child table" originally migrated from the "parent table", hence providing referential integrity. In the \__column_definition\_ view those columns are marked in the \__col__constraints\_ column with (f).
-**Foreign key constraint**: Ensures the case sensitive referencing of a column or a combination of column entries in the so called "child table" originally migrated from the "parent table", hence providing referential integrity. In the \__column_definition\_ view those columns are marked in the \__col__constraints\_ column with (f).
<details><summary>Example</summary>
<details>
<summary>Example</summary>
A foreign key references one or in this example two columns of another table case sensitive.
A foreign key references one or in this example two columns of another table case sensitive.
SQL_Umsetzung: ALTER TABLE hidden.samples ADD FOREIGN KEY (identifier_sampling_site, sampling_site_compartment) REFERENCES hidden.sampling_sites (identifier_sampling_site, compartment) ON UPDATE RESTRICT ON DELETE RESTRICT;
SQL_Umsetzung: ALTER TABLE hidden.samples ADD FOREIGN KEY (identifier_sampling_site, sampling_site_compartment) REFERENCES hidden.sampling_sites (identifier_sampling_site, compartment) ON UPDATE RESTRICT ON DELETE RESTRICT;
