Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL: uniqueness, automatic created_at, updated_at refresh + soft delete. SQLite, PostgreSQL, MySQL
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
-- 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', 'jho@example.com'),
('bob', 'bob@example.com'),
('alice', 'alice@example.com');
-- 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 = 'jho@example.com'
AND not_deleted = true;
-- cehck
SELECT * FROM somethings WHERE not_deleted = true;
-- INSERT jho AGAIN (SHOULD PASS)
--
INSERT INTO somethings (name, email) VALUES
('jho', 'jho@example.com');
-- cehck
SELECT * FROM somethings WHERE not_deleted = true;
-- TOUCH (jho for example)
--
UPDATE somethings SET updated_at = current_timestamp
WHERE email = 'jho@example.com'
AND not_deleted = true;
-- cehck
SELECT * FROM somethings WHERE not_deleted = true;
-- EMAIL UNIQUINESS (SHOULD FAIL)
--
INSERT INTO somethings (name, email) VALUES
('jho', 'jho@example.com');
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
-- 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', 'jho@example.com'),
('bob', 'bob@example.com'),
('alice', 'alice@example.com');
-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;
-- EMAIL UNIQUINESS (SHOULD FAIL)
--
INSERT INTO somethings (name, email) VALUES
('jho', 'jho@example.com');
--
-- SOFT DELETE (jho for example)
UPDATE somethings SET deleted_at = current_timestamp
WHERE email = 'jho@example.com'
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', 'jho@example.com');
-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;
--
-- TOUCH (jho for example)
UPDATE somethings SET updated_at = current_timestamp
WHERE email = 'jho@example.com'
AND deleted_at IS NULL;
-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
-- 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', 'jho@example.com'),
('bob', 'bob@example.com'),
('alice', 'alice@example.com');
-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;
-- EMAIL UNIQUINESS (SHOULD FAIL)
--
INSERT INTO somethings (name, email) VALUES
('jho', 'jho@example.com');
--
-- SOFT DELETE (jho for example)
UPDATE somethings SET deleted_at = current_timestamp
WHERE email = 'jho@example.com'
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', 'jho@example.com');
-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;
--
-- TOUCH (jho for example)
UPDATE somethings SET updated_at = current_timestamp
WHERE email = 'jho@example.com'
AND deleted_at IS NULL;
-- cehck
SELECT * FROM somethings WHERE deleted_at IS NULL;
@logrusorgru

This comment has been minimized.

Copy link
Owner Author

@logrusorgru logrusorgru commented Mar 17, 2016

No performance tests were made.

@logrusorgru

This comment has been minimized.

Copy link
Owner Author

@logrusorgru logrusorgru commented Mar 17, 2016

Tested on:

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

This comment has been minimized.

Copy link

@jiechic jiechic commented Mar 15, 2017

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

@kaschbacher

This comment has been minimized.

Copy link

@kaschbacher kaschbacher commented Jun 30, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment