Error while modifying table to add constraint

I would like to add a constraint to my table Account

name, attribute name Type

. In Type

I want to add an account type, for example:Saving, Credit Card, Home Loan, Personal Loan, Fixed Deposit, Current, iSaver.

Here is my code:

ALTER TABLE ACCOUNT 
ADD CONSTRAINT AccountCK_Type
CHECK (TYPE IN('Saving','Credit Card','Home Loan','Personal Loan','Fixed Deposit','Current','iSaver'));

      

Error display:

>Error report -
>
>SQL Error: ORA-02293: cannot validate (SHT461.ACCOUNTCK_TYPE) - check constraint violated
>02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
>
>*Cause:    an alter table operation tried to validate a check constraint to
>           populated table that had nocomplying values.
>
>*Action:   Obvious

      

Pls suggest me how to add restriction. Thank.

+3


source to share


1 answer


If you want the constraint to be enforced for future data changes, as you said in a comment, you can force it to ignore other existing values ​​with NOVALIDATE

:

ALTER TABLE ACCOUNT 
ADD CONSTRAINT AccountCK_Type
CHECK (TYPE IN('Saving','Credit Card','Home Loan','Personal Loan',
  'Fixed Deposit','Current','iSaver'))
ENABLE NOVALIDATE;

      



Otherwise, you will have to sanitize your existing data - deleting or fixing rows with any other value in that column - before creating the constraint.

+3


source







All Articles