What steps will reproduce the problem?
When you create a new table with a constraint on column STATUS, it's difficult later on to amend that constraint as Oracle assigns a random name for it
CREATE TABLE MY_TABLE (
ID NUMERIC(19,0) NOT NULL,
STATUS NVARCHAR2(20) NOT NULL CHECK (STATUS IN ('OPEN', 'CLOSED'));
);
What is the expected result?
Getting a warning that it's better to create column constraint using a separate statement like
ALTER TABLE MY_TABLE ADD CONSTRAINT STATUS_VALIDATION CHECK (STATUS IN ('OPEN', 'CLOSED'));Link to a ticket - https://youtrack.jetbrains.com/issue/DBE-10549
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;
