0

I am developing an web app using asp.net. I am making this app for compatable for both SQL and MYSQL DB.

So my concern is think that I have a set of records in a table. This table's records are referenced by other tables. So if a user try to delete a record from this table I have to check whether this record is referenced by other tables or not. If not then user can delete the record. I am using using foreign keys for many tables but others not.

So I want this scenario for every tables. So method that coming to my mind is before delete a record I have to run some select queries against those tables to check whether if records available. So is this the only approach.? Seems to its headache. you know if table is referenced by lot of tables. Can I use a flag or some thing? Is there any better way to do this?

Prageeth Liyanage
  • 1,612
  • 2
  • 19
  • 41
  • do you have foreign key references in the table? – user4221591 May 29 '15 at 06:06
  • Some of and some of not – Prageeth Liyanage May 29 '15 at 06:08
  • search via using primary key of the row the user want to delete if that primary key exists in the other referenced table then do not delete. – user4221591 May 29 '15 at 06:09
  • Yes. thats is the way to do it. But if I have many tables referencing it I have to do that for every table right? Is it the only solution? – Prageeth Liyanage May 29 '15 at 06:10
  • 1
    Any amount of checking *you* perform is going to be wasted effort because there's no way to communicate to the server "trust me, it's safe to delete this row" - the server's going to check all of the foreign keys *again* before it allows the delete to happen. So, I'd say get all of the foreign keys correctly set up in the database and then let it deal with referential integrity - that is one of the things it's meant to be good at. – Damien_The_Unbeliever May 29 '15 at 08:41

3 Answers3

0

I think this might help you ::

SELECT 
  table_name, column_name     
FROM
  information_schema.key_column_usage
WHERE
  referenced_table_name = '<table>'
  and referenced_column_name = '<primary key column>'

Please check this link too: MySQL: How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys?

Community
  • 1
  • 1
user4221591
  • 2,084
  • 7
  • 34
  • 68
0

I think it is a little overkill and not performance optimized to be selecting tables and references to check before each delete. You will be making unnecessary database calls.

Since you tag'd ASP.Net are you using ADO ? If so, or similar. Why not make the normal delete call inside a try block and in the catch handle error message received from database something like:

try
{

}
catch(SqlExcpetion sqlEx)
{
    if(sqlEx.Message.ToLower().Contains("foreign"))
    {
        return "your user friendly error message";
    }
}
EaziLuizi
  • 1,557
  • 1
  • 16
  • 25
0

In case you are using foreign keys to constraint the references, you can act in the following order:

consider you are using database test and are trying to delete a row from emp table

1) list all the tables with their column names, that reference any column in the table we are going to remove a row from (emp in this case)

select 
  table_name,column_name,referenced_column_name 
from 
  information_schema.KEY_COLUMN_USAGE
where
  REFERENCED_TABLE_NAME = 'emp' and REFERENCED_table_schema = 'test';

2) for each row of the result try looking up the value of referenced_column_name from the emp row that is being removed in the corresponding table_name.column_name

kovalad
  • 174
  • 1
  • 6