db2 - Remove check on a column who has no constraint name

Question

I have to remove this constraint on the TYPE_FR column from an existing table but this one does not have a constraint name and the examples I find need the name of the constraint that this one does not have. Thank you

TYPE_FR INTEGER NOT NULL CHECK (TYPE_FR > 0 AND TYPE_FR < 3)

question from:https://stackoverflow.com/questions/66067373/remove-check-on-a-column-who-has-no-constraint-name

Answer 1

Db2 will generate an anonymous (system generated) name for the constraint if you don't explicitly name the constraint.

You can find the system generated name in the catalog tables/views.

For Db2-on-Linux/Unix/Windows, here is one way to do it, example below.

If your Db2-server is Db2-for-Z/OS, then use sysibm.syschecks2.checkname to find the constraint name to drop.

If your Db2-server is Db2-for-i (as/400), then use qsys2.syschkcst.constraint_name to find the constraint name to drop.

create table MY_TEST_TABLE (TYPE_FR INTEGER NOT NULL CHECK (TYPE_FR > 0 AND TYPE_FR < 3) )
DB20000I  The SQL command completed successfully.

select constname from syscat.colchecks where tabschema='USER1' and tabname = 'MY_TEST_TABLE'

CONSTNAME                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------
SQL210205171553170                                                                                                              

  1 record(s) selected.



begin
  declare constraint_name varchar(128);
  declare my_sql varchar(1024);
  set constraint_name = (select constname
                         from syscat.colchecks
                         where tabschema='USER1'
                         and   tabname = 'MY_TEST_TABLE'
                        );
  if constraint_name is not null then
      set my_sql = 'alter table MY_TEST_TABLE drop constraint '||constraint_name ;
      execute immediate my_sql ;
  end if;
end
DB20000I  The SQL command completed successfully.

select constname from syscat.colchecks where tabschema='USER1' and tabname = 'MY_TEST_TABLE' 

CONSTNAME                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------

  0 record(s) selected.