Re: how to modify a CHECK column-constrait ?

>>> I've found 2 annoying problems. According to the SQL Reference Manual
>>> (Oracle 7.3 and 8.0) it is not possible to:
>>> 1. modify an existing CHECK column-constraint;
>>> 2. drop a column from a table;

1. To modify an existing CHECK constraint, it's as easy as dropping the constraint and then altering the table to include the new CHECK constraint you'd like. True, you can't modify the constraint directly,but this other method isn't that difficult.
2. Dropping a column from a table is something everyone wishes to be possible. Apparently Oracle is listening since this feature is available with Oracle 8i (8.1.5). But once again, there is a workaround. This one takes a little more effort. Assuming table 1 has columns col1, col2, col3, you can do one of two things:
   1. copy the table to a temp table: CREATE table1_copy AS SELECT * FROM table1. Then drop the table: DROP TABLE table1. Then recreate the new table without col2: CREATE TABLE table1 AS SELECT col1, col3 FROM table1_copy. Now drop the temp table: DROP TABEL table1_copy.

   Or

   b) Just create a view that only has the two columns: CREATE VIEW table1_view AS SELECT col1,col3 FROM table1.

Hope that helps,
Brian Peasland
peasland_at_msn.com Received on Fri Jul 23 1999 - 20:51:52 CDT