How drop unnamed column constraint in Oracle

by GarciaPL on Thursday 16 April 2020

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

  1. Duplicate STATUS field into a new field STATUS2
  2. Define CHECK constraints on STATUS2
  3. Migrate data from STATUS into STATUS2
  4. Drop STATUS column
  5. 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;