Database Reference
In-Depth Information
Note: Some DBMSs (e.g. Oracle) will forbid this update, since M100 would be a
referenced tuple. This might be considered imprudent. What might be required is a
restriction on deletion, not update of this sort.
Work-around: Oracle avoids this situation quite elegantly, by allowing the designer
to temporarily disable a constraint, and enable it at a subsequent time. If your DBMS
does not have this kind of facility, what you may have to do is drop the foreign key
constraint from the referencing table(s), make the update, and then reintroduce the
foreign key constraint.
Example 4: Here is an illustration of how you could circumvent the problem in Oracle:
15.5 Limitations on Calculated Columns
In most implementations of SQL (that this writer has worked with), you are forbidden to
specify a virtual (calculated) column in a Group-By-Cause, Having-Clause, or a Where-
Clause in the same Select statement (this excludes accessing virtual columns in nested
queries as demonstrated in section 12.10 of Chapter 12). Neither can you define a virtual
(calculated) column in terms of another calculated column in the same Select statement.
Example 5: Consider a table that is keyed on a numeric attribute, Trans_Date ,
that stores the date (YYYYMMDD format). One might want to create a virtual attribute,
Trans_Year , that stores the year only (first four bytes of Trans_Date ), and cumulate
another column of the table, based on Trans_Year . In the writer's experience, this cannot
be done in a straightforward manner.
Work-around: One obvious work-around would be as follows:
1.
Create a logical view with the virtual column;
2.
Write a query on the view, grouping on the virtual column.
Alternate Work-around: An alternate work-around is to simply restate the
calculated expression wherever it is needed within the query.
 
Search WWH ::




Custom Search