Oracle® Life Sciences Data Hub Application Developer's Guide Release 2.3.1 E35298-02 |
|
![]() Previous |
![]() Next |
This section contains information on the following topics:
The tables that store data in the Oracle Life Sciences Data Hub (Oracle LSH) are Oracle tables with additional metadata developed especially for Oracle LSH. Oracle LSH database tables are designed to hold data that originated in either a SAS or an Oracle system, or both. They are designed so that computer programs based on either Oracle or SAS technology can read from or write to any Oracle LSH table. Oracle LSH tables have the following special characteristics:
Both Oracle and SAS Metadata Attributes. Oracle LSH database tables have Oracle and SAS names and a SAS label at both the table/data set and column/variable levels.
Blinding Attributes. Oracle LSH tables have special attributes for use in maintaining securely blinded clinical data. See "Managing Blinded Data" for further information.
Data Processing Attribute. Oracle LSH tables have a special attribute whose value determines how the system processes data written to each table. Most methods of data processing result in an audit trail of all data changes within the table. See "Data Processing Types".
Table Definitions and Instances You must create both a Table definition and a Table instance, and install the instance in the database. See "Object Definitions and Instances, and their Containers" for further information.
The Table definition is a metadata representation of a Table that includes most of the detailed specifications of the Table, including the number of Columns, the name, data type and length of each Column, and primary key, unique key, and check constraints.
The Table instance contains a pointer to the Table definition and a few details such as a mapping to at least one Table Descriptor contained in a Program or other executable instance. To actually store data in a table, you must install the Table instance in the database. The installation process instantiates the metadata from both the Table instance and the Table definition to which it points in the database to create a database table. Some attributes belong to both the definition and the instance. In this case, you can overwrite the value in the instance, and the installation process creates a database table with the value specified for the instance. As with other object definitions in Oracle LSH, you can create multiple instances of the same Table definition.
Reading from and Writing to Tables There are several types of objects that can read data from and/or write data to an Oracle LSH Table instance, as follows. Each type contains a Table Descriptor for each Table instance they either read from or write to. The Table Descriptor and Table instance must be mapped. For further information, see "Defining and Mapping Table Descriptors".
Programs. Programs can read from and/or write to installed Oracle LSH Table instances. Report Sets and Workflows contain Programs, and therefore they can indirectly read from and/or write to Table instances. Only one Program instance can write to a particular Table instance.
Load Sets. A Load Set loads data from an external system into one or more installed Oracle LSH Table instances (see Chapter 7, "Defining Load Sets").
Data Marts. A Data Mart reads data from an installed Oracle LSH Table instance and copies the data into a file (see Chapter 8, "Defining Data Marts").
Business Areas. A Business Area contains Table Descriptors that allow a data visualization tool to read data from Oracle LSH Table instances.
Creating Tables As you develop applications in Oracle LSH, you can create Oracle LSH Tables in the following ways.
Working in a Work Area, you can:
Create a Table instance from an existing Table definition
Create a new Table instance and Table definition manually at the same time
Create a Table instance and Table definition from an existing SAS data set located in an external system
Working in a Program, Load Set, Data Mart, or Business Area you can:
Create a Table Descriptor from an existing Table definition
Create a Table Descriptor and Table definition manually at the same time
Create a Table Descriptor from an existing SAS data set located in an external system
Create one or more Table instances from one or more Table Descriptors (using the Actions drop-down list)
Create one or more Table Descriptors from one or more Table instances (using the Actions drop-down list)
In addition, working in a Load Set you can create a Table Descriptor from a table, data set, or view in an external system.
You can also create a Table definition directly in a Domain or Application Area if you have the necessary privileges. However, you cannot use the definition for storing data until you create an instance of it in a Work Area and install it in the database.
Columns Table Columns are instances of Variables, as are Parameters. See Chapter 6, "Defining Variables and Parameters"for further information.
Reports on Table Definitions and Instances From the Actions drop-down list, you can generate reports that provide information on a Table definition or instance; see Chapter 14, "System Reports"for further information.
This section contains the following topics:
When you create a Table in a Work Area, you are actually creating an instance of a Table definition.
To create a new Table instance:
In a Work Area, select Table from the Add drop-down list.
Click Go.
The system displays the Create Table screen.
Choose one of the following options:
Create new table definition and instance. Choose this option if no Table definition exists that can meet your needs, either as it is or with some modification.
Create an instance of an existing Table definition. Choose this option if an Oracle LSH Table definition already exists that meets your needs.
If you can adapt an existing Table definition to make it fit your needs, first copy it into the current Application Area, then choose this option and select the copied definition. See "Finding an Appropriate Definition" and "Reusing Existing Definitions" for further information.
Create new table definition and instance from SAS data set. Choose this option if you want to create an Oracle LSH Table with the same metadata structure as an existing SAS data set.
Depending on your choice, follow one of the following sets of instructions:
Modify the default attribute settings as necessary; see "Setting and Modifying Table Attributes".
Define Columns as necessary; see "Defining Table Columns".
Define Constraints and Indexes as necessary; see "Defining Table Constraints and Indexes"
Figure 4-1 Process of Creating a Table Definition and Instance
When you select Create new table definition and instance in the Create Table screen, additional fields appear.
Enter values in the following fields:
Name. See "Naming Objects".
Description. See "Creating and Using Object Descriptions".
Oracle Name (up to 30 characters, uppercase, no spaces). Enter text or accept the default value. The system automatically creates the default from the text you entered in the Name field, converting it to uppercase, with underscores (_) substituted for spaces, truncated to 30 characters if necessary.
SAS Name (up to 32 characters, uppercase, no spaces). Enter text or accept the default value. The system automatically creates the default from the text you entered in the Name field, converting it to uppercase, with underscores (_) substituted for spaces, truncated to 32 characters if necessary.
Enter a SAS Label (optional, up to 256 characters). Enter text or accept the default value. The system automatically creates the default from the text you entered in the Name field.
In the Classification section, select the following for both the definition and the instance:
Subtype. Select a subtype according to your company's policies.
Classification Values. See "Classifying Objects and Outputs" for instructions.
Click Apply to save your work and continue defining the Table.
The system opens the Properties screen for the new Table instance.
Define the details. See:
You can upload SAS data set metadata into Oracle LSH to create a Table with the same structure as a data set in an integrated external system. The system creates Table Columns with the same data type and length as the variables in the data set.
Note: Oracle LSH gives SAS variables of SAS format BEST8 a length of 8 and Precision set to null. |
The system searches for Variables in the same Application Area with the same name, data type, and length as each of the variables in the data set. If a matching Variable exists, the system bases a Column of the Table definition on it. If a Table definition with the same name already exists in the Application Area, the system appends _1 to it, or _x if the Table name already has a number appended, where x is the next larger integer.
Figure 4-2 Process for creating a SAS Data Set
When you select Create new table definition and instance from SAS data set in the Create Program screen, additional fields appear.
Click Browse. A standard Choose file pop-up window opens.
Select the .sas file on a local or shared drive and click Open.
In the Classification section, select the following for both the definition and the instance:
Subtype. Select a subtype according to your company's policies.
Classification Values. See "Classifying Objects and Outputs" for instructions.
Click Apply to save your work and continue defining the Table.
The system opens the Properties screen for the new Table instance.
Define the Table details in the lower part of the screen. See:
Note: You can also create an Oracle LSH Table from a SAS data set when you define Load Set Table Descriptors. You can then use the Actions drop-down list item Table Instance from Existing Table Descriptor to create an identical target Table instance and map the two. |
If you use an existing Table as a definition source, its Columns and other properties are already defined. See "Creating an Instance of an Existing Definition".
When you create a new Table definition and/or Table instance, the system populates its attribute values. To change these values, as well as the name and other attributes described in "Creating a New Table Definition and Instance", click Update.
Process Type The processing type determines how Oracle LSH writes data to the Table instance. See "Data Processing Types" for information.
For Table instances that are the target of an Oracle-technology Load Set, you can instead select Create Table as a View from the Process Type drop-down list. No processing type is required because the system does not write data to the Table instance. Instead, you use the Table instance as a pass-through view to see data in the source system. The option appears after you map the Table instance to an Oracle-technology Load Set.
Note: The system prevents you from selecting a processing type that requires a primary or unique key (Reload or Transactional with Audit) until you have done the following:
|
Allow Snapshot Snapshots are possible only on audited Tables (Reload, Transactional with Audit, or Staging with Audit). See "Data Snapshots".
If set to No, creating and labeling snapshots of this Table instance is not allowed.
If set to Yes, users can create and label snapshots of this Table instance.
Blinding Flag This setting has effect only for Table instances. The setting in the Table definition serves only as a default setting for instances of that Table definition. See "Managing Blinded Data" for further information.
If set to No, the Table instance is intended to never contain blinded data.
If set to Yes, the Table instance may contain blinded data. The system maintains two sets of rows: one set for the real data and another set for dummy data, effectively partitioning the table in the database.
Note: If you change the setting of the Blinding flag for a Table instance that is already installed, you cannot use an Upgrade-mode Work Area installation to reinstall the new version of the Table instance. You must do a Full-mode Work Area install to apply a new blinding status to an installed Table instance, which deletes the Table instance's data; see "About Work Area Installation". |
Blinding Status If the Blinding Flag is set to Yes, you can set the Blinding Status attribute to one of two values:
Blinded. The real, sensitive data cannot be viewed or operated on except by a user with Blind Break privileges. Any user with normal security access to the Table instance can view and operate on the dummy data.
Unblinded. A person with special privileges can change the Blinding Status from Blinded to Unblinded; for example, at the end of a trial. After the status is set to Unblinded, users require special, but less restrictive, privileges to view and operate on the unblinded data. See the chapter on security in the Oracle Life Sciences Data Hub Implementation Guide for more information on blinding-related privileges. See also "Managing Blinded Data".
If the Blinding Flag is set to No, you can set the Blinding Status attribute to one of two values:
Not Applicable. This is the default value. It is intended for use with Table instances that never will contain sensitive information that would require blinding.
Authorized. Use this status in the rare case that the Table instance is the target of a Program that reads from a blinded Table instance, and the Program is written in such a way that this target Table instance will never contain sensitive data that should be blinded. When the Blinding Status is set to Authorized, the system allows users who have Blind Break privileges on all blinded source Table instances to run the Program that writes to this Table instance. These users see a warning and must confirm that they want to run the Program.
You may need to use this feature, for example, when creating dummy data; see "Loading Real and Dummy Data".
Note: While you are developing a Program that reads from a blinded Table instance and writes to a nonblinded Table instance, set the target Table instance's Blinding flag to Yes until you are sure that neither the Program nor its log file exposes any sensitive information. |
Definition Source This field applies to the instance only. It specifies the Table definition to which this Table instance points. See "Definition Source".
Tablespace Name If your company has created its own tablespaces, select the appropriate tablespace for the actual database table created when you install this Table instance. If you do not enter a value, the system creates the table in the default tablespace for the database.
Click the Search icon. The Search and Select window opens.
Enter the name of the tablespace you are looking for and click Go. You can use the % wildcard, but not as the first character. The system displays the tablespaces that meet the search criteria.
Click the Quick Select icon. The system returns you to the Table instance screen with the selected tablespace displayed.
This section contains the following topics:
Subtabs:
See also Figure 4-1, "Process of Creating a Table Definition and Instance".
See "Modifying Tables" for information on modifying Tables.
If you are working in a Work Area, you see the properties of both the Table instance and the Table definition it references. If you are working directly on the definition in an Application Area or Domain, you see only the properties of the definition.
You can see the following instance properties:
Name You can click Update and modify the name. See "Naming Objects" for further information.
Description You can click Update and modify the description. See "Creating and Using Object Descriptions" for further information.
Oracle Name This is the Table's name to be used in PL/SQL code.
SAS Name This is the Table's name to be used in SAS code.
SAS Label This is an optional field. You may see a SAS label for the Table here.
See "Setting and Modifying Table Attributes" for a description of the following:
Validation Status This field displays the current validation status of the Table instance. If you have the necessary privileges, you can change the validation status by selecting Validation Supporting Information from the Actions drop-down list. See "Validating Objects and Outputs" for further information.
Status This field displays the installable status of the Table: Installable or Non Installable. See Appendix A, "Installation Requirements for Each Object Type".
Version This field displays the current version number of the Table instance.
Version Label This field displays the version label, if any, for the current Table instance version.
For further information on object versions, see "Understanding Object Versions and Checkin/Checkout".
Checked Out Status This field displays the status of the definition: either Checked Out or Checked In. You must check out the definition to modify Columns and Constraints/Indexes. See "Understanding Object Versions and Checkin/Checkout" for further information.
Latest Version If set to Yes, this Table instance is pointing to the latest version of the Table definition. If set to No, this Table instance is pointing to an older version of the Table definition.
Checked Out By This field displays the username of the person who has the Table definition checked out. See "Understanding Object Versions and Checkin/Checkout" for further information.
Version Label This field displays the version label, if any, for this definition version.
Validation Status This field displays the current validation status of the Table definition. If you are working directly in the definition in an Application Area or Domain and you have the necessary privileges, you can change the validation status by selecting Validation Supporting Information from the Actions drop-down list. If you are working in an instance of the Table in a Work Area, and you want to change the validation status of the definition, you must go to the definition. See "Validating Objects and Outputs" for further information.
Status This field displays the installable status of the Table: Installable or Non Installable. See Appendix A, "Installation Requirements for Each Object Type".
From a Table instance in a Work Area, you can use the following buttons:
Update Click Update to modify the Table instance properties. See "Modifying Table Instance Properties".
Check In/Out and Uncheck Click these buttons to check out, check in, or uncheck the Table definition. Different buttons are displayed in the Table Definition Properties section depending on the Checked Out Status and whether or not you are the person who has the definition checked out. If someone else has checked out the definition, you cannot check it in or uncheck it. The username of the person who has checked it out is displayed. See "Understanding Object Versions and Checkin/Checkout".
To create a new Table Column:
In the Columns subtab of a Table, click Add. The system displays the Create Column screen.
Choose one of the following options:
Create a New Column and Variable. Choose this option if no Variable definition exists that meets your needs. See "Create a New Column and Variable".
Create a Column from an Existing Variable. Choose this option if a Variable already exists that meets your needs. Using this option whenever possible increases consistency and reusability in your Oracle LSH applications.
See "Finding an Appropriate Definition" and "Reusing Existing Definitions" for further information. For instructions, see "Creating an Instance of an Existing Definition".
To create a new Column definition and instance at the same time, enter the following information:
Enter values in the following fields:
Name. See "Naming Objects".
Description. See "Creating and Using Object Descriptions".
Data Type. Select one of the following from the drop-down list.
VARCHAR2. Specifies a variable-length character string. For each row, the system stores each value in the Column as a variable-length field unless a value exceeds the Column's maximum length, in which case the system returns an error.
NUMBER. Stores zero, positive, and negative fixed and floating-point numbers. A Number Column can contain a number with or without a decimal marker and/or a sign (-). All standard rules for the Oracle Number data type apply.
DATE. For each Date value, Oracle stores the following information: century, year, month, date, hour, minute, and second. Although date and time information can be represented in both character and number datatypes, the Date datatype has special associated properties.
Length. The maximum number of bytes or characters of data that the Column can hold. The requirements vary according to the data type:
VARCHAR2. A value for length is required and must be between 1 and 4000 characters.
DATE. The system disregards the length value, if any.
NUMBER. A value for length is optional.You can leave the length and precision null, and Oracle LSH treats the number column as having the maximum possible length.
Precision. (This field appears only if you select a data type of NUMBER.) The total number of digits allowed. For example, if Precision is set to 2 and a data value is 34.333 is entered in this Column, the system stores the data value as 34.33. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
Oracle Name. Name to use for the Column in PL/SQL source code. The value defaults from the Name value, converted to uppercase and with underscores substituted for spaces. You can change the default value.
SAS Name. Name to use for the Column in SAS source code. Enter text or accept the default value. The system automatically creates the default from the text you entered in the Name field, converting it to uppercase, with underscores (_) substituted for spaces.
SAS Label (Optional) Enter up to 200 characters.
SAS Format Required:
For columns of VARCHAR2 data type, by default, the system enters a dollar sign ($) followed by the value you entered in the Length field.
For columns of NUMBER data type, by default, the system enters a period/full stop (.).
Default Value (optional). You can enter a value to serve as the default for this Column.
Nullable. If set to Yes, null values are allowed in this column. If set to No, each row must have a value in this column.
In the Classification section, select the following for the Variable:
Subtype. Select a subtype according to your company's policies.
Classification Values. See "Classifying Objects and Outputs" for instructions.
Click Apply to save your work and continue defining the Table.
The system opens the Properties screen for the new Table instance.
You can create individual Columns as instances of existing Columns, as you can for other objects. In the case of Columns, a Column definition is really a Variable.
For instructions, see "Creating an Instance of an Existing Definition".
This section contains the following topics:
You can define Constraints to enforce limitations on data in each row of a table. Oracle LSH automatically generates an index based on the Primary Key Constraint. Oracle LSH Constraints and indexes appear as Oracle table constraints and indexes for Oracle technology Programs and as SAS data set constraints and indexes for SAS technology Programs.
You must define the Table's Columns before you can define Constraints.
Constraints are different from other Oracle LSH defined objects in that they belong to the Table instance as well as to the definition. If you define or modify a Constraint in a Table instance in a Work Area, the resulting Constraint is part of the Table instance but not the Table definition on which it is based. The only way to make a Constraint a part of the Table definition is to go to the definition directly in the Application Area or Domain and add it there. After you have added a Constraint to a Table definition, all Table instances created from it also have that Constraint defined. You can delete the Constraint from the Table instance if you want to.
You must define a Primary or Unique Key for a Table in order to perform most types of data processing (see "Data Processing Types").
Note: Oracle LSH supports the standard Oracle Not Null constraint, but you define it as a Column attribute, not through the Table Constraints user interface. |
To create a Constraint:
In the Constraints/Indexes subtab of a Table, click Add.
The system displays the Create Constraint screen.
Enter values in the following fields:
Name. See "Naming Objects".
Description. See "Creating and Using Object Descriptions".
Type. Choose the type of Constraint. The system refreshes the display of the lower portion of the screen depending on which type you choose. The definition procedure varies by type: see "Check Constraint", "Primary Key", "Unique Key".
Click Apply.
The check Constraint allows you to specify allowable values for a particular Column. For example, you can require that a particular Column contain either a Yes or No value in every row.
If any row contains a different value for the Column, the system generates an error to the Program writing to the Table instance. If the Program does not handle the error, the job fails.
Note: Oracle does not verify that check Constraints are not mutually exclusive. Therefore, if you create multiple check Constraints, design them carefully so their purposes do not conflict. Do not assume any particular order of evaluation of the conditions. |
To define a Check Constraint:
Select Check Constraints from the Type drop-down list. The system refreshes the lower portion of the screen.
From the Column Name drop-down list, select the Column for which you want to define a list of allowable values.
In the Column Values section, enter one allowable Check Value value in the first row.
To add an additional row, click Add Another Row.
Click Apply.
You can define a non-unique index on any Column or set of Columns.
Unlike a Primary or Unique Key, a non-unique index does not validate each row for a unique value but allows different rows to have the same value in the Column or set of Columns that are part of the index.
Like other indexes, a non-unique index keeps rows sorted on the specified Column or Columns so that the system can use faster search algorithms on the table, speeding up queries on the table.
To define a non-unique index, do the following:
Select Non-Unique Index from the Type drop-down list. The system refreshes the lower portion of the screen and lists all the Columns you have defined in the current Table instance in the Table Columns side of the shuttle.
Select the Column or Columns you want to be part of the unique key and move them into the Non-Unique Index side of the shuttle. You can use Shift+Click or Ctrl+Click to select the Columns. You can double-click to move them or use the arrows.
If necessary, use the Up and Down arrows to reorder the Columns in the key. You should have the most general Column at the top and the most granular at the bottom. For example, if your non-unique key is Patient, Visit, Test, you should list them in that order.
Click Apply.
A Primary Key is a Column or set of Columns whose value(s) identify a row in a table as unique. A single-Column Primary Key is commonly a unique ID. A multi-Column, or composite, Primary Key might include, for example: Patient, Visit, Test, if each test was conducted only once at a particular visit. None of the Columns that is part of a Primary Key can have a null value in any row.
The system automatically creates an index based on the primary key, which it uses to enforce a unique constraint. The index also speeds up queries on the table.
When you define a Primary Key, the system enforces constraints on the data:
The Column value(s) must serve as a unique identifier for each row.
No value in a key Column can be null. Columns that are part of a Primary Key must have the Nullable attribute set to No (the check box must be cleared).
To define a Primary Key:
Select Primary Key from the Type drop-down list. The system refreshes the lower portion of the screen and lists all the Columns you have defined in the current Table instance in the Table Columns side of the shuttle.
Select the Column or Columns you want to be part of the primary key and move them into the Primary Key side of the shuttle. You can use Shift+Click or Ctrl+Click to select the Columns. You can double-click to move them or use the arrows.
If necessary, use the Up and Down arrows to reorder the Columns in the key. You should have the most general Column at the top and the most granular at the bottom. For example, if your Primary Key is Patient, Visit, Test, you should list them in that order so that you can use the automatically generated index.
Click Apply.
A Unique Key is similar to a Primary Key in that it can include one or more Columns whose value(s) identify a row as unique. The difference is that the system allows null values in the Columns that are part of a Unique Key.
Any number of rows can include null (empty) values. A null in a Column (or even all Columns where allowed in a composite Unique Key) satisfies the Unique Key Constraint. However, you cannot have identical non-null values in the Columns of a partially null composite Unique Key Constraint.
If, as part of the definition of a Column that is part of a Unique Key, you disallow null values for that Column, the system does not allow null values there.
The Unique Key also serves as an index.
To define a Unique Key:
Select Unique Key from the Type drop-down list. The system refreshes the lower portion of the screen and lists all the Columns you have defined in the current Table instance in the Table Columns side of the shuttle.
Select the Column or Columns you want to be part of the unique key and move them into the Unique Key side of the shuttle. You can use Shift+Click or Ctrl+Click to select the Columns. You can double-click to move them or use the arrows.
If necessary, use the Up and Down arrows to reorder the Columns in the key. You should have the most general Column at the top and the most granular at the bottom. For example, if your Unique Key is Patient, Visit, Test, you should list them in that order so that you can use the key as an index.
Click Apply.
This section contains the following topics:
If you have the necessary privileges, you can modify a Table either through an instance of it in a Work Area or directly in the definition in its Domain or Application Area. In most cases it makes sense to work through an instance in a Work Area for the following reasons:
In order to use or test changes to the definition you must create and install an instance of it.
If you work through an instance, the system automatically repoints the instance to the new version of the definition.
However, if you need to change properties of the definition, you must work directly in the definition in its Domain or Work Area.
Whether you work in an instance or directly in the definition, when you check in the new version of the definition you have the opportunity to upgrade instances of the original definition to the new version; see "Upgrading Object Instances to a New Definition Version".
On the Table instance's Properties screen, click Update to enter changes. Oracle LSH creates a new version of the instance you are working on and applies your changes to it when you click Apply. Click Cancel to discard your changes and the new version.
You can modify some properties through the Actions drop-down list; see "Using the Actions Drop-Down List" for further information.
Note: You must reinstall the Table for the changes to take effect. |
You can modify the following:
Name See "Naming Objects" for further information.
Description See "Creating and Using Object Descriptions" for further information.
Definition Source This field applies to the instance only. It specifies the Table definition to which this Table instance points. It generally does not make sense to change the source definition for the following reasons:
Changing the definition may result in a new set of Columns and Constraints/Indexes.
The Status of the Table changes to Non Installable.
If you want to change to a new version of the same definition, use the Upgrade Instance option from the Actions drop-down list.
See "Setting and Modifying Table Attributes" for a description of the following:
You can go to a Table definition's Properties screen in one of the following ways:
From the Table's Properties screen: Click the hyperlink of the Table definition that appears in the Definition field. See "Definition Source".
From the Domain or Application Area where you created the definition: Click Manage Definitions to view all the definitions in that Domain or Application Area. Click the definition name.
Once on the Table definition screen, click Update to enter changes. Oracle LSH creates a new version of the definition. You can change the following properties:
Name See "Naming Objects" for further information.
Description See "Creating and Using Object Descriptions" for further information.
You can modify some properties through the Actions drop-down list; see "Using the Actions Drop-Down List" for further information.
See "Setting and Modifying Table Attributes" for a description of the following:
To modify a Column in any way, you must check out the Table definition; Columns belong to the Table definition.
You can delete and add Columns, and change the definition source Variable for a Column (the source Variable determines the Column's data type, length, and default value, if any). You cannot modify the data type, length, or default value except by substituting a different Variable as the definition source.
When you create a Table instance from a Table definition, the system copies the definition's constraints and indexes to the instance instead of referencing them there. Therefore, any changes you make to the Table instance in the Work Area affect only the instance.
Normal Oracle database rules apply to making changes in table constraints. Oracle prevents changes that might be destructive. For example, you cannot change the Column that constitutes the Primary Key unless the new Column's Nullable attribute was previously set to No.
In most cases you cannot modify an installed Table instances's constraints without dropping and replacing the Table instance during a Full or Partial Work Area installation, resulting in the loss of all data. This is not allowed in a Table instance with a validation status of Production.
If you must modify a Production Table instance's constraints, you may be able to define a new Table instance with the required constraints and write a Program to migrate the data into it, if the data and the new constraints are compatible.
If you modify the Table definition through the instance, the system points the instance to the new version of the definition, so the changes apply to the instance as well as the definition. If you modify the definition directly in its Domain or Application Area, the changes affect only the definition, but you can upgrade instances to point to the new version.
See "Defining Table Constraints and Indexes" for further information.