1 Abstract

xBRL-CSV Table Constraints are a set of backwards-compatible extensions to xBRL-CSV that enable basic but extremely efficient validation checks.

2 Introduction

This specification defines a number of additional validation instructions that sit within xBRL-CSV metadata files, supporting efficient validation of CSV files prior to loading of an XBRL taxonomy and construction of an OIM report model.

This specification does not stipulate a processing model. The design is intended to enable streaming and parallelisation. Most checks can be performed using constant memory, operating on a single row at a time. However, processors remain free to load entire CSV files into memory, or transfer them to persistent storage, if this is convenient for implementing the checks described here or for later operations.

2.1 Documentation conventions

2.1.1 Error codes

QNames in parenthetical red text after a "MUST" or "MUST NOT" statement prescribe standardised error codes to be used if the preceding condition is violated. "MUST" or "MUST NOT" statements that do not have a prescribed error code are not automatically enforceable, and processors are not required to detect violations.

2.2 Namespaces and namespace prefixes

Prefixes used in this specification are to be interpreted according to the following table:

3 Validation

This specification defines three types of validation and two types of processor.

Validation falls into the following categories:

• Metadata validation - essential validation of Table Constraint metadata to ensure internal consistency
• Metadata linting - additional validation of Table Constraint metadata for consistency with the taxonomy
• Report validation - constraints applied to xBRL-CSV data tables

A Table Constraint validator is a processor takes an xBRL-CSV report as input and enforces the constraints defined in this specification.

On encountering a document with the xBRL-CSV document type, a Table Constraint validator MUST perform Metadata validation and Report validation, raising tce error codes as appropriate.

Some table constraint metadata overlaps with information present in the taxonomy, and it is important that this information is consistent.

A Table Constraint linter is a processor that checks consistency of Table Constraint metadata with the taxonomy.

When directed to check a document with the xBRL-CSV document type, a Table Constraint linter MUST raise errors with the specified tcl error codes if the document does not conform to the Metadata linting checks defined in this specification.

3.1 JSON structure

Table constraint instructions are embedded within xBRL-CSV metadata files, and subject to the same basic JSON syntax restrictions (xbrlce:invalidJSON).

Some structural constraints are specific to this specification (tce:invalidJSONStructure):

• table constraint properties marked as "required" MUST be present wherever the enclosing object is present; and
• objects defined by this specification do not allow additional properties.

Table constraint properties are expected at specific locations within xBRL-CSV metadata. If a property appears at a location other than the one prescribed for it, tce:misplacedProperty MUST be raised.

3.2 Column roles

The xBRL-CSV specification explicitly defines three roles for a column:

• a fact column produces facts in the OIM model;
• a comment column contains information that does not survive into the OIM model; and
• a property group column contains references to objects that assist in the construction of facts.

This specification uses the following additional definitions:

• Any column that is not a comment column is a content column.
• A content column that is neither a fact column nor a property group column is a property column.
• A property column that supplies a value for a dimension is a dimension column.
• A fact column or dimension column with value constraints is a constrained column.
• A column that is both a fact column and a constrained column is a constrained fact column
• A column that is both a dimension column and a constrained column is a constrained dimension column

3.3 Parameter definitions

Expected parameters can be documented by placing a tc:parameters property in the additional properties of a table template object.

The value of the tc:parameters property is a parameters object.

The keys of a parameters object MUST be identifiers (xbrlce:invalidIdentifier).

Each value is a value constraint object.

A parameter that has been defined using this mechanism is known as a defined parameter.

3.4 Column value constraints

Constraints can be applied to all values in an xBRL-CSV column by placing a tc:constraints property in the additional properties of a column object.

The value of the tc:constraints property is a value constraint object.

3.5 Value constraints

A value constraint object specifies constraints on the values for a column or parameter. It has the following properties:

type (string)

(optional) Identifies an XML Schema or XBRL-CSV type that values MUST satisfy.

dimension (string)

(optional) Identifies the target dimension, if any, for this value.

optional (boolean)

(optional) Controls whether a value MAY be omitted. Defaults to false.

nillable (boolean)

