CODEWITHSUNDEEP
sqlrelational-databaserdbmsdatabase-integrity
CJ7
CJ7

Reputation: 23295

How to handle deletion in relational databases?

If a relational database has, for example, a table Suppliers, where values of Suppliers.ID are used in columns of other tables such as Invoice.Supplier, what should happen if a user wants to "remove Supplier 15 from the system"?

Upvotes: 1

Views: 2605

Answers (3)

Dale M
Dale M

Reputation: 2473

To elaborate on the comment by @DanBracuk.

Add a column to your table InActive bit NOT NULL DEFAULT 0.

When you "delete" that supplier set this to 1 and no longer display it in your user interface, except in a master list of suppliers so that it can be turned back on

Most commercial accounting packages use something like this, in most cases if you know the name of an inactive supplier you can type it into a dropdown (and it will be accepted) but it does not appear on the dropdown list.

Upvotes: 0

Eran
Eran

Reputation: 393946

It depends on the nature of the relationship between and Invoice and Supplier :

Can an Invoice exist without a supplier? If it can, you may decide to nullify all the columns that refer to the deleted supplir. If it can't, you can either decide to delete the Invoices that belong to the deleted supplier (if you can consider the invoice to be a sub-object of the supplier object), or you can prevent the deletion of the supplier as long as there are any invoices refering to it.

Upvotes: 0

whastupduck
whastupduck

Reputation: 1166

If Supplier 15 has a record in the Invoice table. The rdbms will throw a referential integrity error since Supplier 15 is related and has a record in other tables.

Handling this kind of deletion would depend on your business rules. Should you delete related records first or you can prevent the user from deleting the record if it is used in other tables.

Upvotes: 2

Related Questions