| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: how to modify a CHECK column-constrait ?
Altering a check constraint requires the old constraint to be dropped then replaced, for example:
alter table tab1 drop constraint chk1;
alter table tab1 add constraint chk1 check (col1
is not null);
This means always giving constraints explicit names - if you don't then it is very easy to loose them. When you create a un-named check constraint in a table create statement, it is really defining a table constraint as listed above. By keeping the column and constraint creationg statements separate is is much easier to manage.
As for dropping columns: Oracle 8 can do it, but not 7. This is understandable when you look at how the data is physically arranged on the disk. To drop a column in 7 you must rename the table, create a new table then copy the data accross to the new table. Having scripted the original creation statements the contraints and keys and indexes etc can then be recreated on the new table.
Oracle is very efficent, stable and fast at what is does. In return it asks that the database is designed right from the offset - it is not a RAD tool like Microsoft Access.
I hope this is useful.
Regards,
Jason
In article <7n9s8j$l8v$1_at_rl0001.unimaas.nl>,
"Ruud Schmeitz" <R.Schmeitz_at_MEMIC.UNIMAAS.NL>
wrote:
> Hi readers,
>
> After working for more than 10 year with DEC's
(now Oracle's) Rdb, we're
> now converting our databases to Oracle.
>
> 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;
>
> Is this really true (I just hope it ain't) ?
> Are there any work-arounds ?
>
> Kind regards and TIA,
>
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Mon Jul 26 1999 - 06:37:10 CDT
 |
 |