Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...