Thread: BUG #17751: DDL CREATE statement accepts invalid default values for FOREIGN KEY constraint.

The following bug has been logged on the website:

Bug reference:      17751
Logged by:          Sravan Kumar
Email address:      sky.sravan@gmail.com
PostgreSQL version: 15.1
Operating system:   Windows 10
Description:

VERSION: "PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit"

--Bug reporting ON Foreign key constraint "ON DELETE SET DEFAULT"

-- crate a users table
CREATE TABLE users(
    id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

--Insert values into the users table
INSERT INTO users(username)
    VALUES ('monahan93'),('pfeffer'),('99stroman'),('sim3onis');

--Display the values to check the insertion of data into users table
SELECT * FROM users;

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

--Insert values into the photos table
INSERT INTO photos(url,user_id)
VALUES
('http://bytebud.com/1.jpg',4),
('https://bytebud.com/324.jpg',1),
('https://bytebud.com/34.jpg',3),
('https://bytebud.com/514.jpg',2),
('https://bytebud.com/854.jpg',1),
('https://bytebud.com/95.jpg',1),
('https://bytebud.com/38.jpg',4),
('http://bytebud.com/831.jpg',NULL);

--Display the values to check the insertion of data into photos table
SELECT * FROM photos;

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

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