Hi, Igor, On Sun, Dec 30, 2012 at 6:43 AM, Igor Tandetnik <i...@tandetnik.org> wrote: > Igor Korot <ikoro...@gmail.com> wrote: >> I have: >> >> CREATE TABLE test( id integere primary key, name varchar(70)); >> 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? > > Preventing insertion (with a trigger, or by other means) is easy. The hard > part is figuring out that 'def abc' is in fact a "duplicate" of 'abc def'; I > assume that you want something general like "two strings consist of the same > set of words, regardless of order". I can't think of any way to express such > a condition in pure SQL and built-in functions - you would need a custom > function or a custom collation:
Exactly. See my reply to Simon. Trying to prevent "Igor Tandetnik" and "Tandetnik Igor" to appear in the table. > > http://sqlite.org/c3ref/create_function.html > http://sqlite.org/c3ref/create_collation.html > > If you go the collation route, then you wouldn't need a trigger - just create > a unique index with this collation: > > create unique index MyIndex on test(name collate MyCustomCollation); > > > If the actual condition you want to enforce is simpler than the one I > assumed, then there might be ways that don't require custom coding. For > example, if the values in test.name are always presumed to consist of two > words separated by a space, then you can do something like this: > > create trigger EnforceUnique before insert on test > begin > select raise(fail) from test > where name in (new.name, > substr(new.name, instr(new.name, ' ') + 1) || ' ' || substr(new.name, 1, > instr(new.name, ' ') - 1)); > end; So what would be the easiest way to solve it? Thank you. > > Though in this case, I'd personally have two separate columns instead, say > firstName and lastName. > -- > Igor Tandetnik > > _______________________________________________ > 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
