title: MariaDB/MySql: Setting CURRENT_TIMESTAMP on CREATE and changing noting on UPDATE
body: <p>I have not used MySQL in a few years and when I created a new table it did something I was not expecting. I am using <strong>MariaDB v5.5.60-MariaDB</strong></p>
  
  <p>I need to create a table that has both a <code>created</code> column and an <code>updated</code> column.</p>
  
  <p>I need the <code>created</code> column to only be set to CURRENT_TIMESTAMP when the row is created and then never change unless I change it explicitly.</p>
  
  <p>I need the <code>updated</code> column to be set to CURRENT_TIMESTAMP both when the row is created and when the row is changed.</p>
  
  <p>If I do the following:</p>
  
  <pre><code>CREATE TABLE user_prefs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    user VARCHAR(255) NOT NULL,
    provider VARCHAR(255) NOT NULL,
    pref VARCHAR(128) NOT NULL,
    jsondata LONGTEXT,
    created timestamp NOT NULL,
    modified timestamp NOT NULL,
    PRIMARY KEY (id),
    UNIQUE INDEX id_UNIQUE (id ASC));
  </code></pre>
  
  <p>Then the <code>created</code> column is set to:<br>
  <strong>DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP</strong></p>
  
  <p>and the <code>modified</code> column is set to:<br>
  <strong>DEFAULT '0000-00-00 00:00:00'</strong></p>
  
  <p>If I try this:</p>
  
  <pre><code>CREATE TABLE user_prefs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    user VARCHAR(255) NOT NULL,
    provider VARCHAR(255) NOT NULL,
    pref VARCHAR(128) NOT NULL,
    jsondata LONGTEXT,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE INDEX id_UNIQUE (id ASC));
  </code></pre>
  
  <p>Then I get the error **Error Code: 1293. Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
  **</p>
  
  <p>So is there a way to automate setting both <code>created</code> and <code>modified</code> on creation of a row and then to change <code>modified</code> every time the row is change?</p>
  
  <p>Thanks in advance.</p>