CODEWITHSUNDEEP
sqloracle-databaseoracle12c
user
user

Reputation: 4840

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

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

Upvotes: 0

Views: 249

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

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.

Upvotes: 2

Related Questions