CODEWITHSUNDEEP
database
Lakshman
Lakshman

Reputation:

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

How to delete a records in a table which has a foreign key relation?

Suppose I have a 2 tables: employee and department, the first hast a deptID as a foreign key referencing the department id.

Now I want to delete a specific row in a table like delete from department where depID=10. It is okay if the employees in the department are deleted, and I also want to maintain the foreign key relation. What is the query for doing this?

Upvotes: 2

Views: 3871

Answers (5)

Rashmi Pandit
Rashmi Pandit

Reputation: 23798

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

Upvotes: 0

Ronald Wildenberg
Ronald Wildenberg

Reputation: 32094

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
)

Upvotes: 2

Aaron Alton
Aaron Alton

Reputation: 23236

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.

Upvotes: 0

BobbyShaftoe
BobbyShaftoe

Reputation: 28499

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.

Upvotes: 1

Sam Axe
Sam Axe

Reputation: 33738

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.

Upvotes: 0

Related Questions