Re: Does ORACLE has any stupid restrictions like MS-SQL

In <5gnbo4$o0s_at_chronicle.concentric.net> Nnoor_at_cris.com (NNOOR) writes:
>We are getting increasingly frustrated with the restrictions in the
>MS SQL Server, especially when it comes to modifying the columns of
>a table. It does not let you drop a column, change data type or
>add/remove NULL restrictions. As a result, we are considering
>moving off the SQL Server as we are so early on in the development
>stage that we can afford it.

So in other words, poor database design work done at the beginning means you blame the database and nor your work, first.

You _can_ change the datatype of a column in SQL server OR in Oracle as long as the contents are upwardly compatible. Be aware that the semantics of comparison operations caused by a change from number to character will be significant, depending on your application design and SQL statements used.

The general technique is to create a new table with the desired characteristics, and then copy each row over to the new table, and then renaming the new table into the place of the old table. Constraints may have to be deactivated and then put into place on the new-old table if you've used such. Constraints include not null enforcements.

By the way, going from nullable to not-null is easy, too, under Oracle.

  update tablename
    set nullable_column = NVL(nullable_column, value-when-null)     where nullable_column is null
    ;
  alter table tablename add constraint constraint_name     check ( nullable_column is not null );

Dropping a column is similar to changing its data type, except that you port over every column to the new table but the one you wanted.

Again, better planning at the beginning could have avoided these kinds of issues.

andy

-- 
Andrew Finkenstadt (ICQ UIN 221059 "kahuna")
see web page for affiliations: http://www.supernet.net/~kahuna/