From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: sky.sravan@gmail.com
Subject: BUG #17751: DDL CREATE statement accepts invalid default values for FOREIGN KEY constraint.
Date: Sat, 14 Jan 2023 08:32:15 +0000
Message-ID: <17751-30191fe0c6b1e981@postgresql.org> (raw)
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
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, sky.sravan@gmail.com
Subject: Re: BUG #17751: DDL CREATE statement accepts invalid default values for FOREIGN KEY constraint.
In-Reply-To: <17751-30191fe0c6b1e981@postgresql.org>
* 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