Home » SQL & PL/SQL » SQL & PL/SQL » List schema tables in dependency order
List schema tables in dependency order [message #234691] Wed, 02 May 2007 21:59 Go to next message
lykmyk
Messages: 9
Registered: March 2007
Junior Member
I would like to know how i can list all the tables in a schema in order of dependency.

The objective is to avoid foreign key violations.
Re: List schema tables in dependency order [message #234718 is a reply to message #234691] Thu, 03 May 2007 00:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What did you try so far? What do you think would be the logic?
Re: List schema tables in dependency order [message #234719 is a reply to message #234691] Thu, 03 May 2007 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this really a list?
Is this not a graph or a net?
Don't you have tables that reference themselves or that reference tables that reference them?

Regards
Michel
Re: List schema tables in dependency order [message #234922 is a reply to message #234691] Thu, 03 May 2007 08:26 Go to previous messageGo to next message
lykmyk
Messages: 9
Registered: March 2007
Junior Member
I have a schema with about 300 tables. Some of these tables have foreign key relationships with other tables.

I have an application that inserts records into each an every table. Therefore the application must insert data into each table in the right order to avoid foreign key violations.

Thus i need to be able determine the correct order in which to insert into each table.

Thanks
Re: List schema tables in dependency order [message #234925 is a reply to message #234922] Thu, 03 May 2007 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use deferrable constraints, so constraint checks are deferred until commit.

Regards
Michel
Re: List schema tables in dependency order [message #234955 is a reply to message #234925] Thu, 03 May 2007 09:37 Go to previous messageGo to next message
lykmyk
Messages: 9
Registered: March 2007
Junior Member
For business reasons, that is not an option at this point. Only to insert into tables in there proper order.

Thanks
Re: List schema tables in dependency order [message #234976 is a reply to message #234955] Thu, 03 May 2007 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Business reasons?
Which business reasons can lead to forbid deferable constraints?

And as I previously said, what if your FK/PK links have loops?

Regards
Michel
Re: List schema tables in dependency order [message #234993 is a reply to message #234976] Thu, 03 May 2007 12:11 Go to previous messageGo to next message
lykmyk
Messages: 9
Registered: March 2007
Junior Member
Thanks Michel. I think i get your point. You think that there is no bullet proof way of avoiding foreign key violations by using an ordered list of tables.

I know this. Not my boss! Maybe i need a good example to explain why this approach is fraught with peril.

Thanks
Re: List schema tables in dependency order [message #234995 is a reply to message #234993] Thu, 03 May 2007 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I still don't see why you can't use deferrable constraints and then avoid so much useless work.

Regards
Michel
Re: List schema tables in dependency order [message #235439 is a reply to message #234995] Sat, 05 May 2007 16:36 Go to previous messageGo to next message
lykmyk
Messages: 9
Registered: March 2007
Junior Member
This is what happens when you have a lead that is not technically sound. They can't understand that writing a piece of program is not always the answer to every business process.

The data model here has so many inter-related dependecies that figuring it programtically would only be a 'GUESS' at best.

Stay away from un-experienced technical leads!!!

[Updated on: Sat, 05 May 2007 16:37]

Report message to a moderator

Re: List schema tables in dependency order [message #235448 is a reply to message #235439] Sun, 06 May 2007 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Another advice may be: Keep questioning what you're asked to do. Could this really be what they want? Why can't I use <fill in restriction>? etc.
If your manager tells you not to do it the most logical way, ask him why not. If it is something technical and the answer is Business Process, ask him how this technical solution would disturb the functional process.
Re: List schema tables in dependency order [message #236116 is a reply to message #234691] Wed, 09 May 2007 01:24 Go to previous message
mskalova
Messages: 4
Registered: May 2007
Location: Australia
Junior Member
incase if you want it, wouldnt you just do a self join to dba_constraints. let me know if you want an example of a script to get the table list.
Previous Topic: count of commas
Next Topic: problem in assigning serial number to records fetched by a query
Goto Forum:
  


Current Time: Fri Dec 02 14:01:09 CST 2016

Total time taken to generate the page: 0.36124 seconds