postgresql依赖跟踪 cascade和restrict选项

本文主要是介绍postgresql依赖跟踪 cascade和restrict选项，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

weather表的外键为cities的主键city字段，如下为表结构

mydb=# \d weather
                      Table "public.weather"
 Column  |         Type          | Collation | Nullable | Default
---------+-----------------------+-----------+----------+---------
 city    | character varying(80) |           |          |
 temp_lo | integer               |           |          |
 temp_hi | integer               |           |          |
 prcp    | real                  |           |          |
 date    | date                  |           |          |
Foreign-key constraints:
    "weather_city_fkey" FOREIGN KEY (city) REFERENCES cities(city)

mydb=# \d cities
                       Table "public.cities"
  Column  |         Type          | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
 city     | character varying(80) |           | not null |
 location | point                 |           |          |
Indexes:
    "cities_pkey" PRIMARY KEY, btree (city)
Referenced by:
    TABLE "weather" CONSTRAINT "weather_city_fkey" FOREIGN KEY (city) REFERENCES cities(city)

删除cities表，报错无法删除，有依赖该表的对象

mydb=# drop table cities ;
2022-06-23 09:38:11.501 CST [7191] ERROR:  cannot drop table cities because other objects depend on it
2022-06-23 09:38:11.501 CST [7191] DETAIL:  constraint weather_city_fkey on table weather depends on table cities
2022-06-23 09:38:11.501 CST [7191] HINT:  Use DROP ... CASCADE to drop the dependent objects too.
2022-06-23 09:38:11.501 CST [7191] STATEMENT:  drop table cities ;
ERROR:  cannot drop table cities because other objects depend on it
DETAIL:  constraint weather_city_fkey on table weather depends on table cities
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
mydb=#
mydb=#

使用cascade选项删除

mydb=# drop table cities CASCADE;
NOTICE:  drop cascades to constraint weather_city_fkey on table weather
DROP TABLE
mydb=# \d
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | accounts | table | postgres
 public | product  | table | postgres
 public | rsl      | table | user01
 public | weather  | table | postgres
(4 rows)

mydb=#
mydb=#

cities表已经删除weather表还存在，但是外键约束已经被级联删除

mydb=# \d weather
                      Table "public.weather"
 Column  |         Type          | Collation | Nullable | Default
---------+-----------------------+-----------+----------+---------
 city    | character varying(80) |           |          |
 temp_lo | integer               |           |          |
 temp_hi | integer               |           |          |
 prcp    | real                  |           |          |
 date    | date                  |           |          |

使用restrict选项，其实就是默认的drop table table_name的默认行为，

mydb=# create table city (city varchar(80) primary key,location point);
CREATE TABLE
mydb=# \d city
                        Table "public.city"
  Column  |         Type          | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
 city     | character varying(80) |           | not null |
 location | point                 |           |          |
Indexes:
    "city_pkey" PRIMARY KEY, btree (city)

mydb=# alter table city rename to cities;
ALTER TABLE
mydb=#
mydb=# \d cities
                       Table "public.cities"
  Column  |         Type          | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
 city     | character varying(80) |           | not null |
 location | point                 |           |          |
Indexes:
    "city_pkey" PRIMARY KEY, btree (city)

mydb=# \d
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | accounts | table | postgres
 public | cities   | table | postgres
 public | product  | table | postgres
 public | rsl      | table | user01
 public | weather  | table | postgres
(5 rows)
mydb=# ALTER TABLE weather ADD CONSTRAINT weather_city_fkey FOREIGN KEY (city) REFERENCES cities (city);
ALTER TABLE
mydb=#
mydb=#
mydb=# \d weather
                      Table "public.weather"
 Column  |         Type          | Collation | Nullable | Default
---------+-----------------------+-----------+----------+---------
 city    | character varying(80) |           |          |
 temp_lo | integer               |           |          |
 temp_hi | integer               |           |          |
 prcp    | real                  |           |          |
 date    | date                  |           |          |
Foreign-key constraints:
    "weather_city_fkey" FOREIGN KEY (city) REFERENCES cities(city)

mydb=#
mydb=#
mydb=#
mydb=#
mydb=# drop table cities restrict ;
2022-06-23 09:44:33.279 CST [7191] ERROR:  cannot drop table cities because other objects depend on it
2022-06-23 09:44:33.279 CST [7191] DETAIL:  constraint weather_city_fkey on table weather depends on table cities
2022-06-23 09:44:33.279 CST [7191] HINT:  Use DROP ... CASCADE to drop the dependent objects too.
2022-06-23 09:44:33.279 CST [7191] STATEMENT:  drop table cities restrict ;
ERROR:  cannot drop table cities because other objects depend on it
DETAIL:  constraint weather_city_fkey on table weather depends on table cities
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

