Home » SQL & PL/SQL » SQL & PL/SQL » How to create Foreign key Constraint with Cascade Options
How to create Foreign key Constraint with Cascade Options [message #188045] Wed, 16 August 2006 20:12 Go to next message
just_a_kid
Messages: 16
Registered: December 2005
Junior Member
Hi Guys,


Supposed i have two tables REGIONS and COUNTRIES
where 1 REGIONS can have many COUNTRIES

then i create referential integrity constraint to COUNTRY as below

ALTER TABLE COUNTRIES ADD (
CONSTRAINT FKF6B7AA94F44646B8 FOREIGN KEY (REGION_ID)
REFERENCES REGIONS (REGION_ID));


The Problem is if update the REGION_ID in REGIONS it will give me exception REFERENTIAL INTEGRITY CHILD VIOLATED.

I know this is because i dont specify the cascade update/delete when adding the constraint.

How would i do that?
Can give me sample script/good URL to look for creating constraints with cascade options..

Thanks
Re: How to create Foreign key Constraint with Cascade Options [message #188050 is a reply to message #188045] Wed, 16 August 2006 21:21 Go to previous messageGo to next message
balaji1010
Messages: 35
Registered: March 2006
Location: London
Member

first execute

drop constraint <constraint name>;

then re create the constraint

alter table <table name> add (constraint <constraint name>
foriegn key(column name) references <tablename>(column name) on delete cascade);

that should solve your problem

good luck



Re: How to create Foreign key Constraint with Cascade Options [message #188052 is a reply to message #188050] Wed, 16 August 2006 21:22 Go to previous messageGo to next message
balaji1010
Messages: 35
Registered: March 2006
Location: London
Member

sorry sorry

execute

alter table <table name> drop constraint <constraint name>;

rest is the same

thanx
Re: How to create Foreign key Constraint with Cascade Options [message #188067 is a reply to message #188052] Wed, 16 August 2006 22:03 Go to previous messageGo to next message
just_a_kid
Messages: 16
Registered: December 2005
Junior Member
Hi Thanks..

Deleting the parent will cascade to its children is okay now

But what if i want to update the primary key of parent table
and all child table will be cascade.

I try to search but could not find on update cascade in oracle.

Any better way?



Thanks
Re: How to create Foreign key Constraint with Cascade Options [message #188073 is a reply to message #188067] Wed, 16 August 2006 23:04 Go to previous messageGo to next message
balaji1010
Messages: 35
Registered: March 2006
Location: London
Member

hi,

use 'ON UPDATE CASCADE'

this option would allow you to update child records in child table when you attempt to update records in parent table.

-----------------------

other option is 'ON UPDATE RESTRICT'

this option would restrict you from updating any child records when you attempt to update records in parent table.

Re: How to create Foreign key Constraint with Cascade Options [message #188078 is a reply to message #188073] Thu, 17 August 2006 00:20 Go to previous messageGo to next message
just_a_kid
Messages: 16
Registered: December 2005
Junior Member
Hi,

I couldnt do that in Oralce

ALTER TABLE COUNTRIES ADD (
CONSTRAINT FKF6B7AA94F44646B8 FOREIGN KEY (REGION_ID)
REFERENCES REGIONS (REGION_ID)
ON UPDATE CASCADE );

//ON DELETE CASCADE works well

This will give me missing keyword
I think oracle does not support ON UPDATE CASCADE

is there any better way to do this so if i update parent id
all child referentiate to that parent will be updated also.

Oh yah by the way i am using Oracle10g.


Thanks.
Re: How to create Foreign key Constraint with Cascade Options [message #188079 is a reply to message #188078] Thu, 17 August 2006 00:35 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

i think you are looking out for this.


http://asktom.oracle.com/~tkyte/update_cascade/index.html



regards,
Re: How to create Foreign key Constraint with Cascade Options [message #188082 is a reply to message #188079] Thu, 17 August 2006 00:52 Go to previous messageGo to next message
just_a_kid
Messages: 16
Registered: December 2005
Junior Member
Thanks..

One more thing.
why can't we just declare "on update cascade" when we create a constraint.
is there any reason why oracle prohibit this?

For the url u give me , it seems that we need to add aditional package for this which is cumbersome if in my application
i need to update the parent id oftenly means i need to exec this before updating the parent id ??
Re: How to create Foreign key Constraint with Cascade Options [message #188098 is a reply to message #188082] Thu, 17 August 2006 02:13 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think the underlying problem you've got is that you have used a value for your primary key that has a meaning.
If you had used a simple sequence generated number for your PK, then you could update the column in the Parent table that had the meaningful data in it without causing any problems at all.

Just bad schema design I'm afraid.

The other approach is to make the FK constraints deferrable, so that they are only validated at the point when the transaction is committed. This will let you update the PK and then the FK values without causing an error.
Previous Topic: max 5
Next Topic: Help querying status changes
Goto Forum:
  


Current Time: Tue Dec 06 10:06:42 CST 2016

Total time taken to generate the page: 0.05959 seconds