How to generate constraint sql?

All we need is an easy explanation of the problem, so here it is.

I’m looking for a method that can generate the creating sql, like PgAdmin’s SQL tab while having selected the constraint. (how pg admin does it?)

I want to implement a generic method that can drop and recreate a foreign key constraint after a process.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Postgresql has pg_get_constraintdef function which is for generating a create statement of specific constraint.

Here is an example;

Schema (PostgreSQL v13)

create table test (
  id integer primary key,
  data integer check (data between 1 and 100)
);


create table test2 (
  id integer primary key,
  data integer,
  test_id integer references test (id)
);

Query #1

select
    connamespace::regnamespace "Schema",
    conrelid::regclass "Table",
    conname "Constraint",
    pg_get_constraintdef(oid) "Definition",
    format ('ALTER TABLE %I.%I ADD CONSTRAINT %I %s;', 
                connamespace::regnamespace,
                conrelid::regclass,
                conname,
                pg_get_constraintdef(oid)
           )
    
  from pg_constraint 
  where 
    conname IN (
        'test_data_check', 'test_pkey', 
        'test2_pkey', 'test2_test_id_fkey' 
    );
Schema Table Constraint Definition format
public test test_data_check CHECK (((data >= 1) AND (data <= 100))) ALTER TABLE public.test ADD CONSTRAINT test_data_check CHECK (((data >= 1) AND (data <= 100)));
public test test_pkey PRIMARY KEY (id) ALTER TABLE public.test ADD CONSTRAINT test_pkey PRIMARY KEY (id);
public test2 test2_pkey PRIMARY KEY (id) ALTER TABLE public.test2 ADD CONSTRAINT test2_pkey PRIMARY KEY (id);
public test2 test2_test_id_fkey FOREIGN KEY (test_id) REFERENCES test(id) ALTER TABLE public.test2 ADD CONSTRAINT test2_test_id_fkey FOREIGN KEY (test_id) REFERENCES test(id);

View on DB Fiddle

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply