From: Tom Lane <tgl@sss.pgh.pa.us>
To: sky.sravan@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17751: DDL CREATE statement accepts invalid default values for FOREIGN KEY constraint.
Date: Sat, 14 Jan 2023 11:05:45 -0500
Message-ID: <1006386.1673712345@sss.pgh.pa.us> (raw)
In-Reply-To: <17751-30191fe0c6b1e981@postgresql.org>
References: <17751-30191fe0c6b1e981@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> --Create a table photos;
> -- set the default value of the foreign key as 50 which does not exist in
> the users table
> CREATE TABLE photos(
> id SERIAL PRIMARY KEY,
> url VARCHAR(200),
> user_id INTEGER DEFAULT 50 REFERENCES users(id) ON DELETE SET DEFAULT
> );
> --IF we try to delete any user, we will get an error message
> DELETE FROM users WHERE id=4;
> ERROR: insert or update on table "photos" violates foreign key constraint
> "photos_user_id_fkey"
> DETAIL: Key (user_id)=(1) is not present in table "users".
> SQL state: 23503
Hm, what I get is
regression=# DELETE FROM users WHERE id=4;
ERROR: insert or update on table "photos" violates foreign key constraint "photos_user_id_fkey"
DETAIL: Key (user_id)=(50) is not present in table "users".
That seems like exactly the correct behavior to me. If we
let the DELETE go through, and install the value 50, that
would surely be wrong because now the FK constraint would
be violated. But what else would you have it do, refuse the
second CREATE TABLE altogether on the grounds that maybe
sometime in the future 50 wouldn't be a valid value?
That's not an improvement (and it probably is contrary to
the SQL spec too).
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, sky.sravan@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17751: DDL CREATE statement accepts invalid default values for FOREIGN KEY constraint.
In-Reply-To: <1006386.1673712345@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox