Home » SQL & PL/SQL » SQL & PL/SQL » Deleting a row but keeping the Foreign Key (Oracle 10g)
Deleting a row but keeping the Foreign Key [message #341603] Tue, 19 August 2008 08:55 Go to next message
amardilo
Messages: 37
Registered: February 2007
Member
Hi,

I am not sure if this is possible or answered elsewhere or makes sense but here goes.....

I have an existing table (i.e. address table) which has an ID column which is used as a foreign key in a lot of different tables (i.e. delivery table).

I need to remove a few rows from this table however there are loads of other tables which reference the rows ID as a foreign key and I can't delete those.

Is there a way to delete these rows without effecting the tables that reference it as a foreign key? Is the only way to drop the constraint or can I alter it in some way to enforce the constraint on Inserts and Updates (and to a lessor extent Selects) but not being enforced for deletes?
Re: Deleting a row but keeping the Foreign Key [message #341604 is a reply to message #341603] Tue, 19 August 2008 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can set the option "on delete set null" then foreign value are nullified when the parent is deleted.

Regards
Michel
Re: Deleting a row but keeping the Foreign Key [message #341613 is a reply to message #341604] Tue, 19 August 2008 09:47 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
Thanks for that.

Do you know how I would alter existing constraint?

Is there a way to maintain the ID or stick in a dummy ID?
Re: Deleting a row but keeping the Foreign Key [message #341621 is a reply to message #341613] Tue, 19 August 2008 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't alter an existing constraint you have to recreate it (afaik).

No way to have a dummy ID (Oracle does not support "on delete set default" or the like), but isn't NULL a "dummy" ID?

Regards
Michel
Re: Deleting a row but keeping the Foreign Key [message #341623 is a reply to message #341621] Tue, 19 August 2008 10:19 Go to previous message
amardilo
Messages: 37
Registered: February 2007
Member
Michel Cadot wrote on Tue, 19 August 2008 10:15
You can't alter an existing constraint you have to recreate it (afaik).

No way to have a dummy ID (Oracle does not support "on delete set default" or the like), but isn't NULL a "dummy" ID?

Regards
Michel



Thanks for the info.
Previous Topic: SQL Function query
Next Topic: how to truncate a specific number range
Goto Forum:
  


Current Time: Fri Dec 02 23:21:40 CST 2016

Total time taken to generate the page: 0.11523 seconds