How BEFORE UPDATE triggers can be used to emulate CHECK CONSTRAINT for updating values in the table?

MySQLMySQLi Database

<p style="">As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below &minus;</p><h2>Example</h2><p>Suppose we have a table named &lsquo;car&rsquo; which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows &minus;</p><pre class="prettyprint notranslate">mysql&gt; Create table car (number char(9)); Query OK, 0 rows affected (0.32 sec)</pre><p style=""><strong>Creating BEFORE UPDATE trigger to emulate CHECK CONSTRAINT for updating the values &minus;</strong></p><p>Now, suppose if we will try to update the table with an incorrect value then MySQL is not going to stop us to do so as follows &minus;</p><pre class="prettyprint notranslate">mysql&gt; update car set number=&#39;AB-2X5-YZ&#39;; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0</pre><p style="">The above query has updated the incorrect value in the table &lsquo;car&rsquo;. In the following query we are going to create a BEFORE UPDATE trigger which will prevent us to update incorrect data in the table &minus;</p><pre class="prettyprint notranslate">mysql&gt; delimiter // mysql&gt; create trigger car_update_value before update on car -&gt; for each row -&gt; begin -&gt; if new.number not rlike &#39;^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$&#39; -&gt; then -&gt; &nbsp;signal sqlstate &#39;45000&#39; set message_text = &#39;Not a valid number&#39;; -&gt; end if; -&gt; end // Query OK, 0 rows affected (0.11 sec) mysql&gt; Delimiter ; mysql&gt; update car set number=&#39;AB-2X5-YZ&#39;; ERROR 1644 (45000): Not a valid number</pre>
raja
Updated on 22-Jun-2020 12:59:22

Advertisements