OraFAQ Forum: General » Verification of foreign keys

Home » Other » General » Verification of foreign keys (Oracle,10g,Unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Verification of foreign keys [message #458232]

Fri, 28 May 2010 00:57

SaritaP
Messages: 3
Registered: May 2010
Location: India

Junior Member

I am working on a legacy application which consists of a database with no foreign keys.So,as a client reqt. I retrieved the foreign keys from the database using a script which identifies foreign keys based on the database schema and data in the tables.Now I am required to use their database logs to verify the extracted keys.These logs consist of only INSERT,UPDATE and DELETE statements.Does an INSERT followed by an UPDATE or vice-versa would be sufficient condition to say there exists a foreign key-primary key relationship between these two tables?Or should I be looking at some other aspects?

Report message to a moderator

Re: Verification of foreign keys [message #458238 is a reply to message #458232]

Fri, 28 May 2010 01:12

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

DELETE is nice; it won't let you remove parents as long as children exist.

Report message to a moderator

Re: Verification of foreign keys [message #459487 is a reply to message #458238]

Sun, 06 June 2010 01:04

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

You could always define the keys you have identified and run their code. If they violate any of the constraints, they will be kicked out. From this you would know one of three things

1) their insert/update/delete operations are loose aka. done in the wrong order in which case you could try deferred constraints

2) one or more operations has bad data in which case how would you know since you do not yet know if the FK is actually correct

3) what you thought was an FK is not but then again how do you really know

The best situation would be that you got no errors across a large number of operations on each FK. That would be reasonable proof of the value of the FK.

Additionally, you could assume that a small percentage of failures across one of your proposed FKs also is strong evidence that the FK is valid and the data has a small number of errors.

If you assume their logs and the applications that generated them are correct, and that the applications are not loose in that they actually tried to obey the Foreign Key Hierachy when applying their INSERT/UPDATE/DELETE operations then you can assume that an insert must preceed an update and thus that too would be evidence to support the validity of a proposed FK.

But as you can see you have to make assumptions for any of these (the problem with reverse engineering keys). In the end semantic knowledge (eg. an understanding of the datamodel and the business data) is what you really need. Too bad there isn't an old DataModel lying around you can look at.

Good luck, Kevin

Report message to a moderator

Re: Verification of foreign keys [message #459552 is a reply to message #459487]

Mon, 07 June 2010 00:17

SaritaP
Messages: 3
Registered: May 2010
Location: India

Junior Member

Thanks a lot for the detailed answer Kevin.

Had 1 more doubt.Does an INSERT followed by an UPDATE be considered as a sufficient condition to state that there exists a foreign key - primary key relationship between the 2 tables?

Report message to a moderator

Re: Verification of foreign keys [message #459553 is a reply to message #458232]

Mon, 07 June 2010 00:22

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Well all we can really say is this:

1) the only way to know where a FK exists is for someone who knows the data to tell you "there should be a FK here"

2) failing that you can note that if a FK does exist, then a row must inserted before it is updated. If an application is NOT using deferred constraints (aka. it is NOT loose in its sequence of DML) then you could assume that an insert into table A followed by an insert into table B means A may be higher on the FK chain that B. If an update changes a FK then we can assume that the parent table must have had an insert done to it before the update and thus and insert before an update can imply the existence of a FK.

You will have to do a lot of analysis to figure this out though. I am not sure how you would analyze a stream of SQL to figure it out. Drop us some details once you have a working process, I for one would like to see it.

Kevin

Report message to a moderator

Re: Verification of foreign keys [message #459688 is a reply to message #459553]

Tue, 08 June 2010 00:30

SaritaP
Messages: 3
Registered: May 2010
Location: India

Junior Member

Thanks Kevin.

That was really a useful reply.Will update the thread once I have a working process.

Report message to a moderator

Previous Topic:	many to many relationship with unique values Options
Next Topic:	PL/SQL and asp/jsp

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 03:56:39 CST 2024