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