From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | sky(dot)sravan(at)gmail(dot)com |
Subject: | BUG #17751: DDL CREATE statement accepts invalid default values for FOREIGN KEY constraint. |
Date: | 2023-01-14 08:32:15 |
Message-ID: | 17751-30191fe0c6b1e981@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17751
Logged by: Sravan Kumar
Email address: sky(dot)sravan(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2023-01-14 16:02:01 | Re: DROP DATABASE deadlocks with logical replication worker in PG 15.1 |
Previous Message | Sam S | 2023-01-14 02:23:18 | Re: BUG #17739: postgres ts_headline function is not returning matches it should during full text search |