Thanks Guus, I knew that but that's not what I meant to ask you guys.
What I want to know is:
How do I get rid of the old values that do not match the new constraint?
The question is:
If I UNLOAD and LOAD the data again with the new constraint, does DB2 get rid of those old values that don't match the new constraint?
Thanks in Advance.
-----Mensagem original-----
De: Guus van de Velde [mailto:db2udbdba-ezmlmshield-x58208216.[Email address protected]
Enviada: quarta-feira, 28 de Setembro de 2005 15:59
Para: LazyDBA Discussion
Assunto: Re: Restrict values in an INTEGER column.
You can't force the values that do not match your check constraint to be changed as such.
what you could do, before you activate the constraint is alter the values. The only way your alter table add constraint is going to work, is if all the values already in your table already pass your check. Much like when you activate a foreign key on a table, all the rows in the table must have an existing value in the other table.
You could force all the values to match your check by doing something
like:
update table_name set column_name=2 where column_name>2 to bring all values >2 down to 2. (don't know if that will do what you want it to do...
Guus
On 28-Sep-05, at 3:00 AM, Pedro Miguel Gonçalves wrote:
> 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
>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
---------------------------------------------------------------------
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