创建和删除外键

最近更新时间:2023-06-05 18:24:41

我的收藏

创建外键

postgres=# create table t_p(f1 int not null,f2 int ,primary key(f1));
create table t_f(f1 int not null,f2 int );NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# create table t_f(f1 int not null,f2 int );
NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# ALTER TABLE t_f ADD CONSTRAINT t_f_f1_fkey FOREIGN KEY (f1) REFERENCES t_p (f1);
ALTER TABLE
postgres=#
外键只是同一个节点内约束有效果,所以外键字段和对应主键字段必须都是表的分布键,否则由于数据分布于不同的节点内会导致更新失败。

删除外键

postgres=# \\d+ t_f
Table "public.t_f"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
f1 | integer | | not null | | plain | |
f2 | integer | | | | plain | |
Foreign-key constraints:
"t_f_f1_fkey" FOREIGN KEY (f1) REFERENCES t_p(f1)
Distribute By: SHARD(f1)
Location Nodes: ALL DATANODES

postgres=# ALTER TABLE t_f drop constraint t_f_f1_fkey;
ALTER TABLE
postgres=# \\d+ t_f
Table "public.t_f"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
f1 | integer | | not null | | plain | |
f2 | integer | | | | plain | |
Distribute By: SHARD(f1)
Location Nodes: ALL DATANODES