Re: TRUNCATE TABLE

Ben >>say another table has foreign key constraints (not necessarily anyrows) against this table, >>that then? Do you have to manually recreate these tables as well? Lets say you want to truncate table A, which contains a PK which is referenced by table B. If table B does not contain any rows, there is no necessity to recreate the table. However, if table B does contain rows, when you reload table A and re-enable the constraints you need to ensure that table A contains the keys that table B points to. Otherwise you will get an error when you reeable the constraints. >>If this table you are dropping/recreating is referenced by these constraints, wont the >>reference be broken and cause these constraints to become invalid or dropped >>themselves? I havent tried this, but I dont think this will happen if you have named constraints. If you have autonamed (Informix generated) constraints, then of course the results are indeterninate and chances are that what you say will happen. My guess is that what you are saying that Oracle's (and probably Informix 9.2's) truncate is able to determine that there is data in other tables which is dependent on the data in the table being truncated and will stop you from doing the truncate. This would seem to be fairly easy to enforce. With a drop, this check will not happen. It would be up to you to keep the constraints valid. Thank you for pointing this out. Sujit Ben Draper <bdraper@mathworks.com> on 04/27/2000 09:53:28 AM Please respond to Ben Draper <bdraper@mathworks.com> To: informix-list@iiug.org cc: Subject: Re: TRUNCATE TABLE If this table you are dropping/recreating is referenced by these constraints, wont the reference be broken and cause these constraints to become invalid or dropped themselves? Sujit.Pal@bankofamerica.com wrote in message <8e9r3o$mmh$1@news.xmission.com>... > > > >Ben > >You dont have to manually recreate these tables, just disable the constraints. >Anyway, Harry tells me that TRUNCATE is supported on 9.2 - its in the FM. > >HTH >Sujit > > > > > >Ben Draper <bdraper@mathworks.com> on 04/26/2000 12:58:02 PM > >Please respond to Ben Draper <bdraper@mathworks.com> > >To: informix-list@iiug.org >cc: >Subject: Re: TRUNCATE TABLE > > > >And say another table has foreign key constraints (not necessarily any rows) >against this table, what then? >Do you have to manually recreate these tables as well? > > >Sujit.Pal@bankofamerica.com wrote in message ><8e7h3l$f8q$1@news.xmission.com>... >> >> >> >>Harry >> >>TRUNCATE is an Oracle-ism, it does not exist in Informix. You can do the >same >>thing by: >> >>dbschema -d <yourdb> -t <yourtable> -ss cr_<yourtable>.sql>> >>echo "drop table <yourtable>;" | dbaccess <yourdb> >>dbaccess <yourdb> cr_<yourtable>.sql >> >>where <yourdb> = hib >>and <yourtable> = ipporttnp >> >>HTH >>Sujit >> >> >> >> >> >> >>Harry Sheng <hsheng@crosskeys.com> on 04/27/2000 08:54:42 AM >> >>Please respond to Harry Sheng <hsheng@crosskeys.com> >> >>To: informix-list@iiug.org >>cc: >>Subject: TRUNCATE TABLE >> >> >> >>Hello, there >> >>I use IDS9.2. I tried to delete from a table using "TRUNCATE". >>The table is a fragmented table and is there, and I have checked >>all constraints on executing "truncate table". The statement is >>very simple, but I do not know why I can not successfully execute >>it. Any idea is appreciated. >> >>Harry >> >>------ >>> echo "truncate table ipportnp" | dbaccess hib >> >>Database selected. >> >> >> 201: A syntax error has occurred. >>Error in line 1>>Near character position 1 >> >> >>Database closed. >> >>> >> >> >> >> >> >> >> >> >> > > > > > > > >