By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

Import and self referencing foreign key

P: n/a
We have a table (document_folders), which simulates a directory
hierarchy, so in this table, each folder has an attribute
"TopFolderID" to indicate which folder its contained in (for the root
folder that attribute is NULL). To enforce correct TopFolderID's, we
have a foreign key from document_folders.TopFolder to
document_folders.ID.

Now the problem is: if we import into this table, and the rows are not
in the order of the hierarchy, rows get rejected because of above
foreign key. Is there a way to prevent this, except for just exporting
in the right order (which IMO would need us to write a recursive
query)?

Regards,
Janick

Feb 4 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Janick Bernet wrote:
We have a table (document_folders), which simulates a directory
hierarchy, so in this table, each folder has an attribute
"TopFolderID" to indicate which folder its contained in (for the root
folder that attribute is NULL). To enforce correct TopFolderID's, we
have a foreign key from document_folders.TopFolder to
document_folders.ID.

Now the problem is: if we import into this table, and the rows are not
in the order of the hierarchy, rows get rejected because of above
foreign key. Is there a way to prevent this, except for just exporting
in the right order (which IMO would need us to write a recursive
query)?
Can you disable the RI constraint via ALTER TABLE ... NOT ENFORCED
Then after IMPORT: ALTER TABLE .. ENFORCED
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 4 '07 #2

P: n/a
On 4 Feb., 14:14, Serge Rielau <srie...@ca.ibm.comwrote:
Can you disable the RI constraint via ALTER TABLE ... NOT ENFORCED
Then after IMPORT: ALTER TABLE .. ENFORCED
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Thanks Serge, I'll try this. But if I do an ALTER TABLE ... NOT
ENFORCED, that will only affect my current transaction!?
Because otherwise, it could be problematic if for a certain timeframe
the RI wouldn't be enforced.

Regards,
Janick

Feb 5 '07 #3

P: n/a
Janick Bernet wrote:
On 4 Feb., 14:14, Serge Rielau <srie...@ca.ibm.comwrote:
>Can you disable the RI constraint via ALTER TABLE ... NOT ENFORCED
Then after IMPORT: ALTER TABLE .. ENFORCED

But if I do an ALTER TABLE ... NOT
ENFORCED, that will only affect my current transaction!?
Because otherwise, it could be problematic if for a certain timeframe
the RI wouldn't be enforced.
Why should it be restricted to your transaction only? You are changing the
semantics of the RI constraint, i.e. you tell DB2 specifically not to
enforce it. That's comparable to dropping the constraint. If you want to
have the constraint enforced again, you have to say so with another ALTER
TABLE ... ENFORCED. DB2 will then make sure that the constraint is
satisfied. If not, you will get an error message like this:

SQL0544N The check constraint "X" cannot be added because the table
contains a row that violates the constraint. SQLSTATE=23512

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 5 '07 #4

P: n/a
Janick Bernet wrote:
On 4 Feb., 14:14, Serge Rielau <srie...@ca.ibm.comwrote:
>Can you disable the RI constraint via ALTER TABLE ... NOT ENFORCED
Then after IMPORT: ALTER TABLE .. ENFORCED
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks Serge, I'll try this. But if I do an ALTER TABLE ... NOT
ENFORCED, that will only affect my current transaction!?
Because otherwise, it could be problematic if for a certain timeframe
the RI wouldn't be enforced.
Sorry, big switch.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 5 '07 #5

P: n/a
>Now the problem is: if we import into this table, and the rows are not in the order of the hierarchy, rows get rejected because of above foreign key.<<

Get a copy of TREES & HIERARCHIES IN SQL and look up the nested sets
model for hierarchies. It will work much better for you and it does
not need recursive procedural code.

The fact that ordering matters in your mdoel should tell you that it
is not really a proper table at all. It is a sequential file
structure wearing SQL clothes!


Feb 5 '07 #6

P: n/a
Thanks for all the input.

@Knut:
Why should it be restricted to your transaction only?
I did some testing: If I do the "ALTER TABLE ... NOT ENFORCED" in one
connection and don't commit or rollback the transaction, and I want do
do an insert in another transaction to the same table that transaction
blocks, which is IMO the correct behaviour and just what I need in
this situation.

@Serge:
Sorry, big switch.
?

@Joe Celko:
Get a copy of TREES & HIERARCHIES IN SQL and look up the nested sets model for hierarchies.
It will work much better for you and it does not need recursive procedural code.
I read your article about the alternative representation of trees/
hierarchies in SQL (I just talked my boss into buying a book on SQL PL
so i cannot afford your's right now ;)) and your solution looks
interesting. But if I understood the idea correctly, to insert a new
column one would need to update each other row's boundaries, which is
to much of an overhead, as inserts aren't that rare.

As the "NOT ENFROCED-hack" proposed by Serge seems to work, I think
well stick to that right now.

Regards,
Janick

Feb 6 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.