Timescale Cloud: Performance, Scale, Enterprise
Self-hosted products
MST
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 hypertables, the constraints are propagated to the underlying chunks, and to any indexes.
Hypertables support all standard PostgreSQL constraint types. For foreign keys in particular, the following is supported:
- Foreign key constraints from a hypertable referencing a regular table
- Foreign key constraints from a regular table referencing a hypertable
Foreign keys from a hypertable referencing another hypertable 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 TIMESTAMPTZtemp FLOAT NOT NULL,device_id INTEGER CHECK (device_id > 0),location INTEGER REFERENCES locations (id),PRIMARY KEY(time, device_id)) WITH (tsdb.hypertable,tsdb.partition_column='time');
If you are self-hosting TimescaleDB v2.19.3 and below, create a PostgreSQL relational table,
then convert it using create_hypertable. You then enable hypercore with a call
to ALTER TABLE.
This example also references values in another locations
table using a foreign
key constraint.
Note
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
PostgreSQL docs.
Keywords
Found an issue on this page?Report an issue or Edit this page
in GitHub.