Re: Adding/modifying columns

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 21 Jun 1999 14:07:52 GMT
Message-ID: <3776464b.10853145@newshost.us.oracle.com>

A copy of this was sent to Sam Jordan <sjo_at_spin.ch> (if that email address didn't require changing) On Mon, 21 Jun 1999 12:59:02 +0000, you wrote:

>Hi
>
>What's the easiest way to add/modify columns in a table,
>if new constraints should be added, too? The intuitive
>way doesn't work (using alter table add/modify). I tried
>to create a new table and to copy all rows of the original
>table into the new one. But then the question is, how to
>exchange both, so that all references from other tables
>automatically go to the new one?
>
>What's the reason that constraint specifications aren't allowed
>for alter table add/modify? Basically Oracle should be able to
>check, whether the constraint is violated or not, and allow the
>operation if no violation is detected.
>
>bye

They are allowed for alter table add but not modify. For example:

SQL> desc emp;

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                             NOT NULL NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)

SQL> alter table emp add x number check ( x > 0 ); Table altered.

SQL> alter table emp add y number references dept(deptno); Table altered.

SQL> desc emp

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                             NOT NULL NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)
 X                                        NUMBER
 Y                                        NUMBER

SQL> alter table emp add z number;

Table altered.

SQL> alter table emp modify z varchar2(25) check ( z in ( 'A', 'B' ) ); alter table emp modify z varchar2(25) check ( z in ( 'A', 'B' ) )

                                      *

ERROR at line 1:
ORA-02253: constraint specification not allowed here

It is just as easy though to modify the column and then add the constraint, for example:
SQL> alter table emp modify z varchar2(25);

Table altered.

SQL> alter table emp add constraint z_check check( z in ( 'a', 'b' ) );

Table altered.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 21 1999 - 09:07:52 CDT