Xrhrft

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.

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):

create table if not exists messages(

    id serial

        primary key, 

    max_length smallint not null

        check (max_length > 0),

    default_text text

        check (length(default_text) <= max_length),

    created_on timestamp with time zone not null,

    unique (id, max_length)

);





create table if not exists customized_messages(

    message_id int not null,

    max_length smallint not null,

    message text not null

        check (length(message_text) <= max_length), 

    created_on timestamp with time zone not null,

    foreign key (id, max_length)

        references messages (id, max_length)

);

Note (irrelevant to the question): the message_id column in the 2nd table does not make sense to be serial and at the same time foreign key to the 1st table.