ORA-01758: The table must be empty to add required (not null) Columns

Add a new column to a table with records and specify not null to report a ORA-01758. What should I do?

1. Specify the default value for the newly added column.

2. Clear the records in the table and add new columns.

The experiment is as follows:

1. SQL> drop table ord;
2. Table dropped
4. SQL> create table ORD
5. 2 (
6. 3 ORD_NO NUMBER (2) notNull,
7. 4 ORD_DATE DATE,
8. 5 CUST_ID NUMBER (4)
9. 6 );
11. Table created
13. SQL> insert into ORD values (1, sysdate, 100 );
15. 1 row inserted
17. SQL> COMMIT;
19. Commit complete
21. -- The table contains records, adds new columns, and specifies notNullTimes ORA-01758
23. SQL> ALTER TABLE ORD ADD price NUMBER (8, 2) NOT NULL;
25. Alter table ord add price NUMBER (8, 2) NOT NULL
27. ORA-01758: The table must be empty to add required (not null) Columns
29. -- Specify the default value for the new column, which can be added.
31. SQL> ALTER TABLE ORD ADD price NUMBER (8, 2) DEFAULT 0 NOT NULL;
33. Table altered
35. -- Clear the records in the table without specifying the default value. This method is rarely used.
36. SQL> TRUNCATE TABLE ORD;
38. Table truncated
40. SQL> alter table ORD drop column PRICE;
42. Table altered
44. SQL> ALTER TABLE ORD ADD price NUMBER (8, 2) NOT NULL;
46. Table altered
48. SQL>