将复合主键的一部分用作外键
我正在使用PostgreSQL。
我有一个表帐户，其中account_id是主要键。我还有一个名为关系的表格，带有复合主键（follower_id，polust_id）。每个关系必须是唯一的。
ALTER TABLE accounts ADD CONSTRAINT users_pk PRIMARY KEY (account_id);
ALTER TABLE relations ADD CONSTRAINT relations_pk PRIMARY KEY (follower_id, following_id);
我想从follower_id（关系） - ＆gt;创建一个外键约束。 account_id（帐户），以及以下_id。
ALTER TABLE relations ADD CONSTRAINT follower_id_fk FOREIGN KEY (follower_id) REFERENCES accounts (account_id) ON DELETE CASCADE
数据库不接受此外键。我收到以下错误：
错误：在表“关系”上插入或更新违反外键约束“ follower_id_fk” 详细信息：键（follower_id）=（4）在表“帐户”中不存在。
我理解这一点，因为它是复合主键。
我想实现的目标：
删除帐户时，我想删除account_id是follower_id的所有记录（delete cascade 上的）以及以下是以下_ID 。
我可以在NodeJS代码或触发功能中执行此操作，但是我不知道最佳性能是什么。有人知道/最好的解决方案吗？
I'm using PostgreSQL.
I have a table accounts with account_id as the primary key. I also have a second table called relations with a composite primary key (follower_id, following_id). Each relation must be unique.
ALTER TABLE accounts ADD CONSTRAINT users_pk PRIMARY KEY (account_id);
ALTER TABLE relations ADD CONSTRAINT relations_pk PRIMARY KEY (follower_id, following_id);
I want to create a foreign key constraint from follower_id (relations) -> account_id (accounts), and the same with following_id.
ALTER TABLE relations ADD CONSTRAINT follower_id_fk FOREIGN KEY (follower_id) REFERENCES accounts (account_id) ON DELETE CASCADE
This foreign key is not accepted by the database. I get the following error:
ERROR: insert or update on table "relations" violates foreign key constraint "follower_id_fk"
DETAIL: Key (follower_id)=(4) is not present in table "accounts".
I understand this, because it's a composite primary key.
What I want to achieve:
When an account is deleted, I want to delete all the records where the account_id is the follower_id (ON DELETE CASCADE) AND where it is the following_id.
I could do this in my nodejs code or with a trigger function, but I don't know what will be the best performance-wise. Does anyone knows a/the best solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论