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/ ------------