(optional) Controls whether nil values are allowed. Defaults to true.

allowedValues

(optional) A list of strings specifying the allowed values.

allowedPatterns

(optional) A list of patterns constraining the allowed values.

timeZone (boolean)

(optional) Controls whether date values include a time zone component.

periodType (string)

(optional) Additional type restriction for values of type period

durationType (string)

(optional) Additional type restriction for values of type xs:duration

The constrained column or defined parameter associated with a value constraint object is known as the constraint subject.

3.5.1 Type validation

All core dimensions except noteId MAY be specified in xBRL-CSV metadata and data tables. Such a dimension is known in this specification as an xBRL-CSV core dimension and identified by one of the following strings: concept, entity, period, unit, language.

If type is specified, its value MUST identify (tce:invalidTypeConstraint):

• an XML Schema base type, prefixed by xs;
• an xBRL-CSV core dimension; or
• the string decimals, indicating that the constraint subject provides a value for the decimals property.

If type is not specified, dimension MUST be specified and MUST identify an xBRL-CSV core dimension (tce:invalidTypeConstraint). In this case, type defaults to the value of dimension.

All values for the constraint subject MUST be valid against type (tce:invalidValue).

In the case of an XML Schema type, validation is prescribed by that specification.

In the case of an xBRL-CSV core dimension, the allowed representations are defined by xBRL-CSV.

3.5.2 Dimension identification

The dimension field MAY be used to specify a dimension targeted by a dimension column or defined parameter.

If dimension is absent, the constraint subject MUST be a fact column.

If dimension is present, the constraint subject MUST NOT be a fact column (tce:invalidColumnRole).

If the constraint subject is a defined parameter, dimension MUST be present except in the case where type has the value decimals (tce:invalidColumnRole).

If dimension identifies an xBRL-CSV core dimension, type MUST be left unspecified (tce:invalidTypeConstraint); it will default to the value of dimension, as noted above.

If dimension is present, all the parameter references in property groups and fact columns pointing to the constraint subject MUST be bound to dimension (tce:illegalDimension).

3.5.3 Required values

If optional is set to false, values for the constraint subject MUST NOT be absent (tce:missingValue):

• for a defined parameter, the parameter MUST be specified at table or report level;
• for a constrained column, the value MUST be specified for every row in every table for the table template.

3.5.4 Nillable values

If nillable is set to false, values for the constraint subject MUST NOT be #nil or the JSON null value (tce:invalidValue).

3.5.5 Allowed values

If allowedValues is specified, each item in the list MUST conform to type (tce:illegalAllowedValue), and every value for the constraint subject MUST match one of the allowedValues (tce:invalidValue).

When type is an XML Schema type, equality is determined according to the rules of XML Schema, and allowedValues behaves equivalently to <xs:enumeration>.

When type is a an xBRL-CSV core dimension, equality is determined as defined in OIM 5.1.

3.5.6 Allowed patterns

If allowedPatterns is specified, the values for the constraint subject MUST conform to at least one of the patterns (tce:invalidValue).

For each pattern, matching proceeds according to XML Schema, as if the pattern were specified in <xs:pattern>.

3.5.7 Timezone

The following XML Schema types optionally allow a timezone component; each of these is known in this specification as optionally-timezoned schema type: xs:date, xs:time, xs:dateTime, xs:gYearMonth, xs:gMonthDay, xs:gDay.

If the timeZone property is specified, type MUST be period or an optionally-timezoned schema type (tce:unknownTimeZone).

If timeZone is set to true, all values for the constraint subject MUST have a timezone component (tce:missingTimeZone).

If timeZone is set to false, the values MUST NOT have a timezone component (tce:unexpectedTimeZone).

If timeZone is absent, the presence or absence of a timezone component is unconstrained.

3.5.8 Period type

If the periodType property is specified, type MUST be period (tce:illegalPeriodType).

The periodType property takes one of the following values (tce:unknownPeriodType), with noted implications for constraint subject value validation (tce:invalidPeriodType):

