help!about constraint

Hi: Now I am working on Informix Online 7.2 and using ESQL to program. I create 2 tables:table1,table2. Some information of every person are stored in table1,other information of same people stored in table2. table1: name type index null --------------------------------- id serial UNIQUE no dn varchar UNIQUE no ...... table2: name type index null --------------------------------- id integer UNIQUE no dn varchar UNIQUE no ...... I set table1.id as primary key,table2.id as foreign key. I want to establish constraint between them because they store the information of the same people. What I do is as below: a. insert a record to table1 $insert into table1 values(0,dnval1,...); b. get the record's id $select id from table1 where dn=dnval1 c. insert to table2 $insert into table2 values(id,dnval1,...); step a,b are successful,but step 3 shows:SQLCODE -691, ISAM -111,SQLSTATE 50. This errors are about key,constraint. Though I read the error message which found by "finderr", I don't know how to correct it. Any help would be appreciated! Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Several problems and suggestions. Read on: carina@371.net wrote: > > Hi: > Now I am working on Informix Online 7.2 and using ESQL to program. > I create 2 tables:table1,table2. Some information of every person are > stored in table1,other information of same people stored in table2. > > table1: > name type index null > --------------------------------- > id serial UNIQUE no > dn varchar UNIQUE no > ...... > > table2: > name type index null > --------------------------------- > id integer UNIQUE no > dn varchar UNIQUE no > ...... > > I set table1.id as primary key,table2.id as foreign key. So far so good. > I want to establish constraint between them because they store the > information > of the same people. > What I do is as below: > a. insert a record to table1 > $insert into table1 values(0,dnval1,...); OK > b. get the record's id > $select id from table1 where dn=dnval1 You do not need to do this. Immediately after the insert to table1, the structure element sqlca.sqlerrd[1] contains the serial number assigned to the row just inserted. You can use that in the insert to table2 below. > c. insert to table2 > $insert into table2 values(id,dnval1,...); > > step a,b are successful,but step 3 shows:SQLCODE -691, ISAM > -111,SQLSTATE 50. > This errors are about key,constraint. Though I read the error message > which > found by "finderr", I don't know how to correct it. OK so here is the problem, you have a database with logging and you have either issued a BEGIN WORK or it is an ANSI mode database. The row you added to table1 in the first insert statement has not been committed yet so the insert of the row to table 2 fails the contraint check which by default is accomplished immediately upon insert. You can delay the contraint check until commit time by issuing the following: SET CONSTRAINTS ALL DEFERRED; -or- SET CONSTRAINTS name_of_foreign_key_constraint DEFERRED; Art S. Kagel

Sorry Art, I think SET CONSTRAINT DEFERRED is not a solution here. If a row has been inserted in table1 (and I can obviously select some columns of it), I am able to insert into a dependent table as well (whether being in transaction or not) (I've tested this to be sure; otherwise I wouldn't dare to correct Art;) As stated above I tried the following: create table t1 (id serial not null, dn varchar(20) not null); alter table t1 add constraint primary key (id); create unique index t1x on t1 (dn); create table t2 (id serial not null, dn varchar(20) not null); alter table t2 add constraint primary key (id); alter table t2 add constraint foreign key (id) references t1; create unique index t2x on t2 (dn); begin work; insert into t1 values (0, 'Gumpo'); select id from t1 where dn = 'Gumpo'; -- return 1 (as expected) insert into t2 values (1, 'Gumpo again');commit; select * from t1; select * from t2; id dn 1 Gumpo id dn 1 Gumpo again So the question is: what else to look? Carina: * Are there any other foreign keys on table2 (you should find the name of the 'bad' constraint in the error-message? * Be sure to retrieve the correct id from table1 (verify the id's variable) after the select and/or use Art's suggestion using sqlca.sqlerrd[1]. If this doesn't help, mail me (us) your real source-code and your real dbschema; maybe we'll find out more. Peter Art S. Kagel wrote: > > Several problems and suggestions. Read on: > > carina@371.net wrote: > > > > Hi: > > Now I am working on Informix Online 7.2 and using ESQL to program. > > I create 2 tables:table1,table2. Some information of every person are > > stored in table1,other information of same people stored in table2. > > > > table1: > > name type index null > > --------------------------------- > > id serial UNIQUE no > > dn varchar UNIQUE no > > ...... > > > > table2: > > name type index null > > --------------------------------- > > id integer UNIQUE no > > dn varchar UNIQUE no > > ...... > > > > I set table1.id as primary key,table2.id as foreign key. > > So far so good. > > > I want to establish constraint between them because they store the > > information > > of the same people. > > What I do is as below: > > a. insert a record to table1 > > $insert into table1 values(0,dnval1,...); > OK > > > b. get the record's id > > $select id from table1 where dn=dnval1 > > You do not need to do this. Immediately after the insert to table1, > the structure element sqlca.sqlerrd[1] contains the serial number > assigned to the row just inserted. You can use that in the insert to > table2 below. > > > c. insert to table2 > > $insert into table2 values(id,dnval1,...); > > > > step a,b are successful,but step 3 shows:SQLCODE -691, ISAM > > -111,SQLSTATE 50. > > This errors are about key,constraint. Though I read the error message > > which > > found by "finderr", I don't know how to correct it. > > OK so here is the problem, you have a database with logging and you > have either issued a BEGIN WORK or it is an ANSI mode database. The > row you added to table1 in the first insert statement has not been > committed yet so the insert of the row to table 2 fails the contraint > check which by default is accomplished immediately upon insert. You > can delay the contraint check until commit time by issuing the > following: > > SET CONSTRAINTS ALL DEFERRED; > > -or- > > SET CONSTRAINTS name_of_foreign_key_constraint DEFERRED; > > Art S. Kagel -- _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ _/_/ Mag. Peter Kolmhofer __ _ _____ _/ _/_/ Informix & DB2 DBA __ --/_|___\\______ _/ _/_/ Porsche Informatik (Austria) _ _ ( _ _ \\) _/ _/_/ A-5101 Bergheim, Handelszentrum 7 -(_)-------(_)- _/ _/_/ +43 662 4670-6258 fax: -6501 email:kop@porsche.co.at _/ _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

Art,Peter Thank you very much.I have inserted records successfully. I add "begin work" and "commit work" at the beginning and ending of the sql statements and all things go right! Thanks! Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.