Problem in on delete no action why?

[minorcoder]

while i am trying to execute the following query it shows error. i need to create such type relation between 3 table. here i am showing example. this also show error.

create table tbl1(
    id1    number primary key,
    name1    varchar2(10)
);
 
create table tbl2(
    id2    number,
    name2    varchar2(10) primary key);
 
create table tbl3(
    id3    number,
    name3    varchar2(10),
    foreign key (id3) references tbl1(id1),
    foreign key (name3) references tbl2(name2) on delete no action
);
 

but when i run the following code it shows no error

create table tbl1(
    id1    number primary key,
    name1    varchar2(10)
);
 
create table tbl2(
    id2    number,
    name2    varchar2(10) primary key);
 
create table tbl3(
    id3    number,
    name3    varchar2(10),
    foreign key (id3) references tbl1(id1),
    foreign key (name3) references tbl2(name2) on delete cascade
);

why?????

[rski]

and the error is ...

[magicwand]

and the error is ... 

probably something like "ON DELETE NO ACTION is no combination of keywords Oracle supports. Please look up the CREATE TABLE statement ..."

[rski]

@magicwand
I think there is ON DELETE NO ACTION
http://download.oracle.com/docs/cd/E....htm#CNCPT1649

[Oralloy]

I may be confused here, but I think you're mistaking the "DELETE NO ACTION" DML statement for the "ON DELETE NO ACTION" clause.

This link was on the page you gave. It referrs to the "On Delete" clause.

[magicwand]

@rski:

oops, never noticed that.

Sorry ...

[amitpatel66]

Its not required to say "ON DELETE NO ACTION" because oracle supports ON DELETE CASCADE/ON DELETE SET NULL and if you do not specify ON DELETE CASCADE or ON DELETE SET NULL, by default it is set to "NO ACTION".

Try this:

create table tbl3( 
    id3    number, 
    name3    varchar2(10), 
    foreign key (id3) references tbl1(id1), 
    foreign key (name3) references tbl2(name2)); 
 

The above code will by default behave as "ON DELETE NO ACTION"