Thread

1. Example. Foreign Keys Constraints. Wrong Columns

   The Post Office <noreply@postgresql.org> — 2026-04-14T11:08:35Z

   The following documentation comment has been logged on the website:
   
   Page: https://www.postgresql.org/docs/18/ddl-constraints.html
   Description:
   
   https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
   
   In the last example of this section it seems the `users` table is referenced
   wrong.
   
   ```sql
   CREATE TABLE users (
       tenant_id integer REFERENCES tenants ON DELETE CASCADE,
       user_id integer NOT NULL,
       PRIMARY KEY (tenant_id, user_id)
   );
   
   CREATE TABLE posts (
       tenant_id integer REFERENCES tenants ON DELETE CASCADE,
       post_id integer NOT NULL,
       author_id integer,
       PRIMARY KEY (tenant_id, post_id),
       FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL
   (author_id)
   );
   ```
   
   In this example `FOREIGN KEY (tenant_id, author_id) REFERENCES users ON
   DELETE SET NULL (author_id)` implies that `users` table columns are named
   `(tenant_id, author_id)` but in fact `users` table does not have a
   `author_id` column.
   
   That line should be probably like this because `users` tables has a
   `user_id` column instead of `author_id`
   
   ```sql
   CREATE TABLE posts (
       # ...
       FOREIGN KEY (tenant_id, author_id) REFERENCES users (tenant_id, user_id)
   ON DELETE SET NULL (author_id)
   );
   ```
   
   
   
   
   

2. Re: Example. Foreign Keys Constraints. Wrong Columns

   David G. Johnston <david.g.johnston@gmail.com> — 2026-04-15T15:23:34Z

   On Wed, Apr 15, 2026 at 7:51 AM PG Doc comments form <noreply@postgresql.org>
   wrote:
   
   > The following documentation comment has been logged on the website:
   >
   > Page: https://www.postgresql.org/docs/18/ddl-constraints.html
   > Description:
   >
   >
   > https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
   >
   >
   Given that users has:
   
   >     PRIMARY KEY (tenant_id, user_id)
   >
   >
   This:
   
   
   >     FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL
   > (author_id)
   >
   >
   And this:
   
   
   >     FOREIGN KEY (tenant_id, author_id) REFERENCES users (tenant_id,
   > user_id)
   > ON DELETE SET NULL (author_id)
   >
   
   Produce an identical outcome.
   
   The absence of a column list on the former causes the system to look at the
   primary key for the named table and use its column list - which is
   (tenant_id, user_id), same as the later explicit version.
   
   David J.
   

3. Re: Example. Foreign Keys Constraints. Wrong Columns

   Yushu Chen <gentcys@gmail.com> — 2026-04-17T15:06:28Z

   David G. Johnston wrote:
   > On Wed, Apr 15, 2026 at 7:51 AM PG Doc comments form <noreply@postgresql.org>
   > wrote:
   > 
   >> The following documentation comment has been logged on the website:
   >>
   >> Page: https://www.postgresql.org/docs/18/ddl-constraints.html
   >> Description:
   >>
   >>
   >> https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
   >>
   >>
   > Given that users has:
   > 
   >>     PRIMARY KEY (tenant_id, user_id)
   >>
   >>
   > This:
   > 
   > 
   >>     FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL
   >> (author_id)
   >>
   >>
   > And this:
   > 
   > 
   >>     FOREIGN KEY (tenant_id, author_id) REFERENCES users (tenant_id,
   >> user_id)
   >> ON DELETE SET NULL (author_id)
   >>
   > 
   > Produce an identical outcome.
   > 
   > The absence of a column list on the former causes the system to look at the
   > primary key for the named table and use its column list - which is
   > (tenant_id, user_id), same as the later explicit version.
   > 
   > David J.
   > 
   
   Thanks for explanation.
   
   I think "columns mapping" (just how I call it in this example) makes
   this example slightly non-intuitive, and reflects a less-common use case.
   Would it help to change `author_id` to `user_id` as a more
   straightforward case?
   
   Yushu Chen