Ensure data integrity with constraints

To prevent invalid data, apply rules to your table columns using constraints.

Constraints are rules that apply to your database columns. This prevents you from entering invalid data into your database. When you create, change, or delete constraints on your s, the constraints are propagated to the underlying s, and to any indexes.

s support all standard constraint types. For foreign keys in particular, the following is supported:

• Foreign key constraints from a referencing a regular table
• Foreign key constraints from a regular table referencing a

Foreign keys from a referencing another are not supported.

For example, you can create a table that only allows positive device IDs, and non-null temperature readings. You can also check that time values for all devices are unique. To create this table, with the constraints, use this command:

CREATE TABLE conditions (
    time       TIMESTAMPTZ
    temp       FLOAT NOT NULL,
    device_id  INTEGER CHECK (device_id > 0),
    location   INTEGER REFERENCES locations (id),
    PRIMARY KEY(time, device_id)
) WITH (
    tsdb.hypertable
);

This example also references values in another locations table using a foreign key constraint.

Time columns used for partitioning must not allow NULL values. A NOT NULL constraint is added by default to these columns if it doesn’t already exist.

For more information on how to manage constraints, see the docs.