对于用户定义的函数，PostgreSQL会追踪与函数外部可见性质相关的依赖性，例如它的参数和结果类型，但不追踪检查函数体才能知道的依赖性。例如，考虑这种情况：

CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow','green', 'blue', 'purple');

CREATE TABLE my_colors (color rainbow, note text);

CREATE FUNCTION get_color_note (rainbow) RETURNS text AS
  'SELECT note FROM my_colors WHERE color = $1'
  LANGUAGE SQL;

PostgreSQL将会注意到get_color_note函数依赖于rainbow类型：删掉该类型会强制删除该函数，因为该函数的参数类型就无法定义了。但是PostgreSQL不会认为get_color_note依赖于my_colors表，因此即使该表被删除也不会删除这个函数。虽然这种方法有缺点，但是也有好处。如果该表丢失，这个函数在某种程度上仍然是有效的，但是执行它会导致错误。创建一个同名的新表将允许该函数重新有效。

实例
创建测试数据

mydb=# CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow','green', 'blue', 'purple');
CREATE TYPE
mydb=# CREATE TABLE my_colors (color rainbow, note text);
CREATE TABLE
mydb=# CREATE FUNCTION get_color_note (rainbow) RETURNS text AS   'SELECT note FROM my_colors WHERE color = $1'   LANGUAGE SQL;
CREATE FUNCTION
mydb=#
mydb=#
mydb=#

查看函数定义

mydb=# \df get_c
get_color_note         get_current_ts_config
mydb=# \df get_color_note
                             List of functions
 Schema |      Name      | Result data type | Argument data types |  Type
--------+----------------+------------------+---------------------+--------
 public | get_color_note | text             | rainbow             | normal
(1 row)

调用函数测试

mydb=# select get_color_note('red');
 get_color_note
----------------

(1 row)

mydb=# select get_color_note('orange');
 get_color_note
----------------

(1 row)

删除表

mydb=# drop table my_colors
mydb-# ;
DROP TABLE

删除表后，该函数还是存在的，重建表之后还是可以调用该函数

mydb=# select get_color_note('orange');
2022-06-23 09:54:27.797 CST [7191] ERROR:  relation "my_colors" does not exist at character 18
2022-06-23 09:54:27.797 CST [7191] QUERY:  SELECT note FROM my_colors WHERE color = $1
2022-06-23 09:54:27.797 CST [7191] CONTEXT:  SQL function "get_color_note" during inlining
2022-06-23 09:54:27.797 CST [7191] STATEMENT:  select get_color_note('orange');
ERROR:  relation "my_colors" does not exist
LINE 1: SELECT note FROM my_colors WHERE color = $1
                         ^
QUERY:  SELECT note FROM my_colors WHERE color = $1
CONTEXT:  SQL function "get_color_note" during inlining
mydb=#
mydb=#
mydb=#
mydb=#  CREATE TABLE my_colors (color rainbow, note text);
CREATE TABLE
mydb=#
mydb=#
mydb=# select get_color_note('orange');
 get_color_note
----------------

(1 row)

删除类型，该类型rainbow是函数的参数，所以将该参数删除后，函数也会被删除

mydb=# drop type rainbow;
2022-06-23 09:54:54.620 CST [7191] ERROR:  cannot drop type rainbow because other objects depend on it
2022-06-23 09:54:54.620 CST [7191] DETAIL:  function get_color_note(rainbow) depends on type rainbow
        table my_colors column color depends on type rainbow
2022-06-23 09:54:54.620 CST [7191] HINT:  Use DROP ... CASCADE to drop the dependent objects too.
2022-06-23 09:54:54.620 CST [7191] STATEMENT:  drop type rainbow;
ERROR:  cannot drop type rainbow because other objects depend on it
DETAIL:  function get_color_note(rainbow) depends on type rainbow
table my_colors column color depends on type rainbow
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
mydb=# drop type rainbow cascade ;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function get_color_note(rainbow)
drop cascades to table my_colors column color
DROP TYPE
mydb=#
mydb=# \df get_clo

mydb=# \df get_clo

mydb=# \df get_clo

mydb=# \df get_color_note;
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

mydb=#

参考pg官方文档：http://postgres.cn/docs/14/ddl-depend.html

这篇关于postgresql依赖跟踪 cascade和restrict选项的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！