• year, half, quarter, week, month, day
  • these values correspond to the abbreviated period formats defined in xBRL-CSV:
    • e.g. 2024, 2024H1, 2024Q2, 2024W29, 2024-06, 2024-06-01
  • when periodType matches one of these strings, its abbreviated form MUST be used for constraint subject values.
• instant
  • in this case, a valid xBRL-CSV representation for an instant period MUST be used
  • abbreviated forms with suffixes are allowed
    • e.g. 2024W29@start, 2024Q2@end
• An xs:duration value
  • in this case, the period's time interval MUST match the specified duration
    • e.g. P9M would allow 2024-01-01..2024-09-30

3.5.9 Duration type

If the durationType property is specified, the type MUST be xs:duration (tce:illegalDurationType).

The durationType property takes one of the following values (tce:unknownDurationType), with noted implications for constraint subject value validation (tce:invalidDurationType):

• yearMonth
• dayTime

3.5.10 Value constraint linting

The type associated with a constraint subject in the taxonomy is known as the associated taxonomy type.

If dimension and type are both specified, the constraint subject targets a taxonomy-defined dimension and the associated taxonomy type is the dimension's type.

If dimension is not specified and type is specified, the constraint subject is a constrained fact column and the associated taxonomy type is the fact's datatype.

When a constraint subject has an associated taxonomy type, it MUST be equal to or derived from type (tcl:typeMismatch).

Furthermore, every value in allowedValues (if present) MUST be valid against the associated taxonomy type (tcl:invalidAllowedValue).

3.6 Keys

Constraints accross multiple rows may be enforced by placing a tc:keys property in the additional properties of a table template object.

The value of the tc:keys property is a keys object. It has the following properties:

primary

(optional) a primary key object

unique

(optional) a list of unique key objects

reference

(optional) a list of reference key objects

At least one property MUST be specified (tce:missingKeyProperty).

Each of the key objects has a name property subject to the following constraints:

• Names MUST be a identifiers (xbrlce:invalidIdentifier).
• Names MUST be unique among the keys defined in the same keys object (tce:duplicateKeyName).

3.6.1 primary

A primary key object establishes the primary unique index for a table.

It has the following properties:

name (string)

(optional) Defaults to primary

fields

(required) List of strings identifying columns or parameters

sortedColumns (boolean)

(optional) Specifies whether columns are sorted. Defaults to true.

sortedRows (boolean)

(optional) Specifies whether rows are sorted. Defaults to true.

dimensional (boolean)

(optional) Specifies whether the fields in the key correspond to OIM dimensions. Defaults to true.

3.6.1.1 Primary key fields

Each entry in the fields list MUST correspond to a constrained column or a defined parameter in the current table template (tce:illegalPrimaryKeyField).

Each entry in the fields list MUST have optional set to false (tce:invalidPrimaryKeyField).

3.6.1.2 Primary key enforcement

The ordered subset of fields that correspond to columns are known as primary key columns.

The ordered subset of fields that correspond to parameters are known as primary key parameters

The content columns in the table template that are not among the primary key columns are known as non-key columns

Table Constraint validators MUST raise tce:primaryKeyViolation if the same combination of values for the primary key fields is used for more than one row, according to each field's type (using XML Schema equality semantics), across all tables for the table template.

3.6.1.3 Column ordering

If sortedColumns is set to true, the columns MUST be ordered as follows (tce:invalidColumnOrder):

• primary key columns in the order they appear in fields; then
• constrained columns in lexicographical order by column name; then
• comment columns in lexicographical order by column name.

3.6.1.4 Row sorting

When sortedRows is set to true, primary key enforcement can be optimised by checking that each table for the table template is primary key sorted, and that no two tables for the table template have the same combination of values for the primary key parameters (or if they do, the ranges for primary key columns are disjoint, such that these tables could be combined by concatenation into a single table that is primary key sorted).

A table is primary key sorted if its rows are sorted in ascending order according to the rules described in this section.

To establish row ordering, values from the primary key columns are compared in turn:

