On 4 Jan 2013, at 12:08pm, Krzysztof <dib...@wp.pl> wrote: > I want split > repeated columns to dictionary tables like: > > table customers > - name > - surname > - id_country // FK to table countries > > I'm looking for one command which before inserting to "customers" will look > to table countries and check if country exists, if not then insert and > return id, else return existing id.
I think you do understand the problem correctly. Foreign keys have two sets of actions: ON UPDATE ON DELETE What you want would be dealt with correctly if there was an 'ON CREATE' action which made a new related row when necessary. However there is no such type of action (it would be difficult to get right anyway) so you have to write that logic into your own code. If just occurred to me that it might be possible to do what you want using an AFTER TRIGGER. I'm not sure exactly how to do it since it would have to do the INSERT OR IGNORE, then modify the newly created record with the right id_country. However, doing this in a TRIGGER would only be the same amount of work as doing it in your own application logic and I think it would take even more work than just writing your own two commands. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
