SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
PRAGMA foreign_keys = ON;
✓
CREATE TABLE words(
  id INTEGER PRIMARY KEY,
  value TEXT
);
✓
CREATE UNIQUE INDEX unique_words_value ON words(value);
✓
CREATE TABLE data(
  id INTEGER PRIMARY KEY,
  word_id INTEGER NOT NULL,
  FOREIGN KEY (word_id) REFERENCES words(id)
);
✓
CREATE VIEW data_view AS
SELECT d.id, w.value FROM data AS d INNER JOIN words AS w on w.id = d.word_id;
✓
CREATE TRIGGER data_view_insert INSTEAD OF INSERT ON data_view
BEGIN
  INSERT OR IGNORE INTO words(value) VALUES (NEW.value);
  INSERT OR IGNORE INTO data(word_id) VALUES(
    (SELECT id FROM words WHERE value = NEW.value)
  );
END;
✓
CREATE TRIGGER data_view_delete INSTEAD OF DELETE ON data_view
BEGIN
  DELETE FROM data
    WHERE id = OLD.id;
  DELETE FROM words
    WHERE value = OLD.value AND NOT EXISTS(SELECT 1 FROM data_view WHERE value = OLD.value);
END;
✓
INSERT INTO data_view (value) VALUES
  ('random1'),
  ('random2'),
  ('random3'),
  ('random1'),
  ('random3'),
  ('random4');
✓
SELECT * FROM words;


id
value


1
random1

2
random2

3
random3

4
random4


SELECT * FROM data_view;


id
value


1
random1

2
random2

3
random3

4
random1

5
random3

6
random4


DELETE FROM data_view WHERE id IN (2, 4);
✓
SELECT * FROM words;


id
value


1
random1

3
random3

4
random4


SELECT * FROM data_view;


id
value


1
random1

3
random3

5
random3

6
random4