Re: RI and restoring dumps

On Wednesday 13 December 2000 17:04, Robert B. Easter wrote:
> Has anyone experienced referential integrity (RI) problems while restoring
> data from a dump?  Like, if the dump doesn't restore the data in the right
> order, then primary keys might not be in place before the foreign keys are
> restored.  I want to know this from people who have experience before I do
> use RI!
>
> Actually I did use RI in a database but had complications with it (I forget
> exactly what it was, something like the order of PL/SQL procedures and RI
> triggers firing caused some problems) but removed them all and just tried
> implementing all the checks I wanted in my PL/SQL triggers myself.

On Wed, Dec 13, 2000 at 11:38:18AM -0800, Stuart Statman wrote:
[ . . . ]
> I would suggest, instead, to create a table that represents your hierarchy
> without adding columns. For example :
> 
> create table Category (
> CategoryID       int4  not null  primary key,
> ParentCategoryID int4  not null  REFERENCES Category (CategoryID),
> CategoryName     varchar(100)
> );
> 
> Add a CategoryID with an FK reference to this table, and your work is done.
> 
> Then adding, inserting, removing, or moving layers in the hierarchy becomes
> quite simple. This also preserves hierarchical integrity, where subcategory
> a of subcategory b will also remain a subcategory of category c if
> subcategory b is a subcategory of subcategory c, where I'm not sure your
> model will preserve or guarantee that. (Does that sentence deserve a prize?)

Ok, I'm replying to my own post here, but I was reading another message
(above) about a FOREIGN KEY that REFERENCES the same table as it is in.  When
doing a restore of this table, I guess you would have to disable those RI
checks.  I know it can be done (right?), but how?  Or is this really
automatic and not to be worried about!? :)


--
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------