SQL: uniqueness, automatic created_at, updated_at refresh + soft delete. SQLite, PostgreSQL, MySQL

mysql.sql

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
--                                                                            --
--                                  mysql                                     --
--                                                                            --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--
-- mysql <http://sqlfiddle.com/#!9/91afb5/2>
--        note: sqlfiddle is very stupid

-- SCHEMA

-- table
CREATE TABLE somethings (
  id integer  AUTO_INCREMENT  NOT NULL
  --
  ,name   text
  ,email  varchar(255)  NOT NULL
  --
  ,created_at timestamp  NOT NULL  DEFAULT current_timestamp
  ,updated_at timestamp  NOT NULL  DEFAULT current_timestamp
                                   ON UPDATE current_timestamp -- trigger
  -- soft delete
  ,not_deleted  boolean DEFAULT true
  ,deleted_at   timestamp
  --
  ,PRIMARY KEY (id)
);

-- unique index (email, not_deleted)
CREATE UNIQUE INDEX idx_somethings_email_not_deleted
  ON somethings (email ASC, not_deleted ASC);

-- does it really needed ? ? ?
-- It's a big question, because the column type is boolean
--   and possible values are true and NULL.
-- I dont know.
-- index (not_deleted)
CREATE UNIQUE INDEX idx_somethings_not_deleted
  ON somethings (not_deleted ASC);

--

-- STUFF

--


-- SELECT (lookup)
--
SELECT * FROM somethings WHERE not_deleted = true;

-- CREATE RECORDS
--
INSERT INTO somethings (name, email) VALUES
    ('jho', '[email protected]'),
    ('bob', '[email protected]'),
    ('alice', '[email protected]');

-- cehck
SELECT * FROM somethings WHERE not_deleted = true;

-- SOFT DELETE (jho for example)
--
UPDATE somethings SET deleted_at = current_timestamp, not_deleted = NULL
    WHERE email = '[email protected]'
    AND not_deleted = true;

-- cehck
SELECT * FROM somethings WHERE not_deleted = true;

-- INSERT jho AGAIN (SHOULD PASS)
--
INSERT INTO somethings (name, email) VALUES
    ('jho', '[email protected]');

-- cehck
SELECT * FROM somethings WHERE not_deleted = true;

-- TOUCH (jho for example)
--
UPDATE somethings SET updated_at = current_timestamp
    WHERE email = '[email protected]'
    AND not_deleted = true;

-- cehck
SELECT * FROM somethings WHERE not_deleted = true;

-- EMAIL UNIQUINESS (SHOULD FAIL)
--
INSERT INTO somethings (name, email) VALUES
    ('jho', '[email protected]');

postgresql.sql

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
--                                                                            --
--                               postgresql                                   --
--                                                                            --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--
-- postgresql <http://sqlfiddle.com/#!15/1b30f/3>
--

-- SCHEMA

-- function
-- it's from stackovrflow (the link was lost; google can help)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- table
CREATE TABLE somethings (
  id SERIAL  NOT NULL
  --
  ,name   text
  ,email  varchar(255)  NOT NULL
  --
  ,created_at timestamp with time zone  NOT NULL  DEFAULT current_timestamp
  ,updated_at timestamp with time zone  NOT NULL  DEFAULT current_timestamp
  -- soft delete
  ,deleted_at timestamp with time zone
  ,PRIMARY KEY (id)
);

-- index (deleted_at)
CREATE INDEX idx_somethings_deleted_at
  ON somethings (deleted_at ASC);

-- index (email)
CREATE UNIQUE INDEX idx_somethings_email
  ON somethings (email ASC)
  WHERE deleted_at IS NULL;

-- trigger (updated_at)
CREATE TRIGGER tg_somethings_updated_at
    BEFORE UPDATE
    ON somethings
    FOR EACH ROW
    EXECUTE PROCEDURE update_updated_at_column();


--

-- STUFF

--


-- SELECT (lookup)
--
SELECT * FROM somethings WHERE deleted_at IS NULL;

-- CREATE RECORDS
--
INSERT INTO somethings (name, email) VALUES
    ('jho', '[email protected]'),
    ('bob', '[email protected]'),
    ('alice', '[email protected]');

-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;

-- EMAIL UNIQUINESS (SHOULD FAIL)
--
INSERT INTO somethings (name, email) VALUES
    ('jho', '[email protected]');

--
-- SOFT DELETE (jho for example)
UPDATE somethings SET deleted_at = current_timestamp
    WHERE email = '[email protected]'
    AND deleted_at IS NULL;

-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;

-- INSERT jho AGAIN (SHOULD PASS)
--
INSERT INTO somethings (name, email) VALUES
    ('jho', '[email protected]');

-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;

--
-- TOUCH (jho for example)
UPDATE somethings SET updated_at = current_timestamp
    WHERE email = '[email protected]'
    AND deleted_at IS NULL;

-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;

sqlite3.sql

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
--                                                                            --
--                               sqlite3                                      --
--                                                                            --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--
-- sqlite3 <http://ideone.com/0K9KjE and mirror http://goo.gl/NMLryD>
--

-- SCHEMA

-- table
CREATE TABLE somethings (
  id integer PRIMARY KEY AUTOINCREMENT  NOT NULL
  --
  ,name   text
  ,email  varchar(255)  NOT NULL
  --
  ,created_at datetime  NOT NULL  DEFAULT current_timestamp
  ,updated_at datetime  NOT NULL  DEFAULT current_timestamp
  -- soft delete
  ,deleted_at datetime
);

-- index (deleted_at)
CREATE INDEX idx_somethings_deleted_at
    ON somethings (deleted_at ASC);

-- index (email)
CREATE UNIQUE INDEX idx_somethings_email
  ON somethings (email ASC)
  WHERE deleted_at IS NULL;

-- trigger (updated_at)
CREATE TRIGGER tg_somethings_updated_at
AFTER UPDATE
ON somethings FOR EACH ROW
BEGIN
  UPDATE somethings SET updated_at = current_timestamp
    WHERE id = old.id;
END;


--

-- STUFF

--


-- SELECT (lookup)
--
SELECT * FROM somethings WHERE deleted_at IS NULL;

-- CREATE RECORDS
--
INSERT INTO somethings (name, email) VALUES
    ('jho', '[email protected]'),
    ('bob', '[email protected]'),
    ('alice', '[email protected]');

-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;

-- EMAIL UNIQUINESS (SHOULD FAIL)
--
INSERT INTO somethings (name, email) VALUES
    ('jho', '[email protected]');

--
-- SOFT DELETE (jho for example)
UPDATE somethings SET deleted_at = current_timestamp
    WHERE email = '[email protected]'
    AND deleted_at IS NULL;

-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;

-- INSERT jho AGAIN (SHOULD PASS)
--
INSERT INTO somethings (name, email) VALUES
    ('jho', '[email protected]');

-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;

--
-- TOUCH (jho for example)
UPDATE somethings SET updated_at = current_timestamp
    WHERE email = '[email protected]'
    AND deleted_at IS NULL;

-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;

No performance tests were made.

Tested on:

• PostgreSQL 9.4.6
• SQLite 3.8.11.1
• MySQL Ver 14.14 Distrib 5.6.28

sqlite when use update somethings
will print error：
too many levels of trigger recursion

Isn't created_at the same as endpoint_ts? Is there a difference?