Supporting Deferred Constraints for Oracle 8i using PowerDesigner 8.x
Oracle 8i supports a feature called Deferred Constraints. PowerDesigner supports this feature for some objects via physical options, such as the primary key and alternate key constraints. However, PowerDesigner 8.x does not have built in support for deferred foreign key constraints. Deferred constraint support for foreign key constraints can be implemented via changes in the XDB file and the use of Extended Attributes.
For an overview of how to use PowerDesigner Extended Attributes see one of the following Technical Documents.
For Primary Keys and Alternate Keys1) Open the property sheet of the desired Key
2) Select the Options tab
3) Move the "intially" and "<deferrable>" items to the right
4) For each item, select a value from the drop down list
5) Click OK
To generate this syntax, you must select Physical Options to be generated
for the primary key and/or alternate keys in the Generate DBMS window.
For Foreign Keys1) Create a new DBMS file
- Select Tools>Resources>DBMS
- Select the "new" icon to create a new DBMS file and give it a name such as ORACLE Version 8i Extended
- Select Oracle Version 8i in the "Copy from" drop down listbox
- Click OK
- Save the new XDB file in the \\program files\PowerDesigner 8\DBMS directory using a name like oracle8iext.xdb
2) In the new XDB add a new Extended Attribute Type
- Expand the Extended Attributes node
- Right-click on Extended Attributes>Types and select New> Extended Attribute Types
- Expand Types and select the newly created Extended Attribute Type
- Rename the Type to DefConst
- Add three values NOT DEFERRABLE, INTIALLY DEFERRED DEFERRABLE, INTIALLY IMMEDIATE DEFERRABLE
3) Add a new Extended Attribute
- Right-click on Extended Attributes>Objects, select Add Items and select Reference
- Right-click on Extended Attributes>Objects>Reference and select New>Extended Attribute
- Expand Reference and select the newly created Extended Attribute
- Rename the Extended Attribute to RefDefer
- Under Data Type, select DefConst from the drop down listbox.
NOTE: The Extended Attribute name will be used as a variable in the Create Reference section. This variable name is case sensitive.
4) Modify the Create Reference syntax
The following syntax must be added to the Create Table item:
[%variable name%=True? :[%RefDefer%]
In this example, the variable name = RefDefer
- Select the Create Item under Script>Objects>Reference
- In the Value block, insert [%RefDefer%=True? :[%RefDefer%] between [(%CKEYCOLUMNS%)] and ].
Sample code for Create Item:
alter table [%QUALIFIER%]%TABLE%
add [constraint %CONSTNAME%] foreign key (%FKEYCOLUMNS%)
references [%PQUALIFIER%]%PARENT% [(%CKEYCOLUMNS%)] [%RefDefer%=True? :[%RefDefer%]]
[%DELCONST%=RESTRICT?:[on delete %DELCONST%]]
5) Click OK
Note: The above steps will need to be implemented for each object in the DBMS that generates references syntax. For example, the Add section of the Reference object.
To implement the RefDefer extended attribute
1) Change the current DBMS to ORACLE Version 8i (8.1.5) Extended
2) Open the Reference properties and select the Extended Attributes tab
3) Select the RefDefer extended attribute and select a Value from the drop down list
4) Click OK to apply the changes
5) Open the Reference properties and select preview