SQL:如何检查外键是否真的改变了

Question

我的 FK 有错误的约束，我必须改变它:

ALTER TABLE user_login_logout_fact DROP CONSTRAINT user_fk;

ALTER TABLE user_login_logout_fact
  ADD CONSTRAINT user_fk FOREIGN KEY (user_id)
      REFERENCES uuser (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;

没有问题，但我必须使用补丁文件来应用它，并检查它是否已经应用。所以我必须创建这样的函数:

CREATE FUNCTION tryUpgrade(patch varchar) RETURNS integer AS $$
    DECLARE testRecord RECORD;
    BEGIN
        RAISE NOTICE 'checking %', patch;
        SELECT INTO testRecord * FROM patchlog where basename = patch;
        IF FOUND THEN
            RAISE NOTICE 'patch % has already been applied', patch;
            RETURN 0;
        END IF;

    //check if constraints are ok
    IF ok THEN
        RAISE NOTICE 'upgraded but not noted';
        INSERT INTO patchlog VALUES (patch, now());
        RETURN 0;
    END IF;

    SELECT INTO testRecord upgrade(); //this function will alter table
    INSERT INTO patchlog VALUES (patch, now());
    RAISE NOTICE 'upgraded';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

所以，问题是 - 如何检查 FK 是否将使用 ON DELETE CASCADE 而不是旧的 NO ACTION？

Answer 1

How to check if FK will use ON DELETE CASCADE but not old NO ACTION?

可以查看系统目录表中的confdeltype pg_constraint .手册:

Foreign key deletion action code: a = no action, r = restrict, c = cascade, n = set null, d = set default

在 PL/pgSQL 代码中像这样使用它:

IF EXISTS (
   SELECT FROM pg_constraint
   WHERE  conname = 'user_fk'
   AND    conrelid = 'user_login_logout_fact'::regclass
   AND    confdeltype = 'c'
  ) THEN 
  -- all done
ELSE
  -- run ALTER TABLE ...
END IF;

表名可以选择是模式限定的。见:

• How do I speed up counting rows in a PostgreSQL table?

信息模式与系统目录

正如您自己回答的那样，同样可以通过查询 information schema 来实现，它遵守 SQL 标准。

但是:

• 并非所有主要的 RDBMS 都实现了它。甲骨文没有，例如。

• 信息架构是通过(有时非常复杂) View (而非表格)实现的，这使得查找很多 比访问 pg_catalog 中的表慢直接地。在真实实时数据库中进行的快速测试显示手头示例的因子为 10。我似乎有 1000 倍甚至更多。

• 使用信息模式的目的通常是让您的实现“可移植”。但这几乎从一开始就行不通。各种 RDBMS 在很多方面都与 SQL 标准相去甚远。

不过还有另一个好处:Postgres 不会断言任何关于 pg_catalog 跨主要版本的表结构的内容。通过使用信息模式，跨主要版本的 Postgres 将是安全的。
然而，基本结构几乎没有变化。虽然有可能，但不太可能像这样的查询会在未来的版本中中断。

参见:

• How to check if a table exists in a given schema
• Related answer on dba.SE discussing "Information schema vs. system catalogs"