Oracle 12c - I cannot modify constraint of column ORA-02275

Question

I create a table ASSIGNMENTS:

CREATE TABLE assignments (
    id_project NUMBER(4) NOT NULL CONSTRAINT fk_assignments_01 REFERENCES projects (id_project),
    empl_number NUMBER(6) NOT NULL CONSTRAINT fk_assignments_02 REFERENCES employees (empl_id),
    start DATE DEFAULT SYSDATE,
    end DATE,
        CONSTRAINT chk_assignements_dates CHECK (end > start),
    rate NUMBER(7, 2),
        CONSTRAINT chk_assignements_rate CHECK (rate > 0),
    role VARCHAR2(20),
        CONSTRAINT chk_assignements_role CHECK (rola IN ('ANALYST', 'DEVELOPER'))
);

And now I have to modify constraint fk_assignments_02. I want to set fk_assignments_02 ON DELETE CASCADE. I tried do it as following:

ALTER TABLE assignments
    MODIFY id_project NUMBER(4) NOT NULL CONSTRAINT fk_assignments_01 REFERENCES projects (id_project) ON DELETE CASCADE;

But I get an error: ORA-02275: such a referential constraint already exists in the table

I want to know how to modify it, I don't want re-create table. It's not a solution, it's a hack. — user
Were you able to create the table with that statement? I strongly doubt it. You have a typo: in the last check constraint, rola IN ... - the name of the column is role, not rola. start is an Oracle reserved word, so it can't be used as a column name. What's the problem, is COPY AND PASTE broken on your machine? — mathguy

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2017-06-24T23:25:18

You can't do that - you'll have to drop and re-add the constraint. There is no provision in Oracle to change the ON DELETE action, which is part of the REFERENCES clause rather than being part of the constraint state.

So to accomplish this you'd need to do

ALTER TABLE ASSIGNMENTS DROP CONSTRAINT FK_ASSIGNMENTS_02;

followed by

ALTER TABLE ASSIGNMENTS
  ADD CONSTRAINT FK_ASSIGNMENTS_02
    FOREIGN KEY (ID_PROJECT) REFERENCES PROJECT (ID_PROJECT)
      ON DELETE CASCADE;

Best of luck.

Oracle 12c - I cannot modify constraint of column ORA-02275

1 Answers