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.
In-reply-to: <17751-30191fe0c6b1e981@postgresql.org>
References: <17751-30191fe0c6b1e981@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Sat, 14 Jan 2023 08:32:15 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1006385.1673712345.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 14 Jan 2023 11:05:45 -0500
Message-ID: <1006386.1673712345@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1006386.1673712345%40sss.pgh.pa.us>
Precedence: bulk

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 i=
n
> 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=3D4;

> ERROR:  insert or update on table "photos" violates foreign key constrai=
nt
> "photos_user_id_fkey"
> DETAIL:  Key (user_id)=3D(1) is not present in table "users".
> SQL state: 23503

Hm, what I get is

regression=3D# DELETE FROM users WHERE id=3D4;
ERROR:  insert or update on table "photos" violates foreign key constraint=
 "photos_user_id_fkey"
DETAIL:  Key (user_id)=3D(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