Database Update Wizard
Validate Schema
To prevent problems when updating or exporting the schema, you can check the current schema for issues that violate schema best practices.
The Database Update Wizard's Validate Schema action runs a script that checks the current schema for the below "best practice" rules and alerts you to schema issues that do not follow these rules. You can then fix these issues before updating the schema or transferring out the current schema.
It is good practice to validate the schema:
- before and after you upgrade the database for a new release to ensure everything is in good shape
- after you modify the data dictionary for customization.
Procedure
-
Open the Database Update Wizard, click Run Script, and choose Next. This brings you to the Script screen.
-
Click Validate Schema.
-
The Database Update Wizard runs the
db-validation/schema-validation.duwscript. -
The script presents its results in the Messages Log window for you to review and address.
Schema "Best Practice" Rules
The Validate Schema action categorizes issues as:
| ERROR | Displayed in red color, these rules are critical and all tables and fields should obey those rules. Archibus applications and Web Central may stop working properly if these rules are violated. |
| WARN | These rules are considered important and it is recommended that you fix these issues. |
| INFO | These are harmless rules, but for the sake of consistency, the action returns this information. |
The Validate Schema action checks the schema for these "best practice" rules.
| Rule | Error Level | Description |
|---|---|---|
|
PK not found |
ERROR |
Table/View has no primary key. |
|
Size too big |
ERROR |
Size >4000(Oracle) chars (8000 for MSSQL). |
|
Odd elements in enum_list. |
ERROR |
There is a missing key or value in enum_list. There should be an even number of keys and values in the enum_list. |
|
Default value does not match enumeration |
ERROR |
Default value is not found in enumeration keys. |
|
Missing fields for table |
ERROR |
Detected a table with no fields defined. |
|
afm_flds_lang is missing field |
WARN |
Missing field in afm_flds_lang. |
|
Wrong FK definition |
WARN |
Data_type/size do not match with referenced field. |
|
Null default for enumeration |
WARN |
Default value = null for enum_list. |
|
Enumeration field allows null |
WARN |
An enumeration field has allow_null=1. |
|
PK allows null |
INFO |
PK should not allow null. |
|
PK as varchar |
INFO |
PK should be CHAR or NUMERIC. |
|
PK wrong string_format |
INFO |
String PK should be uppercase. |
|
PK wrong num_format |
INFO |
Numeric PK should be defined as NoSeparator. |
|
No default value for numeric |
INFO |
Numeric fields should have a default value. |
|
Numeric field allows null |
INFO |
Numeric fields should not allow null and have a default value. |
|
Size too big for char |
INFO |
Detects chars with size > 64 |
|
No decimals |
INFO |
Need to define a decimal for types Numeric, Float, Real, Double. |