Given the following PostgreSQL table definitions:
postgres=# create table if not exists messages(
postgres(# id serial primary key,
postgres(# max_length smallint not null check (max_length > 0),
postgres(# default_text text check (length(default_text) <= max_length),
postgres(# created_on timestamp with time zone not null
postgres(# );
CREATE TABLE
postgres=#
postgres=# create table if not exists customized_messages(
postgres(# message_id serial references messages(id) not null,
postgres(# message text not null,
postgres(# created_on timestamp with time zone not null
postgres(# );
CREATE TABLE
how can I enforce length(customized_messages.text)
is <=
messages.max_length
per the foreign key constraint?
Note that, as-is, I can do the following:
postgres=# insert into messages (id, max_length, default_text, created_on) values (1, 5, null, current_timestamp);
INSERT 0 1
postgres=# insert into customized_messages (message_id, message, created_on) values (1, 'longer than 5', current_timestamp);
INSERT 0 1
postgres=# select * from messages;
id | max_length | default_text | created_on
----+------------+--------------+-------------------------------
1 | 5 | | 2019-01-18 14:12:59.692075-05
(1 row)
postgres=# select * from customized_messages;
message_id | message | created_on
------------+---------------+-------------------------------
1 | longer than 5 | 2019-01-18 14:13:38.226987-05
(1 row)
So, I would like to not allow, e.g. check constraint or something else, the longer than 5
to be inserted since its length is > 5
.
Best Answer
One way is to duplicate the
max_length
column and enforce that they have identical values in the two tables with a composite foreign key (instead of the simple one):Note (irrelevant to the question): the
message_id
column in the 2nd table does not make sense to beserial
and at the same timeforeign key
to the 1st table.