CODEWITHSUNDEEP
sqldb2
Mariusz
Mariusz

Reputation: 1985

How to alter check constraint in db2 when I do not know its name

I have following column:

CREATE TABLE MyTable
(
  ...
  PAGE_SETUP CHAR(1)    DEFAULT 0  check (PAGE_SETUP in (0, 1))  
);

I want to create script which changes check constraint. I want to get following result:

CREATE TABLE MyTable
(
  ...
  PAGE_SETUP CHAR(1)    DEFAULT '0'  check (PAGE_SETUP in ('0', '1'))  
);

I don't know how to change check constraint using sql queries. I think that I could drop and create check constraint but I don't know its name.

Upvotes: 2

Views: 1782

Answers (1)

proksch_ibm
proksch_ibm

Reputation: 278

If you name your check constraint, it makes it easier to run the alter table statement. Otherwise, you have to look into sysibm.check_constraints to find the name of the constraint. Here is a sample script that should help you get started.

connect to pocdb;
drop table stack.check_test;

create table stack.check_test (
    f1 integer not null default 0,
    constraint f1_check check ( f1 in (0,1) )
);

insert into stack.check_test (f1) values (0);
insert into stack.check_test (f1) values (1);
insert into stack.check_test (f1) values (2);

alter table stack.check_test
    drop check f1_check;

alter table stack.check_test
    add constraint f1_check check ( f1 in (0,1,2) );

insert into stack.check_test (f1) values (0);
insert into stack.check_test (f1) values (1);
insert into stack.check_test (f1) values (2);
insert into stack.check_test (f1) values (3);

connect reset;
terminate;

The resulting execution of the script is:

connect to pocdb

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = DB2INST1
 Local database alias   = POCDB


drop table stack.check_test
DB20000I  The SQL command completed successfully.

create table stack.check_test ( f1 integer not null default 0, constraint f1_check check ( f1 in (0,1) ) )
DB20000I  The SQL command completed successfully.

insert into stack.check_test (f1) values (0)
DB20000I  The SQL command completed successfully.

insert into stack.check_test (f1) values (1)
DB20000I  The SQL command completed successfully.

insert into stack.check_test (f1) values (2)
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "STACK.CHECK_TEST.F1_CHECK".  SQLSTATE=23513

alter table stack.check_test drop check f1_check
DB20000I  The SQL command completed successfully.

alter table stack.check_test add constraint f1_check check ( f1 in (0,1,2) )
DB20000I  The SQL command completed successfully.

insert into stack.check_test (f1) values (0)
DB20000I  The SQL command completed successfully.

insert into stack.check_test (f1) values (1)
DB20000I  The SQL command completed successfully.

insert into stack.check_test (f1) values (2)
DB20000I  The SQL command completed successfully.

insert into stack.check_test (f1) values (3)
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0545N  The requested operation is not allowed because a row does not
satisfy the check constraint "STACK.CHECK_TEST.F1_CHECK".  SQLSTATE=23513

connect reset
DB20000I  The SQL command completed successfully.

terminate
DB20000I  The TERMINATE command completed successfully.

Upvotes: 2

Related Questions