BUG #17751: DDL CREATE statement accepts invalid default values for FOREIGN KEY constraint. - Mailing list pgsql-bugs

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