blocks|key|540270|text|你遇到的问题--位于层次结构顶端的类别的parent_id是多少？|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|540271|如果它将是null+-它将中断NOT+NULL构造。|540272|如果它是像0这样的任意数字，它会破坏外键(就像你的例子一样)。|540273|常见的解决方案是将NOT+NULL表达式放在parent_id上，并将顶级类别的parent_id设置为null。|540274|entityMap^0|K|9|0|5|4|F|8|0|5|1|0|9|8|M|9|14|9|1G|4|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|Y|8|@$9|Z|A|10|B|C]|$9|11|A|12|B|C]|$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|L|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|M|$]]

The problem you have - what would be the <code>parent_id</code> of a category at the top of the hierarchy?

If it will be <code>null</code> - it will break the <code>NOT NULL</code> constratint.

If it will be some arbitrary number like <code>0</code> - it will break the foreign key (like in your example).

The common solution - drop the <code>NOT NULL</code> constratint on the <code>parent_id</code> and set <code>parent_id</code> to <code>null</code> for top categories.

blocks|key|3506153|text|--+create+some+fake+data+for+testing
--
DROP+SCHEMA+tmp+CASCADE;
CREATE+SCHEMA+tmp+;
SET+search_path=tmp;
CREATE+TABLE+category

(
++cat_id+serial+NOT+NULL,
++cat_name+character+varying+NOT+NULL,
++parent_id+integer+NOT+NULL,
++CONSTRAINT+cat_id+PRIMARY+KEY+(cat_id)
);

INSERT+INTO+category(cat_name,parent_id)
SELECT+'Name_'+%7C%7C+gs::text
++++++++,+gs+%25+3
FROM+generate_series(0,9)+gs
++++++++;

++++++++--+find+the+records+with+the+non-existing+parents
SELECT+ca.parent_id+,+COUNT(*)
FROM+category+ca
WHERE+NOT+EXISTS+(
++++++++SELECT+*
++++++++FROM+category+nx
++++++++WHERE+nx.cat_id+=+ca.parent_id
++++++++)
GROUP+BY+ca.parent_id
++++++++;

++++++++--+if+all+is+well:+proceed
++++++++--+make+parent+pointer+nullable
ALTER+TABLE+category
++++++++ALTER+COLUMN+parent_id+DROP+NOT+NULL
++++++++;

++++++++--+set+non-existing+parent+pointers+to+NULL
UPDATE+category+ca
SET+parent_id+=+NULL
WHERE+NOT+EXISTS+(
++++++++SELECT+*
++++++++FROM+category+nx
++++++++WHERE+nx.cat_id+=+ca.parent_id
++++++++)
++++++++;

++++++++--+Finally,+add+the+FK+constraint
ALTER+TABLE+category
++++++++ADD+CONSTRAINT+cat_cat_id_fkey+FOREIGN+KEY+(parent_id)
++++++++++++++REFERENCES+category+(cat_id)+MATCH+SIMPLE
++++++++++++++ON+UPDATE+CASCADE+ON+DELETE+CASCADE
++++++++;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3506154|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>-- create some fake data for testing
--
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;
CREATE TABLE category

(
 cat_id serial NOT NULL,
 cat_name character varying NOT NULL,
 parent_id integer NOT NULL,
 CONSTRAINT cat_id PRIMARY KEY (cat_id)
);

INSERT INTO category(cat_name,parent_id)
SELECT 'Name_' || gs::text
 , gs % 3
FROM generate_series(0,9) gs
 ;

 -- find the records with the non-existing parents
SELECT ca.parent_id , COUNT(*)
FROM category ca
WHERE NOT EXISTS (
 SELECT *
 FROM category nx
 WHERE nx.cat_id = ca.parent_id
 )
GROUP BY ca.parent_id
 ;

 -- if all is well: proceed
 -- make parent pointer nullable
ALTER TABLE category
 ALTER COLUMN parent_id DROP NOT NULL
 ;

 -- set non-existing parent pointers to NULL
UPDATE category ca
SET parent_id = NULL
WHERE NOT EXISTS (
 SELECT *
 FROM category nx
 WHERE nx.cat_id = ca.parent_id
 )
 ;

 -- Finally, add the FK constraint
ALTER TABLE category
 ADD CONSTRAINT cat_cat_id_fkey FOREIGN KEY (parent_id)
 REFERENCES category (cat_id) MATCH SIMPLE
 ON UPDATE CASCADE ON DELETE CASCADE
 ;
</code></pre>

blocks|key|26811|text|这很简单。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|26812|这里的外键parent_id指的是cat_id。|offset|length|style|CODE|26813|这里存在一个带有parent_id=0的记录，但不是一个带有cat_id=0的记录。|26814|entityMap^0|0|5|9|H|6|0|8|B|U|8|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]|$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@$D|S|E|T|F|G]|$D|U|E|V|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|K|$]]

This is quite simple. 
Here the foreign key <code>parent_id</code> refers to <code>cat_id</code>. 
Here a record with <code>parent_id=0</code> exists but not a record with <code>cat_id=0</code>.

To create table I use:

<pre><code>CREATE TABLE category
(
 cat_id serial NOT NULL,
 cat_name character varying NOT NULL,
 parent_id integer NOT NULL,
 CONSTRAINT cat_id PRIMARY KEY (cat_id)
)
WITH (
 OIDS=FALSE
);
ALTER TABLE category
 OWNER TO pgsql;
</code></pre>

parent_id is a id to another category. Now I have a problem: how to cascade delete record with its children? I need to set parent_id as foreign key to cat_id.
I try this:

<pre><code> ALTER TABLE category 
ADD CONSTRAINT cat_cat_id_fkey FOREIGN KEY (parent_id)
 REFERENCES category (cat_id) MATCH SIMPLE
 ON UPDATE CASCADE ON DELETE CASCADE
</code></pre>

But it falls with:

<pre><code>ERROR: insert or update on table "category" violates foreign key constraint "cat_cat_id_fkey"
DETAIL: Key (parent_id)=(0) is not present in table "category".
</code></pre>

How to add a foreign key constraint to same table using ALTER TABLE in PostgreSQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

要创建表，我使用：CREATE TABLE category(  cat_id serial NOT NULL,  cat_name character varying NOT NULL,  parent_id integer NOT NULL,  CONSTRAINT cat_id PRIMARY KEY (cat_...

问如何在PostgreSQL中使用ALTER table向同一个表添加外键约束
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在PostgreSQL中使用ALTER table向同一个表添加外键约束EN