Other Horizon Notation Type

Definition

“Classification of a horizon according to a specific classification system.”¹

INSPIRE Data Specification on Soil – Technical Guidelines,
D2.8.III.3.
https://inspire-mif.github.io/technical-guidelines/data/so/dataspecification_so.pdf

Table: otherhorizonnotationtype

Columns

Table Identifiers

In this table, the primary key is the id field (integer, auto-incrementing).
There is also a text field named GUID, which stores a UUID (Universally Unique Identifier) compliant with RFC 4122.

Although GUID is not mandatory at the schema level (it is not declared NOT NULL), its functional requirement is enforced by two triggers:

• otherhorizonnotationtypeguid (INSERT) trigger: if GUID is missing or empty, a correctly formatted UUID is generated and inserted automatically.
• otherhorizonnotationtypeguidupdate (UPDATE) trigger: prevents any modification of GUID after insertion, making it immutable (effectively behaving as a stable key).

Any foreign keys (FK) from other tables reference this table’s GUID field rather than the id field, ensuring stable and interoperable references across datasets and database instances.

Coded Fields

The horizonnotation­ field is a coded field (codelist-based attribute), meaning that it can only contain values belonging to a predefined codelist, in accordance with the INSPIRE specifications.

Codelist Definition

The complete list of allowed codes is stored in the codelist table.
The associated documentation, provides a detailed description of:

• which codes are available (codelist URL),
• the database tables to which each codelist applies,
• the fields for which each code is valid,

in accordance with the adopted conceptual model.

Validation and Data Entry

The semantic and syntactic validation of the inserted values is enforced at the database level through dedicated control triggers (i_horizonnotation/u_horizonnotation), ensuring compliance with the defined codelists.

Relationships (as child)

• None

Referenced by (as parent)

• otherhorizon_profileelement.guid_otherhorizonnotationtype → otherhorizonnotationtype.guid (ON UPDATE CASCADE, ON DELETE CASCADE)

Indexes

• None

Triggers

For every trigger you will find:

• When it runs (BEFORE/AFTER, INSERT/UPDATE/DELETE)
• What it reads and compares (columns, lookups in other tables)
• What happens on success (the statement proceeds, optional updates)
• What happens on failure (the exact error text raised)

otherhorizonnotationtypeguid / otherhorizonnotationtypeguidupdate

When they run: AFTER INSERT / AFTER UPDATE OF guid

What they do: Assign GUID at insert when missing; block changes afterwards.

If the check passes: Insert writes GUID; unchanged updates proceed.

If the check fails: On change, abort with: Cannot update guid column.

i_otherhorizonnotationtype / u_otherhorizonnotationtype

When they run: BEFORE INSERT / BEFORE UPDATE

What they do: Validate horizonnotation membership in OtherHorizonNotationTypeValue.

If the check passes: Statement proceeds.

If the check fails: Aborts with: Table otherhorizonnotationtype: Invalid value for horizonnotation. Must be present in id of otherhorizonnotationtypevalue codelist.