Check if foreign key is being used

Asked

Viewed 883 times

0

I have 2 tables:

Functionary

  • idFunctioning

  • Name

  • Addressee

  • profession

Professional

  • idProfissao

  • officiating

Being that the profession of the working table is a foreign key of idProfissão of the Professional table.

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

php mysql

  • The correct is in the bank itself you do this treatment if the table depends on others. But what you can do is a select of all id table, with a where of id you want to know if it’s being used, which is not a good practice, but each case is a case.

  • MYSQL itself already takes care of it.

  • The idea would be to bring a code and explain to us your question/difficulty to help you. Which is not the case requested. Either way, a foreign key (when created) restricts the deletion of the record if it has a reference in another table.

  • https://answall.com/questions/152775/howto avoidr-a-excludes%C3%A3o-de-relational-data-atrav%C3%A9s-of-integrity-referential-n

1 answer

1


As I said, the database itself (in the example, Mysql) makes this deletion impossible. Follow an example print:

inserir a descrição da imagem aqui

The error that is displayed:

16:39:29 DELETE FROM tbprofissao WHERE id = 3 Error Code: 1451. Cannot delete or update a Parent Row: a Foreign key Constraint fails (wrock.tbfuncionario, CONSTRAINT tbfuncionario_ibfk_1 FOREIGN KEY (profissao) REFERENCES tbprofissao (id)) 0.328 sec




Edit (After Comment):

So do the following (3 is the desired id):

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

What this SQL does ?
A: It will delete the row from the table that does not exist in the working table

  • In mine excludes the two tables

  • Look at the change

  • I saw it. I just can’t change it now.

  • For what I needed, I just switched from Cascade to restrict and it worked More your solution works yes. Thanks.

  • If solved, mark as solved :)

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.