Thanks Igor. >>You seem to be under impression that a FOREIGN KEY >>constraint somehow magically pulls a value from >>referenced table.
correct. so, first I need to insert in t0 table, then query for value from t0, then update t1 with this value. Needs to avoid reading from t0 table. Is there any alternative way to insert? Thanks in advance. On Wed, Jan 11, 2012 at 6:41 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > Durga D <durga.d...@gmail.com> wrote: > > create table if not exists t0 ( h integer primary key autoincrement, > t0info > > text); > > create table if not exists t1 ( i integer primary key autoincrement, > t1info > > text, hh integer not null, unique(i, hh), foreign key (hh) references t0 > > (h) ); > > The UNIQUE constraint is pointless. Since i is a PRIMARY KEY, it's already > guaranteed to be unique by itself. > > > create table if not exists t2 (ii integer primary key, t2info text, > foreign > > key (ii) references t1 (i) ); > > > > I created three prepared statement for t0, t1 and t2. > > Updated the records (t0, t1 and t2 table) by using bind and step apis in > > trasaction. > > Binding t0info for t0, t1info for t1 table and t2info for t2 table only. > > > > But, sqlite3_step(stmtoft1); is failing always. What might be the wrong > > with this approach. > > When you insert a record into t1, what value are you providing for hh > column, if any? You can't just leave it off, as it's declared NOT NULL. It > needs to be assigned a value, and that value must match t0.h in some t0 row. > > You seem to be under impression that a FOREIGN KEY constraint somehow > magically pulls a value from referenced table. This is not the case. You > are still responsible for providing the value, the constraint only checks > that the value actually exists in the referenced table/column. > -- > 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