• if the value in the current row is less than the corresponding value in the previous row, tce:primaryKeyViolation is raised
• if the value in the current row is greater than the corresponding value in the previous row, validation proceeds to the next row
• if the values are equal, the tie is resolved by comparing the next column in the primary key columns sequence, and so on recursively
• if the values are equal in all columns of the primary key columns list, tce:primaryKeyViolation is raised

Values are compared according to their type:

• a nil value (#nil or JSON null) is considered to be equal to another nil value, and less than any non-nil value
• for types derived from xs:decimal, xs:float, xs:double, xs:boolean, xs:base64Binary, and xs:hex64Binary, values are compared in a manner equivalent to the the XPath 3.1 expressions $cur lt $prev and $cur eq $prev, where $cur is the value from the current row, and $prev is the value from the previous row
• for types derived from xs:string, xs:QName and xs:anyURI, values are whitespace normalized and compared lexically according to their unicode codepoints
  • this is equivalent to the XPath 3.1 expression fn:compare($cur, $prev, "http://www.w3.org/2005/xpath-functions/collation/codepoint") where $cur and $prev are the whitespace normalized string representations of the values for the current and previous rows respectively
• for optionally-timezoned schema types, values without a timezone component are assumed to have a timezone of UTC (Z) and compared as described in Comparison operators on duration, date and time values
• for values derived from xs:duration:
  • if both values match the pattern '[^DT]*', or both match the pattern '[^YM]*[DT].*' they are compared according to Two totally ordered subtypes of duration
  • otherise, an error is raised (tce:incomparableDurations)
• for values of type period, comparison proceeds according to the semantics for the Period Core Dimension:
  • if the period values represent instants, they are compared according to the rules for xs:dateTime
  • if the period values represent durations, and their time intervals have equal length, their start dates are compared according to the rules for xs:dateTime
  • otherwise, an error is raised (tce:incomparablePeriods)

To avoid the possibility of tce:incomparableDurations and tce:incomparablePeriods errors, template authors are advised to apply timeZone, periodType and durationType constraints.

3.6.1.5 Dimensional tables

If dimensional is set to true, the following conditions MUST hold (tce:invalidDimensionalTable):

• each member of primary key columns MUST be a constrained dimension column with the dimension constraint specified
• each member of non-key columns MUST be a constrained fact column
• each constrained fact column MUST have a parameter reference to each member of primary key columns
  • i.e. the dimensions in the primary key apply to all facts in a row

3.6.2 unique

A unique key object establishes an auxiliary unique index for a table.

It has the following properties:

name (string)

(required) a name for the key

columns

(required) list of strings

name MUST be an identifier (xbrlce:invalidIdentifier).

Each member of columns MUST be a constrained column (tce:invalidUniqueKeyColumn).

Whereas the primary key is typically used for dimensions that qualify all facts in a table, and may include dimensions specified through parameters, unique keys typically involve fact columns and cannot apply to parameters. Another difference is that all of the fields in a primary key are required, while unique keys can include optional columns.

Processors MUST raise tce:uniqueKeyViolation if the same combination of values for the unique key columns is used for more than one row, according each field's type (using XML Schema equality semantics), across all tables for the table template. For the purposes of this check, two absent values are considered equal to each other.

3.6.3 reference

A reference key checks the values in a column (or combination of columns) against an index corresponding to another key.

It is common for references to apply across tables (hence the database term "foreign key"), but reference keys can also refer to an index defined within the same table template (a "self-referential foreign key").

A reference key is defined using a reference key object. It has the following properties:

name (string)

(required) a name for the key

columns

(required) a list of strings identifying columns in the current table template.

keyRef

(required) a key identifier object identifying a key in this table template or another template

negate (boolean)

(optional) if true, the constraint is inverted so that it passes if and only if a match is NOT found in the referenced index. Defaults to false.

name MUST be an identifier (xbrlce:invalidIdentifier).

Each member of columns MUST be a constrained column (tce:invalidReferenceKeyColumn).

columns MUST have the same number of columns as the key specified in keyRef (tce:invalidReferenceKey)

The index of values corresponding to the key specified in keyRef is known as the target index.

The ordered list of values for the columns in columns, in a given row, is known as the local value list.

Reference key validation proceeds as follows for each row in the table template, with tce:referenceKeyViolation raised if the check fails:

• If negate is false, the local value list MUST match an entry in the target index.
• If negate is true, the local value list MUST NOT match an entry in the target index.

As usual, comparisons are made according to the equality definitions for the type.

3.6.3.1 key identifier objects

A key identifier object identifies a key.

It has the following properties:

tableTemplate (string)

(required) The name of a table template

key (string)

(optional) The name of a key for that table template

The following constraints apply (tce:invalidKeyIdentifier):

• tableTemplate MUST identify a table template in the xBRL-CSV effective metadata.
• If key is specified, it MUST correspond to the name of a primary or unique key for the template identified by tableTemplate.
• If key is not specified, the tableTemplate MUST have a primary key, and key takes the primary key's name.

3.7 Example

This example shows the metadata for a set of CSV files capturing loan data.

{
    "documentInfo": {
        "documentType": "https://xbrl.org/2021/xbrl-csv",
        "namespaces": {
            "eg": "http://example.com/xbrl-csv/taxonomy",
            "lei": "http://standards.iso.org/iso/17442",
            "iso4217": "http://www.xbrl.org/2003/iso4217",
            "tc": "https://xbrl.org/PWD/2024-05-21/tc"
        },
        "taxonomy": [
            "taxonomy.xsd"
        ]
    },
    "tableTemplates": {
        "loan_data_template": {
            "columns": {
                "loan_id": {
                    "tc:constraints": {
                        "dimension": "eg:LoanId",
                        "type": "xs:string"
                    }
                },
                "loan_amount_eur": {
                    "dimensions": {
                        "unit": "iso4217:EUR"
                    },
                    "tc:constraints": {
                        "type": "xs:decimal"
                    }
                },
                "loan_amount_usd": {
                    "dimensions": {
                        "unit": "iso4217:USD"
                    },
                    "tc:constraints": {
                        "type": "xs:decimal"
                    }
                }
            },
            "dimensions": {
                "eg:LoanId": "$loan_id",
                "concept": "eg:LoanAmount",
                "entity": "$company_lei"
            },
            "tc:keys": {
                "primary": {
                    "fields": [ "loan_id", "company_lei" ]
                }
            },
            "tc:parameters": {
                "company_lei": {
                    "dimension": "entity"
                }
            }
        }
    },
    "tables": {
        "loan_data_table": {
            "template": "loan_data_template",
            "url": "table2-data-facts.csv"
        }
    }
}

Appendix A Intellectual property status (non-normative)

This document and translations of it may be copied and furnished to others, and derivative works that comment on or otherwise explain it or assist in its implementation may be prepared, copied, published and distributed, in whole or in part, without restriction of any kind, provided that the above copyright notice and this paragraph are included on all such copies and derivative works. However, this document itself may not be modified in any way, such as by removing the copyright notice or references to XBRL International or XBRL organizations, except as required to translate it into languages other than English. Members of XBRL International agree to grant certain licenses under the XBRL International Intellectual Property Policy (https://www.xbrl.org/legal).

This document and the information contained herein is provided on an "AS IS" basis and XBRL INTERNATIONAL DISCLAIMS ALL WARRANTIES, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO ANY WARRANTY THAT THE USE OF THE INFORMATION HEREIN WILL NOT INFRINGE ANY RIGHTS OR ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE.

The attention of users of this document is directed to the possibility that compliance with or adoption of XBRL International specifications may require use of an invention covered by patent rights. XBRL International shall not be responsible for identifying patents for which a license may be required by any XBRL International specification, or for conducting legal inquiries into the legal validity or scope of those patents that are brought to its attention. XBRL International specifications are prospective and advisory only. Prospective users are responsible for protecting themselves against liability for infringement of patents. XBRL International takes no position regarding the validity or scope of any intellectual property or other rights that might be claimed to pertain to the implementation or use of the technology described in this document or the extent to which any license under such rights might or might not be available; neither does it represent that it has made any effort to identify any such rights. Members of XBRL International agree to grant certain licenses under the XBRL International Intellectual Property Policy (https://www.xbrl.org/legal).