If constraint on column STATUS was created without a name during creating a table, Oracle will assign a random name for it. Unfortunately, we cannot modify the constraint directly.
Steps involved of dropping unnamed constraint linked to column STATUS
- Duplicate STATUS field into a new field STATUS2
- Define CHECK constraints on STATUS2
- Migrate data from STATUS into STATUS2
- Drop STATUS column
- Rename STATUS2 to STATUS
ALTER TABLE MY_TABLE ADD STATUS2 NVARCHAR2(10) DEFAULT 'OPEN';
ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_CHECK_STATUS CHECK (STATUS2 IN ('OPEN', 'CLOSED'));
UPDATE MY_TABLE SET STATUS2 = STATUS;
ALTER TABLE MY_TABLE DROP COLUMN STATUS;
ALTER TABLE MY_TABLE RENAME COLUMN STATUS2 TO STATUS;
