RE: Restrict values in an INTEGER column.

RE: Restrict values in an INTEGER column.

 

  

SET INTEGRITY FOR tab1 OFF;
ALTER TABLE <add new constraint>;
SET INTEGRITY back on using an EXCEPTION TABLE

Cheers
Serge

Serge Rielau
DB2 Solutions Development
DB2 Universal Database for Linux, Unix, Windows
IBM Toronto Lab
Tel.: (905) 413 3907 Internet: [Email address protected]
Fax: (905) 413 4840 T/L: 969 3907



"Pedro Miguel Gonçalves "
<db2udbdba-ezmlmshield-x96558600.[Email address protected] wrote on
09/28/2005 11:12:29 AM:

>
> 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
>
>
>
> ---------------------------------------------------------------------
> 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