clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 497144 fiddles created (9372 in the last week).

create table Texts ( tid integer not null primary key AUTOINCREMENT , textval varchar(20) not null unique);
 hidden batch(es)


create index x1 on texts (textval);
 hidden batch(es)


create table T ( x int not null , tid int not null references texts (tid) , primary key (x, tid) );
 hidden batch(es)


create view v as select t.x, texts.textval from t join texts on t.tid = texts.tid ;
 hidden batch(es)


CREATE TRIGGER trig1 INSTEAD OF INSERT ON V BEGIN -- insert unless textval is already in place INSERT INTO texts (textval) VALUES (NEW.textval) -- ON CONFLICT (textval) DO NOTHING ; -- lookup tid for textval --insert into t (x, tid) --select NEW.x, texts.tid --from texts where texts.textval = NEW.textval; END;
 hidden batch(es)


INSERT INTO texts (textval) VALUES ('a') -- ON CONFLICT (textval) DO NOTHING ;
 hidden batch(es)


select * from Texts;
tid textval
1 a
 hidden batch(es)


CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
 hidden batch(es)


INSERT INTO vocabulary(word) VALUES('jovial') ON CONFLICT(word) DO UPDATE SET count=count+1;
near "ON": syntax error
 hidden batch(es)