title: Deleting a row with a foreign key constraint with a Before Delete TRIGGER in MySql
body: <p>I have a table called 'estoque' with a foreign key that references another table called 'produto'. Then I've populated both tables with a few rows.</p>
  
  <p>Here are my tables:</p>
  
  <pre><code>    CREATE TABLE `produto` (
        `ID` int(11) NOT NULL AUTO_INCREMENT,
        `NOME` varchar(45) NOT NULL,
        `PRECO` float NOT NULL,
        PRIMARY KEY (`ID`),
        UNIQUE KEY `NOME_UNIQUE` (`NOME`)
      );
  
      CREATE TABLE `estoque` (
        `ID` int(11) NOT NULL AUTO_INCREMENT,
        `ID_PRODUTO` int(11) NOT NULL,
        `QUANTIDADE_PRODUTO` int(11) NOT NULL DEFAULT '0',
        PRIMARY KEY (`ID`),
        KEY `fk_Estoque_Produto1_idx` (`ID_PRODUTO`),
        CONSTRAINT `fk_Estoque_Produto1` FOREIGN KEY (`ID_PRODUTO`)
        REFERENCES `produto` (`ID`)
      );
  </code></pre>
  
  <p>I need 'estoque' to always reference all existing rows on 'product'. So I'e created an AFTER INSERT and an AFTER UPDATE triggers on product:</p>
  
  <pre><code>    CREATE TRIGGER `cadastrar_novo_produto_no_estoque` 
      AFTER INSERT ON `produto` 
      FOR EACH ROW 
      INSERT IGNORE INTO estoque (ID_PRODUTO)
      VALUES (NEW.ID);
  </code></pre>
  
  <p>EDIT: Actually, since I can't alter the 'ID' column on 'produto' because it is a primary key, I've think I don't need the AFTER UPDATE trigger at all. Am I right?</p>
  
  <pre><code>    CREATE TRIGGER `atualizar_novo_produto_no_estoque` 
      AFTER UPDATE ON `produto` 
      FOR EACH ROW 
      UPDATE estoque
          SET estoque.ID_PRODUTO = NEW.ID
          WHERE OLD.estoque.ID_PRODUTO = OLD.ID;
  </code></pre>
  
  <p>Now I need a trigger so that every time I delete a row from 'product', it also deletes the corresponding row in 'estoque'.</p>
  
  <p>I've tried creating one like this:</p>
  
  <pre><code>    CREATE TRIGGER `deletar_produto_inexistente_no_estoque` 
      BEFORE DELETE ON `produto` 
      FOR EACH ROW DELETE FROM estoque
          WHERE estoque.ID_PRODUTO = ID;
  </code></pre>
  
  <p>But whenever I try to delete a row from 'produto' I get the following error:</p>
  
  <pre><code>ERROR 1175: 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
  SQL Statement:
  DELETE FROM `papelaria`.`produto` WHERE (`ID` = '6')
  </code></pre>
  
  <p>So I've tried it with the OLD keyword, as such:</p>
  
  <pre><code>        CREATE TRIGGER `deletar_produto_inexistente_no_estoque` 
          BEFORE DELETE ON `produto` 
          FOR EACH ROW DELETE FROM estoque
              WHERE OLD.estoque.ID_PRODUTO = OLD.ID;
  </code></pre>
  
  <p>And then I get this error instead:</p>
  
  <pre><code>ERROR 1054: 1054: Unknown column 'OLD.estoque.ID_PRODUTO' in 'where clause'
  SQL Statement:
  DELETE FROM `papelaria`.`produto` WHERE (`ID` = '6')
  </code></pre>
  
  <p>What am I missing or doing wrong?</p>
  
  <p>P.S.: Not sure if it's worth mentioning but I'm fairly new to sql and programming in general, so I'd appreciate it if you took it into consideration when answering (for all purposes, just assume I don't know anything about anything ^^)</p>
  
  <p>Thank you in advance!</p>