Oracle Modify operation - error
I added a new column to my table.
ALTER TABLE neue_buch modify preis not null;
and I 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 is wrong with my CHANGE OF STATEMENT.?
thank you very much in advance,
magidu
You have two options:
-
Remove NULL - eg. update the rows with NULL to have some value; or remove the lines.
-
Make the constraint unaudited for existing data - This will cause the constraint to fail only for newly inserted or updated rows.
ALTER TABLE neue_buch MODIFY preis NOT NULL NOVALIDATE;
Both approaches have their pros and cons. Option 1 is better for performance because CBO can rely on the constraint when optimizing queries. However, if you just don't know what NULLs should be, you might be tempted to replace them with a "zero value indicator", that is, a magic value, for example, for a column that only has positive integers that you could replace with NULL. with -1 or something, but this has its drawbacks. Option 2 might be preferred on this instance type to avoid using magic values.
source to share
ALTER TABLE AG_PARTICELLE_AZIENDA MODIFY SEZIONE NOT NULL; ORA-02296: impossible abilitare (TRACKER.) - trovati valori non appropriati
nella tabella AG_PARTICELLE_AZIENDA alcuni valori di SEZIONE sono null!
Devo cost:
1) update AG_PARTICELLE_AZIENDA set sezione = null;
2) fixation;
3) ALTER TABLE AG_PARTICELLE_AZIENDA CHANGE SEZIONE NOT NULL;
non ho capito il messaggio di errore! qualcquno può aiutarmi? Grazie ugo montorsi mail: ugo.montorsi@sin.it