Wednesday, June 12, 2013

1 DB2 SQL constraint 101

Information constraint

DB2 optimizer would evaluate information constraint when consider the best query plan.

NOT ENFORCED ENABLE QUERY OPTIMIZATION 

 

Reference constraint

  • ON UPDATE RESTRICT:  When update happen on the parent table, the reference key must remain the same for the child table.
  • ON UPDATE NO ACTION: When update happen on the parent table, the reference key does not have to be the same as before.  This is default behavior.

 

CREATE TABLE t1(c1 int NOT NULL primary key,c_desc char(20));
Insert into t1 values ( 1,'A');
Insert into t1 values ( 2,'B');
Insert into t1 values ( 3,'C');
 
CREATE TABLE t2(o int NOT NULL primary key,o_desc char(20),c_id int references t1(c1) ON UPDATE RESTRICT);
Insert into t2 values ( 100,'A100',1);
Insert into t2 values ( 200,'B200',1);
Insert into t2 values ( 300,'C300',2);

t1

image_thumb[6]

t2

image_thumb[7]

Below update will fail due to the ON UPDATE RESTRICT

update t1 set c1=c1-1;

image_thumb[9]

After recreate the table with ON UPDATE NO ACTION.

The update command works now.

image_thumb[11]

Table 1 looks like below.

image_thumb[13]

  • ON DELETE CASCADE: When parent row is deleted, all depends child rows will be deleted.
  • ON DELETE SET NULL: When parent row is deleted, all depends child rows will be set NULL.
  • ON DELETE RESTRICT: Ensure the child row has the same match value as before when delete the parent row. –> Essentially will stop the delete.
  • ON DELETE NO ACTION: Ensure the child row has match value ( does not have to be the same as before) when delete the parent row. –> Essentially will stop the delete. This is default.

Here is the sample for ON DELETE RESTRICT

CREATE TABLE t1(c1 int NOT NULL primary key,c_desc char(20));
Insert into t1 values ( 1,'A');
Insert into t1 values ( 2,'B');
Insert into t1 values ( 3,'C');
 
CREATE TABLE t2(o int NOT NULL primary key,o_desc char(20),c_id int references t1(c1) ON DELETE restrict);
Insert into t2 values ( 100,'A100',1);
Insert into t2 values ( 200,'B200',1);
Insert into t2 values ( 300,'C300',2);
delete t1 where c1=2;

The delete operation would fail.

image

CREATE TABLE t1(c1 int NOT NULL primary key,c_desc char(20));
Insert into t1 values ( 1,'A');
Insert into t1 values ( 2,'B');
Insert into t1 values ( 3,'C');
 
CREATE TABLE t2(o int NOT NULL primary key,o_desc char(20),c_id int references t1(c1) ON DELETE NO ACTION);
Insert into t2 values ( 100,'A100',1);
Insert into t2 values ( 200,'B200',1);
Insert into t2 values ( 300,'C300',2);
 
delete t1 where c1=2;

image

CHECK constraint

 

CREATE TABLE t3(c1 int NOT NULL primary key,c_desc char(20),c3 int check ( c3 > 10));
 
Insert into t3 values ( 100,'A100',11);
Insert into t3 values ( 101,'A100',12);

 

Disable CHECK constraint

By default, SET INTEGRITY XXX OFF would be in NO ACCESS MODE. This means, we can not access the table at all.

set integrity for t3 off; 
set integrity for t3 off READ ACCESS;

 

If we try to select the table while it is in NO ACCESS, we would get the error.

image

SET INTEGRITY XXX OFF , would put the table in the check pending state.

select tabname,status from syscat.tables where tabname = 'T3'

image

when table is in check pending state, the table can not be insert/delete/update nor create the index.

set integrity for t3 immediate checked ;
 
CREATE TABLE EXCEPTION_T3 AS 
(SELECT T3.*, CURRENT TIMESTAMP AS TIMESTAMP, CAST ('' AS CLOB(32K)) AS MSG  FROM T3) WITH NO DATA ;
 
set integrity for t3 immediate checked  for exception in t3 use EXCEPTION_T3;
set integrity for t3 all immediate unchecked ;

  • Use exception table to hold the row that violate the constraint .
  • Use UNCHECKED means the data is not verify against check constraint.

 

 

Reference

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0001111.html

1 comments:

  1. eToro is the ultimate forex broker for newbie and professional traders.

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates