1

I added a new column in my table.

ALTER TABLE neue_buch modify preis not null;

and I have got this error:

Error starting at line 40 in command:
ALTER TABLE neue_buch
modify preis not null
Error report:
SQL Error: ORA-02296: cannot enable (S1885872.) - null values found
02296. 00000 - "cannot enable (%s.%s) - null values found"
*Cause:    an alter table enable constraint failed because the table
           contains values that do not satisfy the constraint.
*Action:   Obvious

What wrong with my MODIFY STATEMENT.?

many thanks in advance,

magidu

Improve this question

3 Answers 3

Reset to default
6

The error message is telling you that there are some rows in NEUE_BUCH where the PREIS column is NULL. You will need to modify those rows to have non-NULL values before you can create this NOT NULL constraint.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You have two options:

  1. Remove the NULLs - e.g. update the rows that are NULL to have some value; or delete the rows.

  2. Make the constraint non-validated for existing data - this will cause the constraint to only fail for newly inserted or updated rows.

    ALTER TABLE neue_buch MODIFY preis NOT NULL NOVALIDATE;

Both approaches have pros and cons. Option 1 is better for performance, because the CBO can rely on the constraint when optimizing queries. However, if you simply don't know what the NULLs should be, you might be tempted to replace them with a "null value indicator", i.e. a magic value - for example, for a column that has only positive integers you might replace the NULLs with -1 or something - but this has its own disadvantages. Option 2 may be preferable in this sort of instance to avoid having to use magic values.

Improve this answer

Comments

0

ALTER TABLE AG_PARTICELLE_AZIENDA MODIFY SEZIONE NOT NULL; ORA-02296: impossibile abilitare (TRACKER.) - trovati valori non appropriati

nella tabella AG_PARTICELLE_AZIENDA alcuni valori di SEZIONE sono a null!

devo fare:

1)update AG_PARTICELLE_AZIENDA set sezione = null;

2)commit;

3)ALTER TABLE AG_PARTICELLE_AZIENDA MODIFY SEZIONE NOT NULL;

non ho capito il messaggio di errore! qualcquno può aiutarmi? grazie ugo montorsi mail:[email protected]

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.