SQL: How to check if foreign key was really changed

Question

I had FK with wrong constraints and I have to change it:

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;

There's no problem with it, but I have to apply this using patch file with checking if it was applied already. So I have to create function like this:

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;

So, the question is - how to check if FK will use ON DELETE CASCADE but not old NO ACTION?

Erwin Brandstetter · Accepted Answer · 2021-05-15T22:40:22.257

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

You can check the confdeltype in the system catalog table pg_constraint. The manual:

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

In PL/pgSQL code use it like:

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;

Table name can optionally be schema-qualified. See:

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

Information schema versus system catalog

As you answered yourself, the same can be achieved by querying the information schema, which adheres to an SQL standard.

However:

Not all major RDBMS implement it. Oracle doesn't, for one.
The information schema is implemented with (sometimes hugely complex) views (not tables), which make look-ups a lot slower than accessing tables in pg_catalog directly. A quick test in a real live db showed factor 10 for the example at hand. I have seem factor 1000 and more.
The aim of using the information schema is often to keep your implementation "portable". But this hardly ever works to begin with. The various RDBMS are just too far from the SQL standard in too many ways.

There is another benefit, though: Postgres does not assert anything concerning the structure of tables in pg_catalog across major versions. By using the information schema, one would be on the safe side across major versions of Postgres.
However, basic structures hardly change. While possible, it is rather unlikely, that a query like this would break in a future version.

See:

score 1 · Answer 2 · answered Dec 27 '13 at 07:30

1

Also, second solution whitch is seems to be less postgresql-specific.

Info about foreign keys stores in referential_constraints table:

SELECT * FROM information_schema.referential_constraints 
WHERE CAST(constraint_name AS TEXT) LIKE 'user_fk' 
    AND delete_rule LIKE 'CASCADE' 
    AND update_rule LIKE 'CASCADE';

answered Dec 27 '13 at 07:30

uncle Lem

4,954
8
33
53

1

I added a chapter discussing information schema vs. pg_catalog to my answer. – Erwin Brandstetter Dec 27 '13 at 07:58

SQL: How to check if foreign key was really changed

2 Answers2

Information schema versus system catalog