If you are creating database diagrams using the Oracle database server, you can take advantage of features specific to that server. Although the Database Designer is designed to work well with Oracle databases, there are some important differences in how the Database Designer works with Oracle databases.
For details about Oracle-specific issues raised here, see your Oracle database server documentation.
The following sections outline differences you will notice in the Database Designer when you work with Oracle databases.
You must use the Oracle ODBC driver provided with the Microsoft® Visual Database Tools. This driver has been tested to ensure that it works properly with the Database Designer. The Database Designer works with Oracle version 7, and currently supports most of the features of later Oracle versions.
The names of database objects — tables, views, columns, and synonyms — are case-insensitive by default in the Oracle database server. When objects are added to a database diagram, their names are changed to all uppercase, unless the name is in double quotation marks.
In the Data View window, however, database object names are displayed with the actual combination of uppercase and lowercase letters used when creating the objects.
In many cases the Database Designer catches invalid syntax and displays an appropriate message. However, in some cases — usually involving syntax specific to Oracle databases — the Database Designer does not detect all errors. When Oracle attempts to display the changes to a database object, it detects an error and it returns an error number and string to the Database Designer. The Database Designer then displays the information in a "raw" state.
If you receive such an error message, consult your Oracle documentation for details. If you do not have access to the Oracle documentation, you might have to experiment with your database diagram to determine where the error lies.
Because the MLSLABEL data type is designed for use primarily with high-security applications, it is not supported by the Microsoft Oracle ODBC driver. However, MLSLABEL is a legal data type in Oracle, so the Database Designer does not immediately detect an error when you add a table containing a column of this data type to a database diagram. You can make changes to this table that require only an ALTER TABLE statement. However, if you make any changes that require the table to be recreated, you'll get an "ORA-03115: unsupported network datatype or representation" error and the changes will not be allowed.
To make the changes to the table, you can view the change script for the database diagram, save the appropriate SQL statements for the changes, and then use an Oracle client tool to execute these SQL statements against the Oracle database.
Although the procedure for setting column properties using the Database Designer is similar regardless of the database you are using, the following sections contain information specific to Oracle databases.
For information on how to set column properties using the Database Designer, see Setting Column Properties.
If you use an invalid name for a column in the Database Designer, the name is automatically put in double quotation marks. See your Oracle documentation for the rules governing naming database objects.
Oracle column names will be converted to upper case, unless the name is enclosed in quotation marks or the name is not valid when shifted to upper case. In the latter case, the column name will be automatically enclosed in quotation marks.
For the procedure for changing column names, see Renaming a Column.
Even though Oracle supports both ANSI and their own data types, the Database Designer only allows data types native to Oracle and the ANSI float data type. The following table shows the Oracle data types supported in this version of the Visual Database Tools. The Database Designer uses the specified default lengths when the user changes from one data type to another (the length of the original data type is not preserved).
For more information about these data types, consult your Oracle database documentation.
Supported Oracle data types
| Data type | Description | Default value |
| VARCHAR2( length) | Variable length character string having maximum length bytes. Maximum length is 2000, and minimum is 1. You must specify length for a VARCHAR2. | 50 |
| NUMBER( p,s) | Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from –84 to 127. | 22 No Edit |
| FLOAT( p) | ANSI Float type that is equivalent to NUMBER(38). The precision p can range from 1 to 126. | 22 No Edit |
| LONG | Character data of variable length up to 2 gigabytes, or 2 31 –1 bytes. | 0 No Edit |
| DATE | Valid date range from January 1, 4712 BC to December 31, 4712 AD. | 7 No Edit |
| RAW( length) | Raw binary data of length bytes. Maximum length is 255 bytes. You must specify length for a RAW value. | 50 |
| LONG RAW | Raw binary data of variable length up to 2 gigabytes. | 0 No Edit |
| CHAR( length) | Fixed length character data of length bytes. Maximum length is 255. Default and minimum length is 1 byte. | 10 |
| MLSLABEL | Binary format of an operating system label. This data type is used with Trusted Oracle7. | 0 No Edit |
| ROWID | Hexadecimal string representing the unique address of a row in its table. This data type is primarily for values returned by the ROWID pseudocolumn. | 6 No Edit |
The Database Designer will allow you to decrement the Scale value of an existing column of type NUMBER. This will cause data loss, but no error message will be displayed.
A table can only have one column with a LONG or LONG RAW data type. The Database Designer verifies the data type when you exit the cell. If another column exists in the table with a LONG or LONG RAW data type, the Database Designer displays an error message and returns control back to the data type cell with the value selected.
The following table shows the available data type conversions for Oracle version 7.
"S" indicates that the conversion is supported.
"NS" indicates that a conversion is not supported.
Data type conversions
| FROM TO |
CHAR | DATE | FLOAT | LONG | LONG RAW | MLSLABEL | NUMBER | RAW | ROWID | VARCHAR2 |
| CHAR | S | S | S | S | NS | S | S | S | S | S |
| DATE | S | S | S | NS | NS | S | S | S | S | S |
| FLOAT | S | S | S | NS | NS | S | S | S | S | S |
| LONG | S | S | S | S | NS | S | S | S | S | S |
| LONG RAW | S | S | S | S | S | NS | S | S | S | S |
| MLSLABEL | S | S | S | NS | NS | S | S | S | S | S |
| NUMBER | S | S | S | NS | NS | S | S | S | S | S |
| RAW | S | NS | NS | NS | NS | NS | NS | S | S | S |
| ROWID | S | NS | NS | NS | NS | NS | NS | NS | S | S |
| VARCHAR2 | S | S | S | S | NS | S | S | S | S | S |
If you attempt to change a data type to an unsupported type, the Database Designer displays an error message.
Unlike Microsoft® SQL Server™, in Oracle a VARCHAR2 data type cannot relate to a CHAR data type. This means that the Database Designer will change the default length when going from VARCHAR2 to CHAR or the other way around.
The Database Designer uses VARCHAR2 as the default data type for new columns defined in the field list control.
You can toggle Allow Nulls on and off regardless of the column's data type. The default value is On for new columns.
The Database Designer doesn’t allow you to name or disable null constraints. The Database Designer will preserve the null constraint name and its disabled state in table recreates.
Disabled null constraints are visually the same as an enabled one. If you toggle Allow Nulls on for a disabled null constraint and then toggle it back off, the null constraint is recreated as enabled.
You can enter up to 2000 bytes for a column comment. If you want to include a single quotation mark in the string, use either a single or two single quotation marks to represent it. If you put in just one quotation mark character, the Database Designer adds the second quotation mark to make it valid for Oracle. For example, a comment of:
Bill’s table
becomes
Bill’’s table
when you exit the edit field.
The Database Designer beeps when you type in more than 2000 characters in the Comment field. If the expansion of the single quotation mark to a double single quotation mark causes the comment to exceed 2000 characters, the Database Designer displays a message, allowing you to choose between truncating the comment or removing the single quotation mark and returning to the Comment field.
Delete a comment by deleting the contents of the Comment field.
Oracle support for referential integrity differs from that in SQL Server.
For more information about referential integrity, see Enforcing Referential Integrity Between Tables in the Database Designer section of the documentation.
Referential integrity is a system of rules that ensure relationships between rows in related tables are valid and that you don't accidentally delete or change related data.
When referential integrity is enforced in an Oracle database, you must observe the following rules:
employee table, but you can indicate that an employee has no assigned job by entering a null in the job_id column of the employee table.jobs table if there are employees assigned to the job represented by that row in the employee table. However, if cascading deletes are enabled, you can delete a primary key row; all matching rows in related tables are also deleted.employee table if that employee is assigned to a job in the jobs table.Oracle supports only two kinds of referential integrity:
SQL Server, on the other hand, supports some additional kinds of referential integrity. You can set options to disable the foreign key constraint under these conditions:
As a result, the following topics in the Database Designer documentation are not valid for Oracle databases:
Checking Existing Data when Creating a Relationship
Disabling a Foreign Key Constraint with INSERT and UPDATE Statements
Disabling a Foreign Key Constraint for Replication
Referential integrity between tables is enforced by default when you create a relationship in your database diagram. An enforced relationship ensures each value entered in a foreign key column matches an existing value in the related primary key column.
You can change the conditions under which referential integrity is enforced by editing the relationship’s properties.
To change referential integrity options for a new relationship
To change referential integrity options for an existing relationship
The relationship is updated in the database when you save the diagram or either of the related tables.
For more information on the options for setting referential integrity for Oracle databases, see Oracle-Specific Dialog Boxes and Property Pages.
Oracle databases have different functionality for constraints. For example, in Oracle, constraints can be enabled and disabled. For information on how constraints are applied and used in Oracle databases, see your Oracle documentation. For information on how Oracle constraints are set in the Database Designer, see Oracle-Specific Dialog Boxes and Property Pages.
The following topics contain information that is specific to SQL Server databases; the topics note where the implementation is different for Oracle databases.
For more information about using constraints in the Database Designer, see Constraints.
In the Database Designer topic Checking Existing Data When Creating a Check Constraint, the following information is SQL Server specific:
When you create a check constraint, you can set an option to apply it either to new data only or to existing data as well. The option of applying the constraint to new data only is useful when you know that the existing data already meets the new check constraint, or when a business rule requires the constraint to be enforced only from this point forward.
In the Database Designer topic Disabling a Check Constraint for Replication, the following information applies to SQL Server only:
You can disable a check constraint when your table is replicated in another database. When you replicate a table, the table definition and data are copied from the source database to a destination database. These two databases are usually (but not necessarily) on separate servers. If the check constraints are specific to the source database, they may unnecessarily prevent new data from being entered in the destination database.
In Oracle, you can enable and disable constraints.
In Oracle, you can enable and disable primary keys.
The following dialog boxes and property pages are specific to Oracle. For more information on the fields and settings in these dialog boxes and property pages, see your Oracle documentation.
