Referential Integrity Problem 
Author Message
 Referential Integrity Problem

Conditions:
I have a table called "name" of the following structure:

create table name
(ID integer primary key not null,
Last char(20) not null,
First char(20) not null,
Middle char(20) not null);

This table has a one to many relationship.  A single
record in the "name" table is referenced by MANY different
tables of various names.  

Problem:
When my MFC code changes a record in
the "name" table.  That change gets populated into
several different places in the database unexpectedly
since several other tables in my database are refering to
the same record via the foriegn key (name.ID).

Question:
When my user attempts to update a record in the "name"
table, is there an SQL statement I can issue to find out
what other tables in my database (if any) reference
the "name" table, so I can search them to create a report
for my user of all database references to the record
in "name" that he/she is trying to change.

Reason:
If I can find the answer to the above question, I can then
ask my user which references he/she would like to continue
pointing to the old data (with or without change) and
which references he/she would like to point to a new
record in table "name" that will hold the new data.

Regards,

Shawn



Tue, 26 Oct 2004 23:52:06 GMT  
 Referential Integrity Problem
Shawn,

That's a rather classic problem ...

Unfortunately, the ability to discover all of the references to a PK does
not exist in a single SQL statement that is recognized across all database
systems.  You can get the information by querying the database schema - but
the syntax will be unique to whatever database you are using.  For instance,
in Oracle you could issue the following SQL:
" Select * from user_constraints where R_CONSTRAINT_NAME='PK_NAME_ID' "
where PK_NAME_ID is the name of the primary key constaint on ID in the Name
table.  But that would not work when you moves the data to a SQL Server
database or to an MS Access database.  And the Oracle example would work
only if ALL references to the PK column were from tables in your schema.  To
get all references in the database, you replace user_constraints view with
the sys_constraints view - but that requires specific permissions that you
may or may not have!

I assume that you are using the MFC ODBC classes (CDatabase and CRecordset).
If you switch to ADO, you can use the ADO Extensions (ADOX) to enumerate all
tables looking for a key that references the ID field of the NAME table.
This would provide a fair amount of isolation from differences between
database providers.

I hopethis helps a bit.

best regards
roy fine


Quote:
> Conditions:
> I have a table called "name" of the following structure:

> create table name
> (ID integer primary key not null,
> Last char(20) not null,
> First char(20) not null,
> Middle char(20) not null);

> This table has a one to many relationship.  A single
> record in the "name" table is referenced by MANY different
> tables of various names.

> Problem:
> When my MFC code changes a record in
> the "name" table.  That change gets populated into
> several different places in the database unexpectedly
> since several other tables in my database are refering to
> the same record via the foriegn key (name.ID).

> Question:
> When my user attempts to update a record in the "name"
> table, is there an SQL statement I can issue to find out
> what other tables in my database (if any) reference
> the "name" table, so I can search them to create a report
> for my user of all database references to the record
> in "name" that he/she is trying to change.

> Reason:
> If I can find the answer to the above question, I can then
> ask my user which references he/she would like to continue
> pointing to the old data (with or without change) and
> which references he/she would like to point to a new
> record in table "name" that will hold the new data.

> Regards,

> Shawn



Wed, 27 Oct 2004 03:37:37 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Help! - ODBC and Referential Integrity

2. ADOX: Foreign Keys Without Referential Integrity

3. self-referential structures

4. Computing the Marshal.Sizeof a self referential class

5. Self-referential structure

6. Mutually Referential Structs in C

7. Declaring mutually referential structures

8. Self referential typedefs

9. Safer C : Developing Software for High-Integrity ...

10. self referential typdef's and structs

11. Looking for C/C++ Source Integrity Scanner

12. A self-referential challenge

 

 
Powered by phpBB® Forum Software