Oracle SQL: Delete Rows with Foreign Key Constraints?

Question

I am doing a Computer Science project where I am modeling a video rental store. I have a table called Employees which holds all info on the employees(ID, Name, Address, etc.) and a table called Sales and one called Rentals. They hold transaction info for sales and rentals of videos, both of which have an employee associated with them who conducted the sale or the rental.

I need to write SQL to add or delete an employee to the database. If I add one that's easy, I just add a row to the employee table. Deleting one is harder because if I want to delete an employee and he has been involved in sales or rentals his ID will be in the SALE or RENTAL table and I get foreign key constraint errors in trying to delete him from the employee table.

How can I write SQL to delete an employee from the employee table and preferrably leave all of his associated transactions in place in the SALES or RENTAL table. I'm using Oracle SQL developer by the way

Answer 1

Hi,



Modify constraint (foreign key) on SALE or RENTAL with delete cascade option



ALTER TABLE sale

add CONSTRAINT fk_employee

FOREIGN KEY (employee_id)

REFERENCES employee(employee_id)

ON DELETE CASCADE;



After successfully execution of above command you can delete data from EMPOYEE if it has reference to SALE table with foreign key. Meaning of above command is "whenever data is deleted from primary key same time data from child table with reference also deleted automatically" Be careful to use this command.



Thanks and regards,

Gitesh Trivedi

http://www.dbametrix.com/oracle-dba-tips.html

Answer 2

Delete Row Oracle

Answer 3

Many DBMS allow foreign keys to be NULL, so you could update your sale and renatl table first and set the employee-id to NULL, before you delete the employee.



In a real-world application you would rather not delete the employee at all but have an additional flag to indicate if it is an active or deleted employee - so all transactions remain replicable even after the employee has been 'deleted'.

Answer 4

because of the fact the others state, your relation is the incorrect way around. client does not have a distant places key connection with Appointment, Appointment has a distant places key connection with client. (think of of it this type - you will possibly desire to completely have a consumer with out them having any appointments, even nevertheless you are able to't have an appointment with out having a consumer.) in addition to, a distant places key commonly is composed of the entire key of the appropriate table - ClientID is the entire key of client, yet in user-friendly terms area of the concatenated key for Appointment.