Architectural Principles Foreign Key Constraints
Contents |
Objectives
Foreign Keys are constructed to ensure that foreign keys can be implemented and enforced using only native SQL whenever possible.
To support good data management practices.
Guidelines
1. In some cases, the Foreign Key strucutre of tables may result in creation of duplicate columns (i.e. 2 or 3 UWI columns). Typically, a business rule exists that requires all of the UWI columns to refer to the same well. In these cases, the model will be reconciled to a single UWI column that is referenced in multiple constraints; this will resolve the data management risk of having each column refer to a different well. This will result in columns that are a component of more than one constraint.
For example, some tables contain relationships to all of R_COUNTRY, R_PROV_STATE and R_COUNTY (based on variable user needs). The projection process would normally generate the following columns when tables are created:
- Example:
- R_COUNTRY constraint
- COUNTRY3
- R_PROV_STATE constraint
- COUNTRY2
- PROV_STATE2
- R_COUNTY constraint
- COUNTRY
- PROV_STATE
- COUNTY
The final result should contain only three columns, with the COUNTRY column constrained three times, the PROV_STATE column constrained twice, and the COUNTY column constrained once.
- Example:
- R_COUNTRY constraint
- COUNTRY
- R_PROV_STATE constraint
- COUNTRY
- PROV_STATE
- R_COUNTY constraint
- COUNTRY
- PROV_STATE
- COUNTY
2. Unless the Foreign key columns are part of the Primary key, they should always be defined as NULLABLE.
3. It is acceptable for the PPDM tables to have columns that are bound by more than one constraint. However, it is generally not desirable for a PPDM table to contain multiple columns that refer to the same value. An exception is granted for COMPONENT tables, which may contain foreign key references to many tables, some with the same column name.
4. Value specific exceptions are some of the *_Component tables such as WORK_ORDER_COMPONENT which may have the same value in the COMPONENT_ID and the foreign key such as BUSINESS_ASSOCIATE. The constraint is on BUSINESS_ASSOCIATE, but it is often convenient to also enter the same value into COMPONENT_ID to satisfy the table’s Primary Key.
Implementation Considerations
The data model contains a very large number of foreign keys; a number of issues can potentially arise that should be kept in mind by the implementor.
1. Tables contain a primary key that includes a column which is referenced from another table. AREA for example, contains the 2 part PK AREA_ID and AREA_TYPE. The column AREA_TYPE is validated by the reference table R_AREA_TYPE. When entering data into AREA, the FK to R_AREA_TYPE is explicitly enforced. However, AREA is used as a FK in many other tables - in these tables, the reference to R_AREA_TYPE is indirect.
IMPLICATION - When updating reference table values (or any other table that contains a PK that cascades into other tables) be careful to make sure that both direct and indirect relationships are checked and updated.
2. Tables that have lots of foreign keys can be difficult for query optimizers to work with. This is particularly true for the COMPONENT tables. In these cases, use hints in your BI tool that will help the optimizer select the correct method. There are a few tips for Optimizing Queries that may help.
3. In the PPDM DDL, indexes are only provided for FK columns. Remember that you are responsible for deciding which indexes to use (or not use).
Go to
- Architectural Principles Primary Key Constraints
- Architectural Principles Foreign Key Constraints
- Architectural Principles Check Constraints
- Architectural Principles Indexes
- Architectural Principles Arcs
- Architectural Principles Recursive Relationships
- Architectural Principles Naming Conventions
