Question:
A table, A table and table B, C, D
Watch table B C D refers to the table in A record ID (not set foreign key constraints)
When delete A record in table, how to determine whether A record by watch B or C or table D reference, if the reference is not allowed to delete,
Methods: to delete A record in table, in turn, the query table B, C, D table if there is A table A record ID, if present, are not allowed to delete
One disadvantage: if later added to the table in A record A reference table, and modify the code or SQL
Disadvantages. 2: if you have A lot to the table reference table when A record, to query A lot of table, waste performance,

could you please tell me whether you have any other better solution?

CodePudding user response:

You add A few columns, A table on behalf of the other tables have time id record
Such as call LockB LockC, LockD value of 0 | 1
When b table inserted into the corresponding value LockB assignment 1, and so on
When A table delete data, whether these columns are 0 can

CodePudding user response:

This is why the database need foreign key constraints,
1. The foreign key constraints in order to prevent this kind of incomplete data;
2. Is a foreign key with a index in order to improve the performance,

Somebody has done, the database application and do not have to do, do have to change the code application, there is A problem, when you delete A data query table without reference to other data; Code to delete, just someone to insert A B table data, quotes A deleted data,

So, you don't have to database constraints have to ensure the integrity; Or be able to tolerate temporary incomplete, backstage data cleaning regularly,

CodePudding user response:

Give you an example, the Chinese used to have a university, called Norman Bethune medical university, the school has been incorporated into the jilin university,
Now we have a table of the system, and the name of the stored all of China's university,
The demand now is in the high school students choose to enter oneself for an examination of university, cannot choose Bethune medical university,
However, before someone has graduated from doctor Bethune medical university (ta graduated at jilin university in Norman Bethune medical university has not yet been merged,), ta school also show that Bethune medical university,
, the thinking to solve this problem, in storage and out of all the name of the university of China, and increase the field, identify whether the school is still exists,
When high school students can choose to enter oneself for an examination of university, according to all schools remains,
In the show that the doctor had graduated from school, does not consider the existence of the school and
Above, is I think the train of thought, to solve the problem of you
Usually mature system, won't consider direct physical delete, but through the sign bit, implementation logic deleting,
Even if you must delete unused statement, also have to configure corresponding relation to tables and fields, you can't judge, system to
If, A table with fields A1, this field has value of 123 data, B table with field B1 and B2, if A good table related to table B, and B table B1 and B2 fields have irregular 123 values, this system could not determine which fields are used to B1 and B2 and A1 associated,
Finally, it is not recommended to use foreign keys, began to design is very tall, late can torture the dead, optional baidu about the problems caused by foreign keys, just know that the key problems how bad,