Deleting a record in a table which has foreign key relational tables

Question

Hi guys i am new to database

my question is how to delete a records in a table which has foreign key relation suppose i have a 2 tables employee & department which has deptID as relation now i want to delete a specific row in a table like "delete from department where depID=10" its ok if the employees in the department are deleted and i also want to maintain the foreign key relation. can someone plz provide me query for doing this

Thanks

Answer 1

You should specify that the delete cascades to the employee table. Depends a little on your database how to do this but almost every database supports cascading deletes.

For example in SQL Server:

CREATE TABLE Employee
(
    DepId INT NOT NULL REFERENCES Department(DepId) ON DELETE CASCADE
)

Answer 2

In SQL Server you would mark the relationship as CASCASE DELETE

Alternatively, execute a DELETE against the Employees table first, and then a second DELETE against the Departments table.

Answer 3

It depends on the database and how you have defined your relationships. You could cause deletes to cascade which means that deleting a parent deletes the child records, or "deletes nullify," which causes the child relations to have NULLs in their FK fields, or what have you. It depends on your needs. Typically, I do not define NULLIFY or CASCADE and delete each record as needed without this being taken care of automatically.

Answer 4

When you establish the foreign key relationship, you can define (in MS SQL Server, anyway) what actions are taken on update and delete. For instance, you can say "when a record in department is deleted, cascade the delete to any child tables that reference it". You can find more info here.

Answer 5

DB2 syntax (since you have not specified which DB is being used):

Create table Employee
...
... 
FOREIGN KEY (deptID) REFERENCES department (deptID)
    ON DELETE CASCADE

If you specify CASCADE option, employees will be deleted if the dept gets deleted.

If instead of CASCADE, you specify RESTRICT it won't allow you to delete dept unless all employees belonging to the dept are deleted first.

Alternately if you do not want this to happen everytime, then its better to specify RESTRICT and do a delete first on emp wherever reqd

Delete * from Employee where deptID = 10;
Delete * from Dept where deptID = 10;

Here's a link to examples