Thanks guys, I was able to do whatever i wanted to do.

Know I face another question,
After I drop the constraint and alter it for another values,
the table enters in CHECK PENDING state which I resolve doing a STOP/START of the tablespace, but the old values which
Are prohibited in the NEW INSERT OR UPDATE stays there. The only way to avoid that is to LOAD the table again? The old LOADED values will not enter with the new constraint? Is that it?

Thanks guys!


-----Mensagem original-----
De: sreeramoju [mailto:db2udbdba-ezmlmshield-x98172923.[Email address protected]
Enviada: terça-feira, 27 de Setembro de 2005 17:17
Para: LazyDBA Discussion
Assunto: Re: Restrict values in an INTEGER column.


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


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


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