How to avoid deletion of relational data through referential integrity in MYSQL?

Asked

Viewed 1,602 times

5

I have the tables SIMULATED and QUESTAO, where a simulated can have several questions, but a question can only be for a simulated (relationship 1-n).

MOCK TABLE:

simuladoId
simuladoNome

TABLE QUESTION

questaoId
questaoPergunta
questaoIdSimulado

I would like to know how to prevent these relational data from being erased. That is, when I delete a simulated one that has its foreign key propagated for one or more question, is issued an error, preventing.

From the little I researched I understood that, at least in MYSQL, it is possible through Triggers.. But I understand little of the subject, so I would like some other option, or even some examples of Trigger.

I tried the following:

alter table ang_questao add constraint FK_ang_questao foreign key (questao_id_simulado) references ang_simulado (simulado_id) ON DELETE RESTRICT

I ran and no error was displayed in SQL.

When I tried to erase it, it was shown to me:

Error while Deleting: SQLSTATE[23000]: Integrity Constraint Violation: 1451 Cannot delete or update a Parent Row: a Foreign key Constraint fails (simulado_anglo.ang_questao, CONSTRAINT FK_ang_questao FOREIGN KEY (questao_simulado_id) REFERENCES ang_simulado (simulado_id))

Line Error: #10 :: Cannot Modify header information - headers already sent by (output Started at C: xampp htdocs simulado-geral config.php:33) C: xampp htdocs simulado-geral admin php deletar.php

mysql sql trigger

  • Thiago, do you want to REALLY delete, or prevent it from being deleted? In the case of foreign keys in other tables, as in the case you have a Simulated 10 questions, but when you delete from this error, the way is you through Trigger remove the foreign key from the issues where it was his ID put NULL or direct to another simulated, or a Trigger deleting the questions first and then deleting the questionnaire OR when creating the table places the ON DELETE CASCADE in Foreign key. That then when deleting the simulated questions are deleted.

  • I want to know what it would look like if I wanted both options: When to delete a simulated, cascade out the related questions, or else not allow the simulated one to be erased.

  • You can give a SHOW TRIGGERS on the console. Expensive depending on the use of the simulated instead of you delete REALLY from the bank you can create a column in the simulated give the active and inactive update, is a tip. But there in the case to set the on delete Check out here http://stackoverflow.com/a/18616544/6405917 that’s it.

  • A status column would actually be much more viable, because if you think about it soon, it would be possible to reuse it.. Thanks for the tip! And regarding Trigger, I managed to give a read on the link. Thanks.

  • 1

    Yes if you think for this side is interesting to reuse instead of delete even the data. It’s like the facebook account. There is the ACCOUNT table and it is not allowed to delete the account in its entirety, only disabling, if you want the same delete has to send email and such and there is a whole bureaucracy because of the data. Serves information also worth.

  • @Gustavotinoco, I left with RESTRIC, and now it looks like this: When I TRY to delete a question the same error appears. The idea of logic should be as follows: When deleting a simulated one that contains questions, make a mistake. However, when deleting a question, that’s fine.. Because a question depends on a simulated one, but the same does not depend on a question.

  • 1

    Just like @Gustavotinoco, I encourage the creation of a statusative or inactive column, so it is better to manage, can reuse questions and if you are dealing with programming in a general and not just database, has how in your application to make a page to manage inactive questions, to be able to edit them and whatever you need.

Show 2 more comments

1 answer

1

Good afternoon Thiago,

I want to know what it would look like if I wanted both options: When to delete a simulated, cascade out the related questions, or else not allow the simulated one to be erased.

1) For when deleting a Simulated Cascade Delete Questions, you must use "on delete Cascade" in the Foreign Key Filter.

2) In order not to allow the Simulated to be deleted having at least one Question, you have already done this by creating the Foreign Key Constraint normally.

My Example:

1) Create the tables:

mysql> create table simulado
-> (simuladoId int not null auto_increment,
->  simuladoNome varchar(40) not null,
->  primary key (simuladoId)
-> ) engine=innodb;
Query OK, 0 rows affected (1,25 sec)

mysql> create table questao
    -> (questaoId int not null auto_increment,
    ->  questaoPergunta varchar(40) not null,
    ->  questaoIdSimulado int not null,
    ->  primary key (questaoId),
    ->  constraint fk_questao_questaoIdSimulado foreign key (questaoIdSimulado) references simulado (simuladoId)
    -> ) engine=innodb;
Query OK, 0 rows affected (0,57 sec)

2) Enter the records:

mysql> insert into simulado values (1, 'Simulado do Fernando');
Query OK, 1 row affected (0,06 sec)

mysql> insert into questao values (1, 'Pergunta 1', 1);
Query OK, 1 row affected (0,02 sec)

mysql> insert into questao values (2, 'Pergunta 2', 1);
Query OK, 1 row affected (0,07 sec)

3) Now try to delete the Simulated, will not let:

mysql> delete from simulado where simuladoId=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`teste`.`questao`, CONSTRAINT `fk_questao_questaoIdSimulado` FOREIGN KEY (`questaoIdSimulado`) REFERENCES `simulado` (`simuladoId`))

4) If I want to delete the Simulated child records, I must change the Constraint in the child table (Questao) to cascade deletion, example:

mysql> alter table questao drop foreign key fk_questao_questaoIdSimulado;
Query OK, 0 rows affected (0,52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table questao add constraint fk_questao_questaoIdSimulado 
    -> foreign key (questaoIdSimulado) references 
    -> simulado (simuladoId) on delete cascade;
Query OK, 2 rows affected (1,08 sec)
Records: 2  Duplicates: 0  Warnings: 0

5) Now by deleting the Simulated parent record automatically deletes the Questions child records:

mysql> delete from simulado where simuladoId=1;
Query OK, 1 row affected (0,02 sec)

mysql> select * from simulado;
Empty set (0,00 sec)

mysql> select * from questao;
Empty set (0,01 sec)

Browser other questions tagged mysql sql trigger

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