public inbox for [email protected]
help / color / mirror / Atom feedExample. Foreign Keys Constraints. Wrong Columns
3+ messages / 3 participants
[nested] [flat]
* Example. Foreign Keys Constraints. Wrong Columns
@ 2026-04-14 11:08 PG Doc comments form <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: PG Doc comments form @ 2026-04-14 11:08 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
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)
);
```
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Example. Foreign Keys Constraints. Wrong Columns
@ 2026-04-15 15:23 David G. Johnston <[email protected]>
parent: PG Doc comments form <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2026-04-15 15:23 UTC (permalink / raw)
To: [email protected]; [email protected]
On Wed, Apr 15, 2026 at 7:51 AM PG Doc comments form <[email protected]>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Example. Foreign Keys Constraints. Wrong Columns
@ 2026-04-17 15:06 Yushu Chen <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Yushu Chen @ 2026-04-17 15:06 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; [email protected]; [email protected]
David G. Johnston wrote:
> On Wed, Apr 15, 2026 at 7:51 AM PG Doc comments form <[email protected]>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-04-17 15:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-14 11:08 Example. Foreign Keys Constraints. Wrong Columns PG Doc comments form <[email protected]>
2026-04-15 15:23 ` David G. Johnston <[email protected]>
2026-04-17 15:06 ` Yushu Chen <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox