Hi,

Table-check constraints will enforce data integrity at the table level.
Once a tablecheck constraint has been defined for a table, every update
and insert statement
will involve a checking of the restriction or constraint. If the
constraint is violated, the data record will not be inserted or updated,
and an SQL error will be returned.
A table-check constraint can be defined at table creation time or later
using the ALTER statement

Adding Check Constraints
When you add a check constraint to a table which contains data, one of two
things can happen:

? All the rows meet the check constraint.
? Some or all the rows do not meet the check constraint.

In the first case, when all the rows meet the check constraint, the check
constraint will be created successfully. Future attempts to insert or
update data that does not
meet the constraint business rule will be rejected.

When there are some rows that do not meet the check constraint, the check
constraint will not be created (i.e., the ALTER TABLE statement will
fail). The ALTER TABLE statement, which adds a new constraint to the
EMPLOYEE table, is shown below. The check constraint is name check_job .
DB2 will use this name to inform us which constraint was violated if an
INSERT or UPDATE statement fails. The CHECK clause is used to define a
table-check constraint.

ALTER TABLE EMPLOYEE
ADD CONSTRAINT check_job
CHECK ( JOB IN ( 1,2,3) );


Similarily it can be created while creating a table

CREATE TABLE EMPLOYEE
( JOB INTEGER,
JOBNAME VARCHAR(30),
ADD CONSTRAINT check_job
CHECK ( JOB IN ( 1,2) )
);


See variable JOB which is INTEGER in above cases

Ok Doki

Thanks & Bestrgds
Sreeramoju Pavan Kumar

Tata Consultancy Services Limited
International Tech Park,
Innovator, 11th Floor,
Whitefield Road,
Bangalore - 560066,Karnataka
India
Mailto: sreeramoju.[Email address protected]
Website: http://www.tcs.com



"Pedro Miguel Gonçalves "
<db2udbdba-ezmlmshield-x73893162.[Email address protected]
09/27/2005 09:06 PM

To
"LazyDBA Discussion" <[Email address protected]
cc

Subject
Restrict values in an INTEGER column.







Hi Guys,

Currently working in DB2 v.7 fro Z/Os

I have 2 questions:

1st question:
==========

I need a table that will contain a column data-typed=INTEGER and that wil
restrict its update or insert only with the values 1 and 2.
Is that possible, I think so.

Please tell me how to code that in my table.


2nd question:
==========

If later I would want to ALTER the restriction on that same column to the
values 1,2 and 3?
That would make me drop a recreate my table? Or there's a way just
ALTERing the column and REORG?

Please tell me how to code that in my table too.

Thanks in advance,



---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


ForwardSourceID:NT000081E6


Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you

DB2 & UDB email list listserv db2-l LazyDBA home page