Simon, On Sun, Dec 30, 2012 at 2:08 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 30 Dec 2012, at 9:52am, Igor Korot <ikoro...@gmail.com> wrote: > >> I want to create a trigger on the table. The trigger will be fired >> before insertion. >> In that trigger I want to check if the data is already present. >> >> I have: >> >> CREATE TABLE test( id integere primary key, name varchar(70)); > > By the way, SQLite has no varchar, just TEXT. > >> INSERT INTO test( NULL, "abc def" ); >> INSERT INTO test( NULL, "def abc" ); >> >> Can I prevent the second insertion with the trigger? >> Or I should make some app logic to prevent that? > > I would not use a trigger. SQLite has the ability to prevent this all by > itself. You can do it two ways: one by putting it in the TABLE definition: > > CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT UNIQUE ON CONFLICT FAIL)
Well table is already created and I don't think I can change the definition easily. So this probably won't work. > > You might prefer ON CONFLICT IGNORE which would not generate an error > message, if you felt it was good enough that the data was already in the > table. > > The second way is by creating a special index: > > CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT); > CREATE UNIQUE INDEX testNameUnique ON test (name) > > There is no real difference in time or efficiency in which one you choose. > It's about whether you think of duplication as a fault in the TABLE or > something which might one day not be a problem. This would've worked if the string will be the same, i.e. "abc def" and "abc def". However they are not. One is "abc def", another "def abc". So from the uniqueness point of view they are different. Basically I'm trying to prevent the insertion of: "Simon Slavin" and "Slavin Simon" into the table which is not make much sense as it's one person. > > By the way, had you considered this: > > INSERT INTO test( NULL, "abc def" ); > INSERT INTO test( NULL, "abc DEF" ); > > by default SQLite uses NOCASE, in other words, it will treat these two > strings as the same, and the second INSERT would not be allowed if you used > one of the above. Any other idea? Thank you. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
