Getting ORA-00905 error when trying to add a foreign key

I'm trying to perform the following statement into oracle:

alter table COMENTARIO
add constraint FK_COMENTARIO_DI foreign key (DI_ID)
  references DATO_DE_INTERES (DI_ID) ON UPDATE CASCADE ON DELETE SET NULL;

However, I get ORA-00905 missing keyword.

When I remove the ON UPDATE statement though, the command works without any problem. Why is this? Is there any options in case I can't use ON UPDATE? Thank you beforehand!

Answers


There is no "ON UPDATE" clause for cascading constraints. I don't know of any alternatives aside from some sort of application enforcement or triggers.


There is no such option as ON UPDATE CASCADE in Oracle. Maybe instead of looking for a way to implement this (I think it is possible with ON UPDATE trigger) you could tell why do you need this. I mean - why would you want to update the primary key of DATO_DE_INTERES?


In Oracle, there is no ON UPDATE clause in a constraint definition. In the vast majority of cases, you wouldn't want to implement this "just in case" because primary keys should be immutable. If your primary keys are not immutable, that would generally be indicative of a data model problem that should be addressed rather than coded around.

That said, if you really want to implement something like that, Tom Kyte does have an update cascade package. But you're far better off designing the system to avoid the problem in the first place rather than designing in this level of complexity.


Need Your Help

How to set System.Drawing.Image to System.ui.WebControls.Image in datalist template item

c# asp.net datalist system.drawing

I am making a Bookstore website. I use Visual Studio 2010 and MS SQL Database. I have images about books. I have a Book table in database and there is an image column in this table.

how to subtract system time from user defined time

java android

can anybody suggest me to find interval between system time and user defined time as my hour =20 and minute =12.I just want to find interval between this two times and need to set this time for cou...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.