Thiiink: Ideas, Imagination, and Innovation in GIS

When we look at foreign keys in a database model (keeping in mind that we’re trying to get these into a geodatabase) we can see a few patterns:

1. Implemented for integrity (this would be all of them!)
2. Link lookup values to a base table
3. Single column foreign keys
4. Multi-column foreign keys
5. Foreign keys implemented for history (self-joins)

These patterns can be handled in a few ways:

1. Keep the relational foreign keys (boring)
2. Translate the foreign keys for lookup values into a domain
3. Use relationship classes

The self join concept is something that is only do-able via the relational database so not much can be done about this one, although for the number of times that this actually occurs it isn’t that bad of an option.

Before we go any further, let’s look at a query that will bring back the all the foreign keys that are a) not self-joins and b) are defined on a single column.  Something like this will do the trick:

SELECT a.constraint_name,
       a.r_constraint_name,
       a.delete_rule,
       b.table_name        fk_table_name,
       b.column_name       fk_column_name,
       c.table_name        pk_table_name,
       c.column_name       pk_column_name
FROM   (SELECT *
        FROM   sys.dba_constraints
        WHERE  dba_constraints.constraint_name IN 
(SELECT   constraint_name
FROM     sys.dba_cons_columns 
 WHERE    dba_cons_columns.constraint_name IN 
(SELECT constraint_name 
 FROM   sys.dba_constraints 
 WHERE  owner = 'PPDM37'
        AND constraint_type = 'R') 
 GROUP BY dba_cons_columns.constraint_name 
 HAVING   COUNT(constraint_name) = 1)) a,
          sys.dba_cons_columns b,
          sys.dba_cons_columns c
WHERE  a.owner = b.owner
AND a.constraint_name = b.constraint_name
AND a.r_owner = c.owner
AND a.r_constraint_name = c.constraint_name
AND b.table_name != c.table_name

The bad thing here is that this query doesn’t distinguish between single-column foreign keys that are implemented for integrity only or those that are implemented for lookups.  That said, since we’re limiting our scope to PODS and PPDM we can use the naming conventions of these data models to tease apart the results.  In the PPDM model most of the lookup tables are prefixed with R_ whereas in PODS the lookup tables are suffixed with CL.  Another thing we can bank on because these databases use standard naming approaches is that in the PPDM lookup tables we’ll have a LONG_NAME column and in PODS we’ll have  DESCRIPTION column.

Given these assumptions we can separate the results fairly easily.  From this point I’ll assume that you know how to query a database using Python (if not then refer to the Constraints and Comprehensions post) and I’ll begin with a snippet that filters the results to return just the records that represent foreign keys implemented for lookup values:

resultsFilter = [r for r in results if r[5][:2] == 'R_']

and the other set would be returned by

resultsFilter = [r for r in results if r[5][:2] != 'R_']

Pretty simple hey?  Not everything has to be difficult…but then again, we really haven’t accomplished much yet.  The next steps are where this get cool, where we translate this into domains, simple relationship classes, and composite relational classes.  That’s enough for one night, more later.