Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 03 Apr 2002 @ 07:23:46 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Alter table and RI
From:   Narayan Murthy K S


I consider the ghost table to be a bug, though it makes sense to create a ghost table in the presence of data - i find no reasons apt to justify the creation of such a table when there is no data in the original table. ie. if i created two new tables and then altered one of them to include a foreign key reference you will see a ghost table created with a suffix _0 created - which makes no sense what so ever.

This is what the WBT on SQL has to say on the Ghost table creation :

"Altering a table to add foreign key constraints will appear to be a successful operation even when unreferenced values are found. The rows containing the unreferenced values will be left undisturbed in the child table. Copies of these rows will be written to an error table which is assigned the same name as the child table with a numeric value appended (i.e. department_0), but it is important to note that the ALTER operation will appear successful. It is therefore important to check for the existence of the error table after executing the ALTER TABLE command. It's existence usually implies that there were inconsistencies found. (Its existence may also be leftover from a previous attempt, in which case an error will be returned - the error table will not be over-written). There are several ways to fix these inconsistencies"

So it clearly specifies that it is due to inconsistencies that the ghost table appears (which makes sense) but makes no sense when the same appears when there is no data. More over considering the number of foreign key references possible - the creation of these ghost tables would surely consume a lot of space and to add to it one has to manual remove them, because there is no automatic clean up done, just as they were created.

So to keep things as they were and to avoid all the mess of tables getting created and consumption of space it is better to avoid these references.

Narayan Murthy .

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020