Check if foreign key is being used

0

I have 2 tables:

Officer

  • Employee_id

  • Name

  • Address

  • Profession

Profession

  • idProfession

  • NameProfession

Being that profession of the official table is a foreign key of ProfessionProfession table id.

I wanted a PHP code that would check if the foreign key is being used, so I can block its deletion.

    
asked by anonymous 26.01.2018 / 19:25

1 answer

1

As I said, the database itself (in the example, MySQL) precludes this exclusion. Here is a sample print:

The error that is displayed:

  

16:39:29 DELETE FROM tbprofissao WHERE id = 3 Error Code: 1451. Can not   delete or update a parent row: a foreign key constraint fails   (% with%.% of%, CONSTRAINT% with% FOREIGN   KEY ( wrock ) REFERENCES tbfuncionario ( tbfuncionario_ibfk_1 )) 0.328 sec



Edit (After Comment):

Then, do the following (the 3 is the desired id):

DELETE FROM tbprofissao WHERE tbprofissao.id NOT EXISTS ( SELECT tbfuncionario.profissao FROM tbfuncionario WHERE tbfuncionario.profissao = 3);

What does this SQL do? A: It will delete the row of the table tbprofissao that does not exist in the official table

    
26.01.2018 / 19:41