这是我的 erd 和下面的我创建的表代码;
-- 该脚本是由 pgAdmin 4 中的 ERD 工具生成的。 --如果您发现任何错误(包括重现步骤),请在https://redmine.postgresql.org/projects/pgadmin4/issues/new记录问题。
BEGIN;
CREATE TABLE IF NOT EXISTS project1."Posts"
(
post_id serial NOT NULL,
user_id integer NOT NULL,
title "char" NOT NULL,
content text NOT NULL,
created_at timestamp with time zone NOT NULL,
PRIMARY KEY (post_id)
);
CREATE TABLE IF NOT EXISTS project1."Comments"
(
comment_id serial NOT NULL,
"post_id " integer NOT NULL,
"user_id " integer NOT NULL,
"text " text NOT NULL,
"created_at " timestamp with time zone NOT NULL,
PRIMARY KEY (comment_id)
);
CREATE TABLE IF NOT EXISTS project1.users
(
user_id serial NOT NULL,
username "char" NOT NULL,
password "char" NOT NULL,
email "char" NOT NULL,
PRIMARY KEY (user_id)
);
CREATE TABLE IF NOT EXISTS project1.products
(
"product_id " serial NOT NULL,
name "char" NOT NULL,
price numeric NOT NULL,
"category_id " integer NOT NULL,
PRIMARY KEY ("product_id ")
);
CREATE TABLE IF NOT EXISTS project1.orders
(
"order_id " serial NOT NULL,
user_id integer NOT NULL,
"order_date " date NOT NULL,
"total_amount " numeric NOT NULL,
PRIMARY KEY ("order_id ")
);
CREATE TABLE IF NOT EXISTS project1."order_items "
(
item_id serial NOT NULL,
order_id integer NOT NULL,
product_id integer NOT NULL,
quantity integer NOT NULL,
PRIMARY KEY (item_id)
);
CREATE TABLE IF NOT EXISTS project1.invoices
(
invoice_id serial NOT NULL,
order_id integer NOT NULL,
issued_date date NOT NULL,
amount_due numeric NOT NULL,
PRIMARY KEY (invoice_id)
);
CREATE TABLE IF NOT EXISTS project1.reviews
(
review_id serial NOT NULL,
product_id integer NOT NULL,
user_id integer NOT NULL,
rating integer,
comment text NOT NULL,
created_at timestamp with time zone NOT NULL,
PRIMARY KEY (review_id)
);
ALTER TABLE IF EXISTS project1."Posts"
ADD FOREIGN KEY (user_id)
REFERENCES project1.users (user_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS project1."Comments"
ADD FOREIGN KEY ("post_id ")
REFERENCES project1."Posts" (post_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS project1."Comments"
ADD FOREIGN KEY ("user_id ")
REFERENCES project1.users (user_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS project1.orders
ADD FOREIGN KEY (user_id)
REFERENCES project1.users (user_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS project1."order_items "
ADD FOREIGN KEY (order_id)
REFERENCES project1.orders ("order_id ") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS project1.invoices
ADD FOREIGN KEY (order_id)
REFERENCES project1.orders ("order_id ") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS project1.reviews
ADD FOREIGN KEY (product_id)
REFERENCES project1.products ("product_id ") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS project1.reviews
ADD FOREIGN KEY (user_id)
REFERENCES project1.users (user_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
END;
当我尝试插入这些数据时;
--project1.users 的虚拟数据
INSERT INTO project1.users (username, password, email)
VALUES
('user1', 'pass123', '[email protected]'),
('user2', 'pass456', '[email protected]'),
('user3', 'pass789', '[email protected]'),
('user4', 'pass101', '[email protected]'),
('user5', 'pass202', '[email protected]');
-- project1."Posts" 的虚拟数据
INSERT INTO project1."Posts" (user_id, title, content, created_at)
VALUES
(1, 'Post Title 1', 'Content of post 1', NOW()),
(2, 'Post Title 2', 'Content of post 2', NOW()),
(3, 'Post Title 3', 'Content of post 3', NOW()),
(4, 'Post Title 4', 'Content of post 4', NOW()),
(5, 'Post Title 5', 'Content of post 5', NOW());
-- 项目 1 的虚拟数据。“注释”
INSERT INTO project1."Comments" ("post_id ", "user_id ", "text ", "created_at ")
VALUES
(1, 2, 'Comment 1 on Post 1', NOW()),
(2, 3, 'Comment 2 on Post 2', NOW()),
(3, 4, 'Comment 3 on Post 3', NOW()),
(4, 5, 'Comment 4 on Post 4', NOW()),
(5, 1, 'Comment 5 on Post 5', NOW());
--project1.products 的虚拟数据
INSERT INTO project1.products ("product_id ", "name ", "price", "category_id ")
VALUES
(1, 'Product 1', 10.99, 1),
(2, 'Product 2', 20.99, 2),
(3, 'Product 3', 15.99, 1),
(4, 'Product 4', 25.99, 2),
(5, 'Product 5', 12.99, 1);
--project1.orders 的虚拟数据
INSERT INTO project1.orders ("user_id ", "order_date ", "total_amount ")
VALUES
(1, '2024-01-02', 50.99),
(2, '2024-01-03', 30.99),
(3, '2024-01-04', 45.99),
(4, '2024-01-05', 35.99),
(5, '2024-01-06', 22.99);
-- project1."order_items " Sql``` INSERT INTO project1."order_items " (order_id, Product_id, amount) VALUES (1, 1, 2), (2, 3, 1), (3, 5, 3), (4,2,2),(5,4,1);
-- Dummy data for project1.invoices
```sql
INSERT INTO project1.invoices (order_id, issued_date, amount_due)
VALUES
(1, '2024-01-03', 50.99),
(2, '2024-01-04', 30.99),
(3, '2024-01-05', 45.99),
(4, '2024-01-06', 35.99),
(5, '2024-01-07', 22.99);
--project1.reviews 的虚拟数据
INSERT INTO project1.reviews (product_id, user_id, rating, comment, created_at)
VALUES
(1, 2, 4, 'Good product', NOW()),
(2, 3, 5, 'Excellent product', NOW()),
(3, 4, 3, 'Average product', NOW()),
(4, 5, 4, 'Very good product', NOW()),
(5, 1, 5, 'Outstanding product', NOW());
我不断收到此错误;
错误:表“users”中不存在键 (user_id)=(1)。表“Posts”上的插入或更新违反了外键约束“Posts_user_id_fkey”
错误:表“Posts”上的插入或更新违反了外键约束“Posts_user_id_fkey” SQL 状态:23503 详细信息:表“users”中不存在键 (user_id)=(1)。
如果有人能帮助我解决这个问题,我会很高兴。我只是想提醒一下,我是 sql 新手,感谢您的宝贵时间!