Defining NO ACTION foreign key constraints in Oracle


In both Oracle and Sql Server, when creating a foreign key constraint on a child table, the default option is NO ACTION, meaning that this constraint will prevent the deletion or update of a parent key if there is a row in the child table that references the key.

One popular option is CASCADE, meaning when you delete a row in the parent table, the child table’s records that reference the key value of that row will be deleted automatically also.

However, if you try to change a foreign key constraint from CASCADE to NO ACTION in Oracle, you will have to drop the constraint first and recreate it. For example, the code below will not work:

ALTER TABLE Employee MODIFY (
CONSTRAINT Department_ID_FK
FOREIGN KEY (DepartmentID)
REFERENCES Department (DepartmentID)
ON DELETE NO ACTION)

You will get error ORA-00905: missing keyword if you run the script above.

Instead, you need to drop the constraint first using something like below:

ALTER TABLE Employee DROP CONSTRAINT department_id_fk;

Then, add the NO ACTION using the script below:

ALTER TABLE Employee ADD (constraint Department_ID_FK
FOREIGN KEY (DepartmentID)
REFERENCES Department (DepartmentID));

Now, if you try to delete a row in the parent table that is referenced in the child, you will get the error below:
ORA-02292: integrity constraint (Schema.ConstraintName) violated – child record found.

It took me hours of banging my head on the wall before somebody enlightened me that NO ACTION cannot be used in the script to define a NO ACTION constraint in Oracle.

If you are inclined, you can use the scripts below to do some testing of your own. One more way to verify is to browse through Schema view in Toad to see what kind of constraint it is.

create table Department (DepartmentID NUMBER(2) primary key,
DepartmentName varchar(20));

insert into department values (1, ‘Legal’);
insert into department values (2, ‘IT’);
commit;

create table Employee (EmployeeID number(4) primary key,
DepartmentID number(2),
FName varchar2(20),
LName varchar2(20));

alter table Employee add (constraint Department_ID_FK
foreign key (DepartmentID)
references Department (DepartmentID)
on delete cascade);

insert into employee values (1, 1, ‘Bob’, ‘Smith’);
insert into employee values (2, 2, ‘Susan’, ‘Smith’);
commit;


4 responses to “Defining NO ACTION foreign key constraints in Oracle”

  1. “It took me hours of banging my head on the wall before somebody enlightened me that NO ACTION cannot be used in the script to define a NO ACTION constraint in Oracle.”

    Thank you! It took me an hour of doing the same before seeing your post. It could have been longer!

    BTW, does that seem like Oracle to you to have a modifier work only silently? It doesn’t seem typical to me.

  2. Hi Michael,

    Glad it helped. I couldn’t believe that almost 12 years later, this article still has value!

    As to your question, I think it doesn’t seem typical. This underscores the importance of picking the right words to define things/behavior in a clear, succint way.

    Cheers!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.