-3

How can I do on Update Cascade in Oracle? I read that this is not possible. It is allowed in mysql or plsql. But how can make the same in Oracle?

example:

create table section(
id int not null primary key );

Create table student(
id int not null primary key,
name varchar2(30),
numSec int not null,
Constraint fk_section Foreign key(numSec) references section(id) ON UPDATE CASCADE);
Mehdi
  • 2,160
  • 6
  • 36
  • 53
  • Not possible. Oracle simply does not support this. (It's most definitely not allowed in "PL/SQL" because that's Oracle's language for stored procedures. And a stored procedure has no foreign keys) –  Feb 28 '18 at 13:33
  • As I said: Oracle does not support `on update cascade` –  Feb 28 '18 at 13:34
  • So, How can I update a foreign key in a referenced table ? – Mehdi Feb 28 '18 at 13:35
  • 1
    With an `update` statement –  Feb 28 '18 at 13:39
  • Why do you want to change the primary key value? They are usually static (partly for this reason) - an argument for synthetic keys, which you *seem* to have here anyway as they are numbers. – Alex Poole Feb 28 '18 at 13:39
  • I want to change its datatype for example ! – Mehdi Feb 28 '18 at 13:41
  • That would be an alter not an update, a very different thing *8-) – Alex Poole Feb 28 '18 at 13:42
  • 2
    That is even worst! To change a data type you would need to drop all constraints for all related tables change all the leaf tables data type (using alter) columns, then the table per se and after that recreate the constraints. – Jorge Campos Feb 28 '18 at 13:43
  • Realyl !!! there is no a simple query :((( – Mehdi Feb 28 '18 at 13:44
  • And it is not allowed in any database in the market also. Your question is complete miss leading about your objective. No database will allow you to change a column datatype cascading it. – Jorge Campos Feb 28 '18 at 13:50
  • @Mehdi, you seem to be confusing DML with DDL. Cascading an update would be an UPDATE, which is DML and something that you might program into an application. Changing a datatype, however, is DDL - you would use ALTER TABLE for this, after temporarily disabling the foreign key. This cannot be cascaded automatically because this is a maintenance task, not typically something you would program into an application. – Jeffrey Kemp Mar 02 '18 at 10:08

1 Answers1

1

it is not possible.

Oracle does not support on update cascade.

CompEng
  • 7,161
  • 16
  • 68
  • 122