snipt

Ctrl+h for KB shortcuts
Sign up for : store and share your own code snippets.
#74369

MySQL

Self protect read messages

DROP TABLE IF EXISTS message;
CREATE TABLE message(
 `id` INT NOT NULL AUTO_INCREMENT,
 `read` INT NULL,
 `text` VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

ALTER TABLE message 
ADD FOREIGN KEY (`read`) REFERENCES message(`id`);


INSERT INTO message(`text`) VALUES ('this is a new message');

UPDATE message
SET `text` = 'this message has been read',
    `read` = `id`;

INSERT INTO message(`text`) VALUES ('this is a new message');
SELECT * FROM message;

DELETE FROM message WHERE IFNULL(`read`,0) = 0;
SELECT * FROM message;

--This next deletion is protected due `read` FK reference
DELETE FROM message WHERE IFNULL(`read`,0) != 0;

SELECT * FROM message;

Description

This snippet shows how to self protect a table in order to prevent unwanted deletes
https://snipt.net/embed/636df62ab79a3811807ab3808d2a2ef4/
https://snipt.net/raw/636df62ab79a3811807ab3808d2a2ef4/
636df62ab79a3811807ab3808d2a2ef4
mysql
MySQL
28
2015-05-27T23:50:23
True
False
Aug 15, 2013 at 06:59 AM
/api/public/snipt/74369/
self-protect-read-messages
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a> <a href="#L-18">18</a> <a href="#L-19">19</a> <a href="#L-20">20</a> <a href="#L-21">21</a> <a href="#L-22">22</a> <a href="#L-23">23</a> <a href="#L-24">24</a> <a href="#L-25">25</a> <a href="#L-26">26</a> <a href="#L-27">27</a> <a href="#L-28">28</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="k">DROP</span> <span class="k">TABLE</span> <span class="k">IF</span> <span class="k">EXISTS</span> <span class="n">message</span><span class="p">;</span> </span><span id="L-2"><a name="L-2"></a><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="nf">message</span><span class="p">(</span> </span><span id="L-3"><a name="L-3"></a> <span class="ss">`id`</span> <span class="kt">INT</span> <span class="k">NOT</span> <span class="no">NULL</span> <span class="kp">AUTO_INCREMENT</span><span class="p">,</span> </span><span id="L-4"><a name="L-4"></a> <span class="ss">`read`</span> <span class="kt">INT</span> <span class="no">NULL</span><span class="p">,</span> </span><span id="L-5"><a name="L-5"></a> <span class="ss">`text`</span> <span class="kt">VARCHAR</span><span class="p">(</span><span class="mi">255</span><span class="p">)</span> <span class="k">NOT</span> <span class="no">NULL</span><span class="p">,</span> </span><span id="L-6"><a name="L-6"></a> <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">id</span><span class="p">)</span> </span><span id="L-7"><a name="L-7"></a><span class="p">);</span> </span><span id="L-8"><a name="L-8"></a> </span><span id="L-9"><a name="L-9"></a><span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">message</span> </span><span id="L-10"><a name="L-10"></a><span class="k">ADD</span> <span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="ss">`read`</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="nf">message</span><span class="p">(</span><span class="ss">`id`</span><span class="p">);</span> </span><span id="L-11"><a name="L-11"></a> </span><span id="L-12"><a name="L-12"></a> </span><span id="L-13"><a name="L-13"></a><span class="k">INSERT</span> <span class="k">INTO</span> <span class="nf">message</span><span class="p">(</span><span class="ss">`text`</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="s1">&#39;this is a new message&#39;</span><span class="p">);</span> </span><span id="L-14"><a name="L-14"></a> </span><span id="L-15"><a name="L-15"></a><span class="k">UPDATE</span> <span class="n">message</span> </span><span id="L-16"><a name="L-16"></a><span class="kt">SET</span> <span class="ss">`text`</span> <span class="o">=</span> <span class="s1">&#39;this message has been read&#39;</span><span class="p">,</span> </span><span id="L-17"><a name="L-17"></a> <span class="ss">`read`</span> <span class="o">=</span> <span class="ss">`id`</span><span class="p">;</span> </span><span id="L-18"><a name="L-18"></a> </span><span id="L-19"><a name="L-19"></a><span class="k">INSERT</span> <span class="k">INTO</span> <span class="nf">message</span><span class="p">(</span><span class="ss">`text`</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="s1">&#39;this is a new message&#39;</span><span class="p">);</span> </span><span id="L-20"><a name="L-20"></a><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">message</span><span class="p">;</span> </span><span id="L-21"><a name="L-21"></a> </span><span id="L-22"><a name="L-22"></a><span class="k">DELETE</span> <span class="k">FROM</span> <span class="n">message</span> <span class="k">WHERE</span> <span class="nf">IFNULL</span><span class="p">(</span><span class="ss">`read`</span><span class="p">,</span><span class="mi">0</span><span class="p">)</span> <span class="o">=</span> <span class="mi">0</span><span class="p">;</span> </span><span id="L-23"><a name="L-23"></a><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">message</span><span class="p">;</span> </span><span id="L-24"><a name="L-24"></a> </span><span id="L-25"><a name="L-25"></a><span class="o">--</span><span class="n">This</span> <span class="n">next</span> <span class="n">deletion</span> <span class="k">is</span> <span class="n">protected</span> <span class="n">due</span> <span class="ss">`read`</span> <span class="n">FK</span> <span class="n">reference</span> </span><span id="L-26"><a name="L-26"></a><span class="k">DELETE</span> <span class="k">FROM</span> <span class="n">message</span> <span class="k">WHERE</span> <span class="nf">IFNULL</span><span class="p">(</span><span class="ss">`read`</span><span class="p">,</span><span class="mi">0</span><span class="p">)</span> <span class="o">!=</span> <span class="mi">0</span><span class="p">;</span> </span><span id="L-27"><a name="L-27"></a> </span><span id="L-28"><a name="L-28"></a><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">message</span><span class="p">;</span> </span></pre></div> </td></tr></table>
"delete protect", "foreign key", mysql, trigger
Copyrighted, illegal, or inappropriate content? Email nick@snipt.net.